Creating and Restoring a Database Backup

When creating a database backup, the backup operation copies only the data in the database to the backup file; it does not copy out unused space in the database. Because the database backup contains only the actual data in the database, not any empty space, the database backup is likely to be smaller than the database itself. An estimate of the size of the database backup can be determined using the sp_spaceused system stored procedure; the reserved value indicates the estimated size.

Important Microsoft® SQL Server™ does not truncate the transaction log when backing up the database. Therefore, when creating database backups only, it is recommended that the transaction log is set to be truncated automatically every time a checkpoint occurs in the database by setting the trunc. log on chkpt. database option to TRUE. This prevents that transaction log from becoming full, requiring the transaction log to be truncated manually.

If you are producing database backups only, the backup interval should be long enough to keep the backup overhead from affecting production work, yet short enough to prevent the loss of significant amounts of data. Databases that do not contain critical data and have few modifications can be backed up on a weekly or biweekly basis. Data that is more critical or more volatile may need to be backed up daily, or even more frequently. Some databases that are usually read-only may need to be backed up only after a periodic refresh with new data.

It is also prudent to have more than one backup of the database. Have a rotating series of backup media, such that you have two or more versions of the database you can restore. This allows you to address situations where a user may make some incorrect modifications that are not detected for some time, or fall back to an earlier backup if the backup media is damaged.

Note If any files are added or removed from a database, a database backup should be created immediately.

Backup Restrictions

Using SQL Server, backup operations can occur while the database is online and in use. However, some operations are not allowed during a database backup operation:

• Creating or deleting database files
• Creating indexes
• Performing nonlogged operations
• Shrinking either the database (automatically or manually) or the database files. For more information, see Shrinking the Database.

If a backup operation is started when one of these operations is in progress, the backup operation terminates. If a backup operation is in progress and one of these operations is attempted, the operation fails and the backup operation continues.

Restoring a Database Backup

Restoring a database backup returns the database to the same state it was in when the backup was created. When restoring a database, SQL Server re-creates the database and all of its associated files automatically by performing these steps:

1. All of the data from the backup is copied into the database; the rest of the database is created as empty space.
2. Any incomplete transactions in the database backup, (transactions that were not complete when the backup operation completed originally), are rolled back (undone) to ensure the database remains consistent.

This process ensures that the restored database is a copy of the database as it existed when the backup operation completed, except that all incomplete transactions have been rolled back. This is required to restore the integrity of the database.

Additionally, to prevent overwriting a database unintentionally, the restore operation can perform a safety check automatically. The restore operation fails if:

• The database named in the restore operation already exists on the server and the database name does not match the database name recorded in the backup set.
• The database named in the restore operation already exists on the server but it is not the same database as the database contained in the database backup. For example, the database names are the same but each database was created differently.
• One or more files need to be created automatically by the restore operation (regardless of whether the database already exists) but files with the same file name as those that need to be created already exist.

These safety checks can be disabled if the intention is to overwrite another database.

Interrupted Backup and Restore Operations

If a backup or restore operation is interrupted (for example, due to a power loss on the server), it is possible to restart the backup or restore operation from the point it was interrupted. This can be useful if large databases are restored onto other servers as an automated process. If the automated process fails near the end of the restore operation, for example if the power fails, a system administrator can restart the restore operation from where it left off, rather than restoring the whole database from the beginning.

Note Restarting a backup or restore operation can be used only with removable media, such as tapes.

To create a database backup

To create a database backup using the Create Database Backup Wizard

To restore a database backup

To restart an interrupted backup operation

To restart an interrupted restore operation

See Also