SQL SERVER – Fastest Way to Restore Database

A few days ago, I received following email from blog reader where the question was about the fastest way to restore database.

“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 the 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.

SQL SERVER - Fastest Way to Restore Database vdb1

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 (https://blog.sqlauthority.com)

,
Previous Post
SQL SERVER – Subquery or Join – Various Options – SQL Server Engine Knows the Best – Part 2
Next Post
SQL SERVER – Difference Between DATETIME and DATETIME2

Related Posts

4 Comments. Leave new

  • prabhakar thipe
    May 2, 2013 4:41 pm

    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

    Reply
  • 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.

    Reply

Leave a Reply

Menu