Database Backups
A database backup creates a duplicate of the data that is in the database when the backup completes. This is a single operation, usually scheduled at regular intervals. Database backups are self-contained.
You can re-create the entire database from a database backup in one step by restoring the database. The restore process overwrites the existing database or creates the database if it does not exist. The restored database will match the state of the database at the time the backup completed, minus any uncommitted transactions. Uncommitted transactions are rolled back when the database is recovered.
A database backup uses more storage space per backup than transaction log and differential database backups. Consequently, database backups need more time to complete the backup operation and so are typically created less frequently than differential database or transaction log backups. For more information, see Transaction Log Backups and Differential Database Backups.
Restoring a Database Backup
Restoring a database backup re-creates the database and all of its associated files that were in the database when the backup was completed. However, any modifications made to the database after the backup was created are lost. To restore transactions made after the database backup was created, you must use transaction log backups or differential backups.
When restoring a database, Microsoft® SQL Server™:
- Copies all of the data from the backup into the database. The rest of the database is created as empty space.
- Rolls back any incomplete transactions in the database backup to ensure that the database is consistent.
To prevent overwriting a database unintentionally, the restore operation performs safety checks automatically. The restore operation fails if:
- The database name in the restore operation does not match the database name recorded in the backup set.
- The database named in the restore operation already exists on the server but is not the same 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, but the file names already exist.
These safety checks can be disabled if the intention is to overwrite another database. For more information, see RESTORE.
Note If you restore a database on a different instance of SQL Server than the one on which the backup was created, you may need to run sp_change_users_login to update user login information. For more information, see sp_change_users_login.
Backing Up Full-Text Indexes
Backing up a database does not back up full-text index data in full-text catalogs. However, if full-text indexes have been defined for tables, the meta data is backed up when a database backup is created. After a database backup is restored, the full-text index catalogs can be re-created and repopulated. For more information, see Full-text Indexes.
Estimating the Size of Your Database Backup
Before you implement a backup and restore strategy, you need to estimate how much disk space your database backup will use. During a database backup, the backup operation copies only the data in the database to the backup file. Because the database backup contains only the actual data in the database and not any unused space, the database backup is likely to be smaller than the database itself. You can estimate the size of the database backup by using the sp_spaceused system stored procedure. For more information, see sp_spaceused.
To create a database backup