Backup a database, transaction log, file(s) or filegroup(s).
Syntax
--Back up a whole database
BACKUP DATABASE { database_name | @database_name_var }
TO backup_device [ ,...n ]
[ MIRROR TO clause ] [ next-mirror-to ]
[ WITH { DIFFERENTIAL
| general_WITH_options [ ,...n ] } ]
[;]
--Back up specific files or filegroups
BACKUP DATABASE { database_name | @database_name_var } file_or_filegroup [ ,...n ]
TO backup_device [ ,...n ]
[ MIRROR TO clause ] [ next-mirror-to ]
[ WITH { DIFFERENTIAL | general_WITH_options [ ,...n ] } ]
[;]
--Create a partial backup
BACKUP DATABASE { database_name | @database_name_var }
READ_WRITE_FILEGROUPS [ , read_only_filegroup [ ,...n ] ]
TO backup_device [ ,...n ]
[ MIRROR TO clause ] [ next-mirror-to ]
[ WITH { DIFFERENTIAL | general_WITH_options [ ,...n ] } ]
[;]
--Back up the transaction log (full and bulk-logged recovery models)
BACKUP LOG { database_name | @database_name_var }
TO backup_device [ ,...n ]
[ MIRROR TO clause ] [ next-mirror-to ]
[ WITH { general_WITH_options | log_specific_options } [ ,...n ] ]
[;]
--Back up all the databases on an instance of SQL Server (a server)
ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP ON
[;]
BACKUP SERVER
TO backup_device [ ,...n ]
[ MIRROR TO clause ] [ next-mirror-to ]
[ WITH { METADATA_ONLY
| general_WITH_options [ ,...n ] } ]
[;]
--Back up a group of databases
ALTER DATABASE database
SET SUSPEND_FOR_SNAPSHOT_BACKUP ON
ALTER DATABASE <...>
SET SUSPEND_FOR_SNAPSHOT_BACKUP ON
...
BACKUP GROUP {database [,... ]}
TO backup_device [ ,...n ]
[ MIRROR TO clause ] [ next-mirror-to ]
[ WITH { METADATA_ONLY
| general_WITH_options [ ,...n ] } ]
[;]
backup_device::=
{
{ logical_device_name | @logical_device_name_var }
| { DISK
| TAPE
| URL } =
{ 'physical_device_name' | @physical_device_name_var | 'NUL' }
}
MIRROR TO clause::=
MIRROR TO backup_device [ ,...n ]
file_or_filegroup::=
{
FILE = { logical_file_name | @logical_file_name_var }
| FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var }
}
read_only_filegroup::=
FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var }
general_WITH_options [ ,...n ]::=
--Backup Set Options
COPY_ONLY
| [ COMPRESSION [ ( ALGORITHM = { MS_XPRESS | ZSTD | accelerator_algorithm } [, LEVEL = { LOW | MEDIUM | HIGH } ] ) ] | NO_COMPRESSION ]
| DESCRIPTION = { 'text' | @text_variable }
| NAME = { backup_set_name | @backup_set_name_var }
| CREDENTIAL
| ENCRYPTION
| FILE_SNAPSHOT
| { EXPIREDATE = { 'date' | @date_var }
| RETAINDAYS = { days | @days_var } }
| { METADATA_ONLY | SNAPSHOT }
--Media Set Options
{ NOINIT | INIT }
| { NOSKIP | SKIP }
| { NOFORMAT | FORMAT }
| MEDIADESCRIPTION = { 'text' | @text_variable }
| MEDIANAME = { media_name | @media_name_variable }
| BLOCKSIZE = { blocksize | @blocksize_variable }
--Data Transfer Options
BUFFERCOUNT = { buffercount | @buffercount_variable }
| MAXTRANSFERSIZE = { maxtransfersize | @maxtransfersize_variable }
--Error Management Options
{ NO_CHECKSUM | CHECKSUM }
| { STOP_ON_ERROR | CONTINUE_AFTER_ERROR }
--Compatibility Options
RESTART
--Monitoring Options
STATS [ = percentage ]
--Tape Options
{ REWIND | NOREWIND }
| { UNLOAD | NOUNLOAD }
--Encryption Options
ENCRYPTION (ALGORITHM = { AES_128 | AES_192 | AES_256 | TRIPLE_DES_3KEY } , encryptor_options )
encryptor_options ::=
SERVER CERTIFICATE = Encryptor_Name | SERVER ASYMMETRIC KEY = Encryptor_Name
log_specific_options [ ,...n ]::=
--Log-specific Options
{ NORECOVERY | STANDBY = undo_file_name }
| NO_TRUNCATE
DATABASE Specifies a complete database backup. If a list of files and filegroups is specified, only those files and filegroups are backed up. During a full or differential database backup, SQL Server backs up enough of the transaction log to produce a consistent database when the backup is restored.
LOG Specifies a backup of the transaction log only. The log is backed up from the last successfully executed log backup to the current end of the log. Before you can create the first log backup, you must create a full backup.
GROUP (database,...n) Introduced in SQL Server 2022 (16.x). Back up a group of databases. Uses snapshot backup. Requires WITH METADATA_ONLY.
SERVER Introduced in SQL Server 2022 (16.x). Back up all databases on an instance of SQL Server. Uses snapshot backup. Requires WITH METADATA_ONLY.
METADATA_ONLY Introduced in SQL Server 2022 (16.x). Required for snapshot backup. BACKUP SERVER, or BACKUP GROUP...
{ database_name | @database_name_var } Is the database from which the transaction log, partial database, or complete database is backed up. If supplied as a variable (@database_name_var), this name can be specified either as a string constant (@database_name_var=database name) or as a variable of character string data type, except for the ntext or text data types.
file_or_filegroup [ ,...n ] Used only with BACKUP DATABASE, specifies a database file or filegroup to include in a file backup, or specifies a read-only file or filegroup to include in a partial backup.
FILE = { logical_file_name | @logical_file_name_var } Is the logical name of a file or a variable whose value equates to the logical name of a file that is to be included in the backup.
FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var }
Is the logical name of a filegroup or a variable whose value equates to the logical name of a filegroup that is to be included in the backup. Under the simple recovery model, a filegroup backup is allowed only for a read-only filegroup.
READ_WRITE_FILEGROUPS [ , FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var } [ ,...n ] ] Specifies a partial backup. A partial backup includes all the read/write files in a database: the primary filegroup and any read/write secondary filegroups, and also any specified read-only files or filegroups.
READ_WRITE_FILEGROUPS Specifies that all read/write filegroups be backed up in the partial backup. If the database is read-only, READ_WRITE_FILEGROUPS includes only the primary filegroup.
FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var } Is the logical name of a read-only filegroup or a variable whose value equates to the logical name of a read-only filegroup that is to be included in the partial backup. For more information, see "<file_or_filegroup>," earlier in this article.
TO backup_device [ ,...n ] Indicates that the accompanying set of backup devices is either an unmirrored media set or the first of the mirrors within a mirrored media set (for which one or more MIRROR TO clauses are declared).
{ logical_device_name | @logical_device_name_var } Applies to: SQL Server Is the logical name of the backup device to which the database is backed up. The logical name must follow the rules for identifiers.
{ DISK | TAPE | URL} = { 'physical_device_name' | @physical_device_name_var | 'NUL' }
Specifies a disk file or tape device, or a URL. The URL format is used for creating backups to Microsoft Azure Blob Storage or S3-compatible object storage.
MIRROR TO <backup_device> [ ,...n ] Specifies a set of up to three secondary backup devices, each of which mirrors the backups devices specified in the TO clause. The MIRROR TO clause must specify the same type and number of the backup devices as the TO clause. The maximum number of MIRROR TO clauses is three. This option is available only in the Enterprise edition of SQL Server.
CREDENTIAL Used only when creating a backup to Azure Blob Storage.
FILE_SNAPSHOT Used to create an Azure snapshot of the database files when all of the SQL Server database files are stored using the Azure Blob Storage.
DIFFERENTIAL Used only with BACKUP DATABASE, specifies that the database or file backup should consist only of the portions of the database or file changed since the last full backup. A differential backup usually takes up less space than a full backup.
ENCRYPTION Used to specify encryption for a backup. You can specify an encryption algorithm to encrypt the backup with or specify NO_ENCRYPTION to not have the backup encrypted. Encryption is recommended practice to help secure backup files. The list of algorithms you can specify are: AES_128 AES_192 AES_256 TRIPLE_DES_3KEY NO_ENCRYPTION If you choose to encrypt, you will also have to specify the encryptor using the encryptor options.
COPY_ONLY Specifies that the backup is a copy-only backup, which doesn't affect the normal sequence of backups. A copy-only backup is created independently of your regularly scheduled, conventional backups. A copy-only backup doesn't affect your overall backup and restore procedures for the database. Copy-only backups should be used in situations in which a backup is taken for a special purpose, such as backing up the log before an online file restore. Typically, a copy-only log backup is used once and then deleted.
[ COMPRESSION [ ( ALGORITHM = { MS_XPRESS | ZSTD | accelerator_algorithm } [, LEVEL = { LOW | MEDIUM | HIGH } ] ) ] | NO_COMPRESSION ] Specifies whether backup compression is performed on this backup, overriding the server-level default.
{ NOINIT | INIT } Controls whether the backup operation appends to or overwrites the existing backup sets on the backup media. The default is to append to the most recent backup set on the media (NOINIT).
{ NOSKIP | SKIP } Controls whether a backup operation checks the expiration date and time of the backup sets on the media before overwriting them. NOSKIP Instructs the BACKUP statement to check the expiration date of all backup sets on the media before allowing them to be overwritten. This is the default behavior.
{ NOFORMAT | FORMAT } Specifies whether the media header should be written on the volumes used for this backup operation, overwriting any existing media header and backup sets.
NOFORMAT Specifies that the backup operation preserves the existing media header and backup sets on the media volumes used for this backup operation. This is the default behavior. FORMAT Specifies that a new media set be created.
FORMAT causes the backup operation to write a new media header on all media volumes used for the backup operation. The existing contents of the volume become invalid, because any existing media header and backup sets are overwritten.
Specifying FORMAT implies SKIP; SKIP doesn't need to be explicitly stated.
BLOCKSIZE = { blocksize | @blocksize_variable } Specifies the physical block size, in bytes. The supported sizes are 512, 1024, 2048, 4096, 8192, 16384, 32768, and 65536 (64 KB) bytes. The default is 65536 for tape devices and 512 otherwise. Typically, this option is unnecessary because BACKUP automatically selects a block size that is appropriate to the device. Explicitly stating a block size overrides the automatic selection of block size.
BUFFERCOUNT = { buffercount | @buffercount_variable } Specifies the total number of I/O buffers to be used for the backup operation. You can specify any positive integer; however, large numbers of buffers might cause "out of memory" errors because of inadequate virtual address space in the Sqlservr.exe process. The total space used by the buffers is determined by: BUFFERCOUNT * MAXTRANSFERSIZE.
MAXTRANSFERSIZE = { maxtransfersize | @maxtransfersize_variable } Specifies the largest unit of transfer in bytes to be used between SQL Server and the backup media. The possible values are multiples of 65536 bytes (64 KB) ranging up to 4,194,304 bytes (4 MB).
{ NO_CHECKSUM | CHECKSUM } Controls whether backup checksums are enabled.
{ STOP_ON_ERROR | CONTINUE_AFTER_ERROR } Controls whether a backup operation stops or continues after encountering a page checksum error.
RESTART Beginning with SQL Server 2008 (10.0.x), has no effect.
STATS [ = percentage ] Displays a message each time another percentage completes, and is used to gauge progress. If percentage is omitted, SQL Server displays a message after each 10 percent is completed.
{ NORECOVERY | STANDBY = undo_file_name } NORECOVERY Backs up the tail of the log and leaves the database in the RESTORING state. NORECOVERY is useful when failing over to a secondary database or when saving the tail of the log before a RESTORE operation. To perform a best-effort log backup that skips log truncation and then take the database into the RESTORING state atomically, use the NO_TRUNCATE and NORECOVERY options together.
NO_TRUNCATE Specifies that the transaction log shouldn't be not truncated and causes the Database Engine to attempt the backup regardless of the state of the database. Consequently, a backup taken with NO_TRUNCATE might have incomplete metadata. This option allows backing up the transaction log in situations where the database is damaged.
Examples
These assume the backup devices already exist
-- Backup the 'MySample' database to the logical backup device 'MySampleDevice' -- Simple recovery model BACKUP DATABASE MySample TO MySampleDevice -- Back up the full 'MySample2' database to the logical backup device 'MySample2Device'. -- Full recovery model BACKUP DATABASE MySample2 TO MySample2Device -- Then Backup the MySample2 log file. BACKUP LOG MySample2 TO MySample2Device
“We don’t want to go back to tomorrow, we want to go.. forward” ~ Dan Quale
Backup Check script for SQL server (SQLPS)
ALTER DATABASE
DBCC SQLPERF
RESTORE DATABASE Complete
RESTORE DATABASE Partial
RESTORE DATABASE Files
RESTORE LOGS
RESTORE DATABASE_SNAPSHOT
RESTORE FILELISTONLY
RESTORE HEADERONLY
RESTORE LABELONLY
RESTORE VERIFYONLY
sp_addumpdevice
sp_configure
sp_helpfile
sp_helpfilegroup