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.
Sources:
Comments