Step 2: Developing and testing your SQL Server backup

You must have a well-planned and standardized approach to backing up your SQL Servers so you're prepared for the worst-case scenario. Edgewood Solutions' Greg Robidoux offers a three-part guide for putting together a SQL Server backup process -- planning, testing and implementation.

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

 Home: Introduction
 Step 1: Planning your SQL Server backup
 Step 2: Developing and testing your SQL Server backup
 Step 3: Implementing your SQL Server backup


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.

Dig Deeper on SQL Server Backup and Recovery

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.