Backing Up and Restoring a SQL Database
When you create 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—no 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.
Microsoft SQL Server does not truncate the transaction log when backing up the database. Therefore, when you are creating database backups, it is recommended that you set the transaction log 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 the transaction log from becoming full, which would require the transaction log to be truncated manually.
If you are producing only database backups, 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. It is recommended that you maintain a rotating series of backup media, so that you have two or more versions of the database you can restore. This allows you to address situations in which a user may make some incorrect modifications that are not detected for some time, or to fall back to an earlier backup if backup media is damaged.
Backup Restrictions
Using SQL Server, backup operations can occur while the database is online and in use. However, the following operations are not allowed during a database backup operation:
Creating or deleting database files
Creating indexes
Performing non-logged operations
Shrinking either the database (automatically or manually) or the database files
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 you restore a database, SQL Server recreates the database and all of its associated files automatically by performing these steps:
All of the data from the backup is copied into the database; the rest of the database is created as empty space.
Any incomplete transactions in the database backup (transactions that were not complete when the backup operation completed originally) are rolled back (undone) to ensure that 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 (if, for example, there is a power failure), a system administrator can restart the restore operation from where it left off, rather than restoring the whole database from the beginning.
See Also
Minimizing Backup and Recovery Times in Mission-Critical Environments