Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Rolling Back a Restore

By Steve Jones,

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

Everyday Jones

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:

Overall RSS Feed: or now on iTunes!

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.

 
Total article views: 150 | Views in the last 30 days: 1
 
Related Articles
FORUM

restore database failed because database size would exceed your licensed limit of 4096 MB

restore database failed because database size would exceed your licensed limit of 4096 MB

BLOG

Want to be able to read your database between restores?

When restoring a database there are times when it would nice to restore a bit, check what’s been res...

FORUM

Sql 2005 database restore

Sql 2005 database restore

FORUM

database restore file name

database restore

FORUM

Restore (database onwer)

Restore (database onwer)

Tags
 
Contribute