Skip to content

Latest commit

 

History

History
90 lines (68 loc) · 5.55 KB

full-database-backups-sql-server.md

File metadata and controls

90 lines (68 loc) · 5.55 KB
title description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic helpviewer_keywords
Full database backups (SQL Server)
In SQL Server, a full database backup backs up the whole database. Full database backups represent the database at the time the backup finished.
MashaMSFT
mathoma
randolphwest
12/01/2023
sql
backup-restore
how-to
full backups [SQL Server]
backups [SQL Server], database
backing up databases [SQL Server], full backups
estimating database backup size
backing up [SQL Server], size of backup
database backups [SQL Server], full backups
size [SQL Server], backups
database backups [SQL Server], about backing up databases

Full database backups (SQL Server)

[!INCLUDE SQL Server]

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

As a database increases in size, full database backups take more time to finish and require more storage space. Therefore, for a large database, you might want to supplement a full database backup with a series of differential database backups. For more information, see Differential backups (SQL Server).

Important

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

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 backup, when the work-loss exposure returns to zero and a new cycle of work-loss exposure starts. 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.

:::image type="content" source="media/bnr-rmsimple-1-fulldb-backups.gif" alt-text="Diagram showing the work-loss exposure between database backups.":::

Example ([!INCLUDE tsql])

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.

-- Back up the AdventureWorks2022 database to new media set.
BACKUP DATABASE AdventureWorks2022
    TO DISK = 'Z:\SQLServerBackups\AdventureWorksSimpleRM.bak'
    WITH FORMAT;
GO

Database backups under the full recovery model

For databases that use full and bulk-logged recovery, database backups are necessary but not sufficient. Transaction log backups are also required. The following illustration shows the least complex backup strategy that is possible under the full recovery model.

:::image type="content" source="media/bnr-rmfull-1-fulldb-log-backups.gif" alt-text="Diagram showing the series of full database backups and log backups.":::

For information about how to create log backups, see Transaction log backups (SQL Server).

Example ([!INCLUDE tsql])

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 [!INCLUDE ssSampleDBobject] sample database is set to use the full recovery model.

USE master;
GO
ALTER DATABASE AdventureWorks2022 SET RECOVERY FULL;
GO
-- Back up the AdventureWorks2022 database to new media set (backup set 1).
BACKUP DATABASE AdventureWorks2022
  TO DISK = 'Z:\SQLServerBackups\AdventureWorks2022FullRM.bak'
  WITH FORMAT;
GO
--Create a routine log backup (backup set 2).
BACKUP LOG AdventureWorks2022 TO DISK = 'Z:\SQLServerBackups\AdventureWorks2022FullRM.bak';
GO

Use 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. The restored database matches the state of the original database when the database backup finished, minus any uncommitted transactions. Under the full recovery model, you should then restore all subsequent transaction log backups. When the database is recovered, uncommitted transactions are rolled back.

For more information, see Complete Database Restores (Simple Recovery Model) or Complete Database Restores (Full Recovery Model).

Related content