SQL SERVER – Fastest Way to Restore the Database

A few days ago, I received following email from blog reader:

“Pinal,

We are in an emergency situation. We have a large database of around 80+ GB and its backup is of 50+ GB in size. We need to restore this database ASAP and use it; however, restoring the database takes forever. Do you think a compressed backup would solve our problem? Any other ideas you got?”

First of all, the reader has already answered his own question. Yes; I have seen that if you are using a compressed backup, it takes lesser time when you try to restore a database. I have previously blogged about the same subject. Here are the links to those blog posts:

SQL SERVER – Data and Page Compressions – Data Storage and IO Improvement

SQL SERVER – 2008 – Introduction to Row Compression

SQL SERVER – 2008 – Introduction to New Feature of Backup Compression

However, if your database is very large that it still takes a few minutes to restore the database even though you use any of the features listed above, then it will really take some time to restore the database. If there is urgency and there is no time you can spare for restoring the database, then you can use the wonderful tool developed by Idera called virtual database. This tool restores a certain database in just a few seconds so it will readily be available for usage.

I have in depth written my experience with this tool in the article here SQL SERVER – Retrieve and Explore Database Backup without Restoring Database – Idera virtual database. Let me know your experience in this scenario. Have you ever needed your database backup restored very quickly, what did you do in that scenario.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

4 thoughts on “SQL SERVER – Fastest Way to Restore the Database

  1. Pingback: SQLAuthority News – A Monthly Round Up of SQLAuthority Blog Posts Journey to SQL Authority with Pinal Dave

  2. i have one project with database …but this database is stored in sql server 2008.. and i want to restore this database in visual studio 2010 and run this project …plz give me sir some steps or idea if possible as soon as possible

  3. Pingback: SQL SERVER – Weekly Series – Memory Lane – #033 | Journey to SQL Authority with Pinal Dave

  4. Hi, just a question about restores, the message text returns the statistics for the process including the time but the time in SSMS is usually significantly greater, which one in correct, i have a script to monitor the restore progress which returns the SSMS time.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s