What if a restore was a logged operation?
Not really logged, but what if by default a restore would create new files, a new database, and then if everything was OK when it completed, it would instantly switch the name over to the old database. Everything would be the same, but if there was an issue with the restore, you would still have your old database.
I rarely have seen issues restoring files, but I have realized a few times that I started a restore that was overwriting a database and needed to stop it. Typically this has happened when I'm trying to overwrite a copy of production with a refreshed backup, and might accidently be pointing at production (yikes!).
Ideally we would always have separate environments for testing, and even separate physical machines, but that's not always the case. The ability to stop a restore that was in progress, or even to complete the restore and then manually approve the "switch" to the new files would be fantastic.
There is a downside here, and it requires you to balance resources. After all, nothing happens for free. You would need at least double your data and log file sizes in disk space. In the age of larger and larger databases, this could be an issue for some people. However a survey done last year showed that 90% of databases are actually small, less than 2GB in size, so maybe this isn't a bad idea in most cases. There are definitely places where it wouldn't work, and this would have to be an option that could be disabled, but I think it might make a nice enhancement to the SQL Server restore process.
I haven't submitted it on Connect, but I ought to. I'm open to debate about whether this would make sense and be useful for people that often manage restores of their databases.
Steve Jones
The Voice of the DBA Podcasts
The podcast feeds are available at sqlservercentral.mevio.com. Comments are definitely appreciated and wanted, and you can get feeds from there.
You can also follow Steve Jones on Twitter:
or now on iTunes!
- Windows Media Podcast - MB WMV
- iPod Video Podcast - MB MP4
- MP3 Audio Podcast - MB MP3
Today's podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.
