Now that you have your plan in place, it is time to develop and test your process. This is the time to make sure your backup process and restore process works and all the components are available when needed.
|Recovery Model||Now that you have your backup plan in place, make sure your databases have the correct recovery model and the recovery model reflects the types of backups you will be running.|
|Click here for
help selecting a recovery model.
|Process||Use the approach you selected above to develop your backup process. Whether you use maintenance
plans, home-grown scripts, scripts from another resource or third-party tools, try to standardize
on one approach or a couple of approaches to keep things consistent. Classify your servers into
different groups, and those groups should have consistent processes.
|Test Backups||Testing the backups is the easy part. Most of the time, the backup process is just put into
place and the backups are off and running. Unless you have backup failures, this part of your
process will work fine. At this stage, take timings or look at the times on your scheduled tasks to
see how long the backup process should run. This way you will have a benchmark of how long your
backup jobs normally take in case there are problems down the road.
|Test Restores||This one area often does not get the proper attention. Running backups is easy because you can
automate the process and most third-party tools allow you to point and click to set it up. The
restore process is a whole different ballgame. You need a server, enough disk space and enough time
to run the restores. This is a very critical step. It ensures that you know how to restore your
databases and that your process will work when needed.
|Test Access Rights||This is a good time to test the user account that will be used to do the backups and possibly
the restores. Make sure the account has the proper access prior to putting it into production mode.
How to properly back up a SQL Server
Step 1: Planning your SQL Server backup
Step 2: Developing and testing your SQL Server backup
Step 3: Implementing your SQL Server backup
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.
This was first published in October 2005