Frequently Asked Question: What are the recommendations for Backup and Maintenance of the DataWarehouse?

Frequently Asked Question

Subject:	What are the recommendations for Backup and Maintenance of the DataWarehouse?
Status:	Publish
Category:	Data Warehouse, Tracker Office, Tracker Suite
Request Type:	Tracker Office 2.2

SQL Server 7 and 2000 both have Maintenance plan configuration wizards. For more information on these, please refer to either the online help in SQL Server or the documentation that came with your SQL Server software. Run the configuration wizard (normally by selecting Tools>Database Maintenance Planner menu option) and set up the following:

Select the master database (set up at the time of SQL Server installation) as well as the TAC_Datawarehouse database, unless you have already set up the backup for the master database.
Select the Reorganize data and index pages and Remove unused space from database files. Modify the schedule if desired.
Select to Check the Database Integrity and to do them before database backups. Modify the schedule if desired.
Select Backup the database as part of the maintenance plan and Verify the integrity of the backup when complete. Modify the schedule if desired. Modify the target location of the backup, either to a disk or to tape.
Select Backup the transaction log as part of the maintenance plan, select a target location and Verify the integrity of the backup. Modify the schedule if desired.
Select how you want to Write the history of the maintenance plan, either to a local or remote server.
Give the plan an intuitive name and click Finish
The plan can be modified later by using SQL Enterprise Manager

Backup methodologies are too complex to discuss here in complete detail. However, here are a few recommendations:

Select the master database (set up at the time of SQL Server installation) as well as the TAC_Datawarehouse database, unless you have already set up the backup for the master database.
Select the Reorganize data and index pages and Remove unused space from database files. Modify the schedule if desired.
Select to Check the Database Integrity and to do them before database backups. Modify the schedule if desired.
Select Backup the database as part of the maintenance plan and Verify the integrity of the backup when complete. Modify the schedule if desired. Modify the target location of the backup, either to a disk or to tape.
Select Backup the transaction log as part of the maintenance plan, select a target location and Verify the integrity of the backup. Modify the schedule if desired.
Select how you want to Write the history of the maintenance plan, either to a local or remote server.
Give the plan an intuitive name and click Finish
The plan can be modified later by using SQL Enterprise Manager