Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
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 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

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

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


Sql 2005 database restore

Sql 2005 database restore


database restore file name

database restore


Restore (database onwer)

Restore (database onwer)


Database Restoration

Unnecessary database restoration

backup / restore    

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones