Part II: What You Need to Know About Backing Up Your Data

What should I keep in mind when creating a Database Maintenance Plan?

1. The SQL Server Agent service must be running on the server in order to run the jobs created by a Database Maintenance Plan. You can check the SQL Server Agent service at: Start > Control Panel > Administrative Tools > Services


2. For SQL Server 2005, the Database Options must be set to Full Recovery for SQL Server 2000 databases. In SQL Server Management Studio: Expand Databases > Right click your database > Properties > Options > Recovery: Model needs to be Full


3. Be sure you have enough drive space to store the number of backups you decide to create. You can set the drive location on the server for the database backups. The Database Maintenance Plan will manage the removal of old backups per your instructions.
   

What should I do if a transaction log grows unexpectedly or becomes full? Since transaction logs that do not successfully backup can grow very large, you should reviewing your SQL Logs and Windows Event Log for backup failures regularly. See this Microsoft article on potential causes of large transaction logs.

What else do I need to know about data storage? Selecting a length of time to keep the database backups is necessary. Keep in mind the available hard disk space that will be required to store weeks of database backups could be more than what is available. You should store database backup files for at least 1 week on the physical hard disk. It is recommended to also store backups in another device, such as tape backup. Tape backups allow for a longer retention of the database backup files. See Part I for more.

Find online help files that provide greater detail than this by going to:
Start > Programs > Microsoft SQL Server > Books Online

- Nannette Dame, Senior Consultant