Lesson 3: Backing Up and Restoring Databases

Preventing data loss and restoring data quickly after a disaster is critical to providing continuous access to data in a data warehouse. As discussed in Lesson 1, a data warehouse has many parts and contributing databases. You need to ensure that all of these components are available after a disaster so that the data warehouse will function correctly. This lesson summarizes the SQL Server backup and restore practices and procedures that you will use to back up and restore the parts of your data warehouse that are stored in SQL Server relational databases.

Backup and restore are discussed in detail in the SQL Server 7.0 System Administration Training Kit.

After this lesson, you will be able to:

• Back up and restore specific elements in a data warehouse

Estimated lesson time: 20 minutes

Backing Up Databases

You should regularly back up both your enterprise database and the OLAP database to safeguard the data in your data warehouse. The timing and frequency with which you back up your databases are dependent on your particular business environment.

Activities That Require Database Backups

When you create a backup schedule for the databases in your data warehouse, consider the following facts and guidelines:

• Back up system databases (master, msdb, model if it has been modified and distribution if the server is configured as a replication Distributor) and all user databases regularly as a matter of course and, specifically, whenever you modify them.
• Perform a backup after you create a database or an index.
• Back up a database after you perform a nonlogged operation. If your system fails, the transaction log might not contain the information that you need to restore the database to a consistent state.

Activities That Interfere with Database Backups

You can back up a database while the database is online and active, but you should schedule backups when database activity is low and degradation to the performance of the data warehouse has the least impact on users. Some operations cannot be performed during the backup process. If you attempt to start a backup while one of the following operations is in progress, the backup process terminates. If a backup is in progress and you attempt to start any of these operations, the operation fails and the backup continues. Operations that cannot be performed during backup are

• Creating or modifying databases with the CREATE DATABASE or ALTER DATABASE statement
• Creating indexes
• Performing any nonlogged operations, including a bulk load of data and the SELECT/INTO, WRITETEXT, and UPDATETEXT statements
• Shrinking the database or database files

TIP

---

If a backup or restore operation fails (for example, due to a power failure), the operation can be restarted. This is an important time saver for the very large databases typically encountered in a data warehouse.

Types of Backup Methods

SQL Server provides several backup methods. The size of your databases and how frequently you modify data determine which method to use. The following table describes each backup method and when to use it.

Backup Method	Description	Use for
Full database	Backs up the entire database, including the transaction log.	Creating a baseline in case of system failure. Small databases with few modifications or read-only databases.
Differential	Backs up all database changes since the last full database backup. To restore a database, you need to restore the latest full backup and the latest differential backup.	Backups, only after performing a full database backup. Reducing the time needed to perform a restore. Frequently modified databases, because a differential backup copies each page in its current state rather than all of the transactions that are required to return a page to its current state.
Transaction log	Backs up all database changes from the last BACKUP LOG statement to the end of the current transaction log. Truncates the transaction log. To restore a database, you need to restore the latest full backup (and the latest differential backup, if there is one) and all the transaction log backups taken since the full backup.	Backups, only after performing a full database backup or a differential backup.
Database file or filegroup	Backs up specific database files. Requires transaction log backups.	Very large databases (VLDBs) that cannot be backed up in a practical amount of time.

Verifying Backups

You should verify your backups to confirm that you are able to restore the intended data and objects and that the backups contain valid information. Before you restore a backup, you must perform specific tasks that enable you to begin the restore process.

Before you restore a backup file, you must ensure that it is valid and that the file contains the expected backups. You can use SQL Server Enterprise Manager to view the contents of each backup device. For more detailed information about the backups, you can execute the Transact-SQL statements listed in the following table.

Transact-SQL Statement	Use for
RESTORE HEADERONLY	Obtaining the header information of a particular backup file or backup set
RESTORE FILELISTONLY	Obtaining information about the original database or transaction log files that are contained in a backup file
RESTORE LABELONLY	Obtaining information about the backup media that hold a backup file
RESTORE VERIFYONLY	Verifying that the individual files that make up the backup set are complete and that all backups are readable

Restoring Databases

The SQL Server automatic recovery process occurs whenever SQL Server is started and can be initiated manually during restore operations. The automatic recovery process is an internal mechanism that ensures that your database is consistent by examining the transaction log and taking appropriate actions. Your databases must be in a consistent state before you begin a restore, and the automatic recovery process guarantees this, even after an unexpected failure. Figure 13.2 shows the automatic recovery process for the transaction logs for three databases. For the first transaction log, the automatic recovery process rolls transactions 2 and 3 forward and rolls back transaction 3. For the second transaction log, the automatic recovery process rolls transaction 2 forward and rolls back transaction 1. For the third transaction log, the automatic recovery process rolls transactions 1 and 3 forward and rolls back transaction 2.

Figure 13.2 The SQL Server automatic recovery process

SQL Server examines the transaction log from the last checkpoint to the point at which SQL Server failed or was shut down. A checkpoint is like a bookmark, marking the point at which all data changes were written to the database.

• If the transaction log has committed transactions that are not yet written to the database, SQL Server rolls these transactions forward, applying the changes to the database.
• If the transaction log contains any uncommitted transactions, SQL Server rolls back these transactions. Uncommitted transactions are not written to the database, because they contain incomplete data that would leave the tables of the database in an inconsistent state.

Automatic Recovery

When you restart your system after a failure or shutdown, SQL Server begins the automatic recovery process to ensure data consistency. You do not have to start this process manually it occurs automatically.

Manual Recovery

You can manually initiate the recovery process when you perform restore operations. The recovery process that you initiate is similar to the automatic recovery process that occurs when SQL Server is restarted. To initiate a manual recovery, execute the RESTORE DATABASE statement with the RECOVERY clause.

How to Restore Backups

You can use SQL Server Enterprise Manager or the RESTORE statement and specify the options that are specific to the type of backup that you want to restore. You also determine whether to initiate the recovery process after each restore operation.

When you want to restore databases, transaction logs, or files, consider the following guidelines:

• You should obtain information about the backups that you plan to restore. You must ensure that the files are valid and contain all of the backups that you require in order to restore the database to a consistent state.
• You must know the type of backup method that was used to perform the backup. Confirm that your backup files contain the backups that you want to restore. Make sure that the backups are valid and that you have all files or tapes that contain the backup set.
• If your business environment requires that the production server is always accessible, you may want to consider using a standby SQL Server.
• If the media that contain your system databases are damaged, you may have to rebuild the system databases before restoring your databases.

Lesson Summary

Backup and restore is a standard part of SQL Server database administration and must be performed for data warehouse databases as for other SQL Server databases. As well as performing backup and restore operations, you must ensure that you have reliable copies of the scripts, packages, and other objects that are necessary to restore normal operations in case of failure.