Step 2: Developing and testing your SQL Server backup

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

ABOUT THE AUTHOR:

Greg Robidoux 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. Copyright 2005 TechTarget

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT SQL Server Backup and Recovery SQL Server Mailbag: Data restoration and DB property management Achieving high availability and disaster recovery with SharePoint databases How to 'do' SQL Server disaster recovery The keys to database backup protection for SQL Server Choosing a SQL Server disaster recovery solution Licensing a standby server for SQL Server replication Can I encrypt and restore a database backup in SQL Server 2005? SQL Server errors, failures and other problems fixed from the trenches Get SQL Server log shipping functionality without Enterprise Edition SQL Server 2008 backup compression pros and cons SQL Server Backup and Recovery Research

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary rollback  (SearchSQLServer.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary