I learn new things all the time. This was one that actually stunned me. Huge props to Gail Shaw for posting a note about this in a thread.

Run this code:

CREATE DATABASE MyRestoreTest
;
GO
USE MyRestoreTest
go
BACKUP DATABASE MyRestoreTest TO DISK ='myrestoretest.bak';
GO
CREATE TABLE mytable( id INT)
;
GO
USE master
go
BACKUP LOG myrestoretest TO DISK = 'myrestoretest_log.trn' WITH norecovery

You’ll see this in your Object Explorer

Ugh.

I haven’t started a restore. I’ve run a backup. Apparently this causes problems, as noted by Gail in the thread. Sure enough, it’s documented in the BACKUP command, in the Log-specific Options.

I had never scheduled backups with this type of option, but you might have a job that does this if you were preparing for a failover. Having a script ready it a good idea, but if it executes unexpectedly, this could happen.

The lesson: make sure you know the options when you run a command. Always test, and if something strange happens, search or ask what might have happened.

Filed under: Blog Tagged: Backup/Recovery, sql server, syndicated