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.
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)
