Lessons learned from a SQL Server 2014 database restore: the case of tail-log backups

I recently updated on of my development machines to Windows 8.1 Update, and at the same time, wanted to get my hands on the latest SQL Server 2014 RTM version. A project I'm working on uses SQL Server 2012 production database, and so I took a copy of it to my development machine.

Usually, I do such migrations by taking a full backup from the original (this time, the production) database, and then execute a restore on the target server (this time, the development machine). But alas, with SQL Server 2014, this required learning a new thing: by default, a new setting called "Take tail-log backup before restore" is on by default, which leads to an interesting error: you cannot restore a backup to an empty (new) database unless you deselect this default option. Otherwise, SQL Server 2014 will try to make a backup of the database you are restoring, but since it doesn't exist yet, this operation fails.

Of course, the fix is easy. In the Restore Database dialog, go to the Options page, and under Tail-Log backup, uncheck the option "Take tail-log backup before restore".

Remember, that this default only affects situations where you are restoring a backup to create a completely new database. The option is perfectly fine (and a good idea) for restores that overwrite an existing database.

Finally, the Restore Database dialog box has a memory: if you once uncheck the tail-log backup option, it stays unchecked when you next restore a database.

Good luck!