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