Click here to monitor SSC
  • Av rating:
  • Total votes: 18
  • Total comments: 2
Grant Fritchey

Faster Restores: Best Practices to Increase Speed

02 December 2011

Backups are an everyday part of DBA life, whereas restores tend to happen on call at 3 a.m. In this article, Grant Fritchey looks at what you should be doing to make your restores as quick and seamless as possible.

Everyone has a list of best practices for backups, including me. But, you know what doesn’t get talked about much? Restores. Yeah, there are things you can do to make your restores better, faster, stronger. Why would you want to improve restore speed? Think about it like this, backups are something that you automate, tweak, and tune during the day when you get to think about things and make rational decisions. Restores on the other hand are things that occur at 3AM when you’re barely awake. Restores also occur with multiple levels of management crowding into your cube with very little in the way of rational decision making going on. So yeah, maybe having a good handle on some ways to improve restores is a good thing. Here are a few tips to make your restore processes better.

Practice

People frequently hate to be reminded that they need to practice. I know my kids crawl up the wall when I remind them to practice their Spanish or their sword lessons. Same thing goes with DBAs. “Who wants to practice doing a restore operation? Sorry, don’t have time, gotta implement new functionality on the app, update alerting, tweak the monitoring system…” But, the best way to get a RESTORE operation running quickly and efficiently is to know what to do. If you run a RESTORE once or twice a month (at least) every month for a year or so, when you’re suddenly required to run a restore at 3AM, you’re going to be able to type the syntax out without looking it up. That alone will speed up your restore process. Eliminating fifteen minutes of mistyped commands and BOL lookups is a major cost savings.

Practice your restore operations.

Instant File Initialization

When you restore for the first time to a server or you use WITH MOVE, the operating system has to create new files for the database. Starting with SQL Server 2005, there’s support to work with the operating system (which has to be XP or better or Windows Server 2003 or better) to instantly initialize the files. To make this work, you have to make sure you have the appropriate security settings for the account that SQL Server is running under. You have to include that account in the Windows Administrator group on the server, or you have to add the account to the Perform Volume Maintenance Tasks security policy, or you have to just give it the SE_MANAGE_VOLUME_NAME special privilege. Once done, there’s nothing else you have to do. You’ll get instant file initialization which can result in astronomical time savings.

Hardware

Just like backups, the speed of the disks you’re reading from and writing to will increase performance for RESTORE operations. Same thing goes with the number of disks. Extra spindles and extra disk controllers always speed things up even if you eliminate spindles and use SSDs. The more memory you have the faster processing will occur during the RESTORE operation and the same thing goes for CPUs. In short, throwing money at the problem can help.

Compression

Many of the choices you make when you’re creating backups will also affect your restores. Compressing your backups is beneficial because it has to write less to the disk; disks are the slowest part of the system, so savings there really count. Same thing works going the other way. If you have to read less from the disk when you’re running your restore, it increases the overall performance of the process. This is not a huge win. In fact, it’s marginal at best, but, anything you can do to increase the speed of restores can add up.

Multiple Files

If you have multiple disk drives you can radically improve the speed of backups, and going the other way, restores. You do this by splitting the backup up amongst the multiple drives by using more than one backup file. It will require syntax changes to both your backup and your restore commands. This is directly related to throwing hardware at the problem, but instead of concentrating on faster disks, you’re concentrating on more disks.

Restore in Place

If you’re restoring an existing database on the server and you’re not using WITH MOVE to allocate new files, then always run the restore directly against the existing database. You do this because you get to avoid the performance penalties from file initialization. If you have instant file initialization this might not be as big a win as otherwise, but still, it’s a safe way to reduce the time on all systems (even those that don’t support instant file initialization).

Recovery

The biggest part of the restore process is moving all the data from the backup file to the data files. But at the end of the restore process is one more step, recovery. Recovery is when the transactions that were completed during your backup are rolled forward into your data. Recovery is also when the transactions that were not completed during your backup are rolled back. Depending on the size and number of your transactions, this can be an extremely costly operation. SQL Server has a setting called the Recovery Interval that determines how often checkpoints occur. The default is zero, which means checkpoints occur relatively frequently on a schedule determined by SQL Server. Changing this expands the number of transactions that will occur before a checkpoint occurs. This means that your restore process will have more transactions to deal with, making the recovery time longer, hence the recovery interval. Unless you’re having serious performance issues from frequent checkpoints, you should have this set to zero and you’ll have faster database recovery.

Summary

Restores can be very stressful and scary operations. Follow these best practices and you should see some of that stress reduced as you see the restore operations speed along quicker than before. Don’t forget to practice your restore operations. Not knowing how to restore a database is the single biggest pain point I’ve seen.

New! SQL Backup Pro 7
Try out the brand new features, including integrated backup verification. Make checking for corruption an easy step in your normal backup and restore routines. Find out more.

Grant Fritchey

Author profile:

Grant Fritchey, SQL Server MVP, works for Red Gate Software as Product Evangelist. In his time as a DBA and developer, he has worked at three failed dot–coms, a major consulting company, a global bank and an international insurance & engineering company. Grant volunteers for the Professional Association of SQL Server Users (PASS). He is the author of the books SQL Server Execution Plans (Simple-Talk) and SQL Server 2008 Query Performance Tuning Distilled (Apress). He is one of the founding officers of the Southern New England SQL Server Users Group (SNESSUG) and it’s current president. He earned the nickname “The Scary DBA.” He even has an official name plate, and displays it proudly.

Search for other articles by Grant Fritchey

Rate this article:   Avg rating: from a total of 18 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: faster restores
Posted by: mstjn (view profile)
Posted on: Wednesday, December 14, 2011 at 9:56 AM
Message: We have a db that's about 100gb and have split the backup over 5 physical files-- but all on the same drive. There does appear to be a small performance gain. We also have to copy the backup to several other servers-- we use RichCopy. There's a speed increase here as well.

...Maybe the lessons would be more fun for the young'uns if the person that does not get the point(no pun intended; iirc a "score" is a "point") has to conjugate the verb "Maestralizar" (to decline to the northwest)

Subject: faster restores
Posted by: mstjn (view profile)
Posted on: Wednesday, December 14, 2011 at 10:12 AM
Message: We have a db that's about 100gb and have split the backup over 5 physical files-- but all on the same drive. There does appear to be a small performance gain. We also have to copy the backup to several other servers-- we use RichCopy. There's a speed increase here as well.

...Maybe the lessons would be more fun for the young'uns if the person that does not get the point(no pun intended; iirc a "score" is a "point") has to conjugate the verb "Maestralizar" (to decline to the northwest)

 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

Searching for Strings in SQL Server Databases
 Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine... Read more...

Continuous Delivery and the Database
 Continuous Delivery is fairly generally understood to be an effective way of tackling the problems of... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.