SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Accidently Kicking a Database into the Restoring State

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



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

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest


Leave a comment on the original post [voiceofthedba.wordpress.com, opens in a new window]

Loading comments...