Testing is key to ensuring a process actually works when it comes time to implement, rather than finding out when time may be of the essence. This holds true for testing SQL Server restores as well.
Most backups are done for the sole purpose of restoring corrupt or lost data. This is your safety net for ensuring valuable information is not lost in the event of a failure. Even though backups are created for this purpose, often they are not performed, so there's nothing to confirm that the restore process will succeed. Neither can you verify that all underlying components are available to bring your application back online.
In addition to testing your backups and having the essential supporting components, you must take into consideration differences between restoring from a backup that exists on disk versus a backup that exists on tape. The most recent backup file will usually exist on disk and is therefore readily available, but you may need an archived copy for auditing -- or maybe because it's the only version that exists. For this reason, it is important that you have procedures for both of the restore methods and ensure they both work.
Location of backup files
Most DBAs would agree that the primary location for writing SQL Server backups is disk and then archive to tape for long-term storage. One advantage to doing this, from the restore perspective, is that your most recent backup files are online and ready to be restored when you are ready. When dealing with backups that are written directly to tape, you may need to involve groups that handle tape management, which can delay the restore. You must make sure you test the entire process, including retrieving the backup file.
Several scenarios exist for you to test your restore process. Keep in mind that these are not all good approaches and others may exist as well.
1. Backup files written to disk and then archived to tape: This is the best approach to take -- as most DBAs woul
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
d agree.
2. SQL Server backups written directly to tape: This option is used often if you purchased backup agents for SQL Server. Disk space is much cheaper these days, so option #1 is a better choice.
3. SQL Server backups written to disk, copied to tape and then removed from disk: If you are already writing your backup files to disk, you may as well leave the most recent copy on disk in case of a failure. Again, disk space is much cheaper then it used to be.
4. SQL Server is shut down; MDF and LDF files are written directly to tape and SQL Server is restarted: There is no need to shut down your services to take backups. This option is usually employed by people who do not understand how backup and restore works.
5. SQL Server database is detached; MDF and LDF files are written directly to tape and then database is reattached: Again, there is no need to take this option. Using native backup and restore functions are a much better way to go.
With all of these approaches, there are differences in how the restore process will work. The first three will use the SQL Server RESTORE commands. The last two don't use SQL Server BACKUP and RESTORE commands at all. Make sure you understand how backups are being performed, so you can tailor you restore process to match.
Thinking through space issues
Lack of test servers and/or disk space: This used to be a bigger problem than it is now. I think you can easily overcome it by purchasing a low-end or used server to create a test environment. You can use SQL Server Developer Edition to create a test server. Meanwhile, the cost of disk space keeps getting cheaper and cheaper. You may not be able to mimic the exact production server specs, but it would be better to know that your restore process works on a lower-end server than to not know if it will work at all.
Large databases: In some cases it may be impossible to have a duplicate environment or even a large enough test environment. In these situations, people often use file or filegroup backups, which could then be individually restored on a test environment. The ideal situation would be to have a test environment large enough to test the complete restore process. Again, not knowing if your process will work or how long it will take for a complete recovery could rule out this option, so find a way to test the entire process.
Moving files around network
When testing database restores, you usually restore to a different server. Therefore, you should either copy the backup to a different server or do a restore across the network. The better and faster approach is to copy the file to the local server and then restore locally. This is often much faster and avoids having to contend for network I/O through the entire restore process. The downside to doing this is that you now have two copies of the backup file -- one on the originating server and one on the server where you will perform the restore.
Restore process
Depending on where you restore your backups, you must take into account the different considerations. If you restore to the same server and the backup is fairly recent, you probably won't have much to adjust. When restoring to a different server, consider the following points:
1. Location of physical files: When restoring to a different instance or to an older backup, you may need to make an adjustment to the location of the physical files. You can accomplish this easily with the MOVE option in the RESTORE command or by changing it when using Enterprise Manager.
2. Matching logins and users: This is probably one of the biggest issues you will run into when restoring your databases. It is not an issue when testing the restore, but if you want to test your applications and user security, make sure this information is correct.
3. How long it takes: Time the restore process from start to finish, including getting files off of tape, coping files across the network, restoring the backup and any final adjustments. Having this information will be key for future restores.
4. Types of restores: In addition, you should test different types of restores and combinations of restores including full, differential, transaction log and file or filegroup restores. You may find that from your restore testing, you will need to make adjustments to your backup schedule to ensure faster recovery and/or less data loss.
Summary
In general, performing database restores is not that complicated. When restoring on the same server, Enterprise Manager gives you the tools you need to quickly restore your database. This is great if you want to replace your existing database. For testing purposes, there is a lot more involved than just using the GUI. Take the time to set up your backup and restore procedures and make sure your restore process is as seamless as your backup process.
The following tips may be useful for testing and developing your restore procedures.
About the author: Greg Robidoux is the president and founder of Edgewood Solutions LLC, a technology services company delivering professional services and product solutions for Microsoft SQL Server. He has authored numerous articles and has delivered presentations at regional SQL Server users' groups and national SQL Server events. Robidoux, who also serves as the SearchSQLServer.com Backup and Recovery expert, welcomes your questions.
