Full Database Backups

A full database backup backs up the whole database. This includes part of the transaction log so that the full database backup can be recovered. Full database backups represent the database at the time the backup finished.

Note

For an overview of the various types of backups, see either Backup Under the Simple Recovery Model or Backup Under the Full Recovery Model.

Database backups are easy to use. A full database backup contains all the data in the database. For a small database that can be backed up quickly, the best practice is to use just full database backups. However, as a database becomes larger, full backups take more time to finish and require more storage space. Therefore, for a large database, you might want to supplement full database backups with differential backups. For more information, see Differential Database Backups.

Note

TRUSTWORTHY is set to OFF on a database backup. For more information about how to set TRUSTWORTHY to ON, see ALTER DATABASE (Transact-SQL).

Using Database Backups Under the Simple Recovery Model

Under the simple recovery model, after each backup, the database is exposed to potential work loss if a disaster were to occur. The work-loss exposure increases with each update until the next full backup, when the work-loss exposure returns to zero and a new cycle of work-loss exposure starts.

Under the simple recovery model, work-loss exposure increases over time between backups. The following illustration shows the work-loss exposure for a backup strategy that uses only full database backups.

Example

The following example shows how to create a full database backup by using WITH FORMAT to overwrite any existing backups and create a new media set. Then, the example backs up the transaction log. In a real-life situation, you would have to perform a series of regular log backups.

-- Back up the AdventureWorks database to new media set.
BACKUP DATABASE AdventureWorks
    TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\BACKUP\AdventureWorksFullRM.bak' 
    WITH FORMAT;
GO

Using Database Backups Under the Full Recovery Model

For databases that use full and bulk-logged recovery, transaction log backups are required. The following illustration shows the least complex backup strategy that is possible under the full recovery model.

Note

If you have two or more full-recovery-model databases that must be logically consistent, you may have to implement special procedures to make sure the recoverability of these databases. For more information, see Using Marked Transactions (Full Recovery Model).

For information about how to create log backups, see Working with Transaction Log Backups.

Example

The following example shows how to create a full database backup by using WITH FORMAT to overwrite any existing backups and create a new media set. Then, the example backs up the transaction log. In a real-life situation, you would have to perform a series of regular log backups. For this example the AdventureWorks sample database must be set to use the full recovery model.

USE master;
ALTER DATABASE AdventureWorks SET RECOVERY FULL;
GO
-- Back up the AdventureWorks database to new media set (backup set 1).
BACKUP DATABASE AdventureWorks
  TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\BACKUP\AdventureWorksFullRM.bak' 
  WITH FORMAT;
GO
--Create a routine log backup (backup set 2).
BACKUP LOG AdventureWorks TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\BACKUP\AdventureWorksFullRM.bak';
GO

Creating a Full Database Backup

A full database backup is created in a single operation and is usually scheduled to occur at set intervals.

The required BACKUP syntax for creating a full database backup is:

BACKUP DATABASE database_name TO backup_device

To create a full database backup

• How to: Create a Full Database Backup (Transact-SQL)
• How to: Back Up a Database (SQL Server Management Studio)
• SqlBackup (SMO)

To schedule backup jobs

• Maintenance Plan Wizard

Using Differential Database Backups

A database backup can serve as the differential base for differential database backups. This lets you supplement a database backup with a short series of differential database backups. For more information, see Using Differential Backups and Differential Database Backups.

Using a Full Database Backup to Restore the Database

You can re-create a whole database in one step by restoring the database from a full database backup to any location. Enough of the transaction log is included in the backup to let you recover the database to the time when the backup finished. When the database is recovered, uncommitted transactions are rolled back. The restored database matches the state of the original database when the restored backup finished, minus any uncommitted transactions.

The restore operation creates the database in the specified destination. If the database already exists in the specified location, the restore operation overwrites the existing database. To avoid overwriting an existing database, specify a different name for the restored database.

For more information, see Performing a Complete Database Restore (Simple Recovery Model) or Performing a Complete Database Restore (Full Recovery Model).

See Also

Concepts

Backing Up and Restoring Federated Database Servers
Security Considerations for Backup and Restore
Backing Up Read-Only Databases
Maintenance Plan Wizard
Setting Database Options

Other Resources

Backing Up and Restoring an Analysis Services Database
Databases (Database Engine)

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release	History
17 July 2006	New content: Added summary of the basic BACKUP syntax for creating a full database backup. Changed content: Revised the introduction.