SQL Server supports different types of backups for databases. However, which type of a database backup can be performed totally depends upon the recovery model of the database one choose.
Different Types of SQL Server Backups are:-
- Full Backup
- Differential Backup
- Transaction Log Backup
- File Backup
- FileGroup Backup
- Partial Backup
- Copy-Only Backup
- Mirror Backup
Syntax for backup:
BACKUP DATABASE database TO backup_device [ ,...n ] [ WITH with_options [ ,...o ] ] ;
Basic backup set WITH options:
NOINIT | INIT:
Controls whether the backup operation appends to or overwrites the existing backup sets on the backup media.
NOINIT:
NOINIT is the default.
The default is to append to the most recent backup set on the media (NOINIT).
NOINIT Indicates that the backup set is appended to the specified media set, preserving existing backup sets. If a media password is defined for the media set, the password must be supplied.
INIT:
INIT Specifies that all backup sets should be overwritten, but preserves the media header. If INIT is specified, any existing backup set on that device is overwritten, if conditions permit.
COMPRESSION:
In SQL Server 2008 Enterprise and later only, specifies whether backup compression is performed on this backup, overriding the server-level default.
COMPRESSION Explicitly enables backup compression.
NO_COMPRESSION Explicitly disables backup compression.
ENCRYPTION (ALGORITHM, SERVER CERTIFICATE | ASYMMETRIC KEY):
In SQL Server 2014 or later only, specify the encryption algorithm to use, and the Certificate or Asymmetric key to use to secure the encryption.
DESCRIPTION:
Specifies the free-form text that describes the backup set. The string can have a maximum of 255 characters.
NAME :
Specifies the name of the backup set. Names can have a maximum of 128 characters. If NAME is not specified, it is blank.
FORMAT:
Use the FORMAT clause when you are using media for the first time or you want to overwrite all existing data. Optionally, assign the new media a media name and description.
Important
Use extreme caution when you are using the FORMAT clause of the BACKUP statement because this destroys any backups that were previously stored on the backup media.
EXPIREDATE:
Specifies when the backup set expires and can be overwritten.
To ignore the expiration date, use the SKIP option.
RETAINDAYS:
Specifies the number of days that must elapse before this backup media set can be overwritten.
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.
SKIP
SKIP Disables the checking of backup set expiration and name that is usually performed by the BACKUP statement to prevent overwrites of backup sets.
BLOCKSIZE
Specifies the physical block size, in bytes.
BACKUP automatically selects a block size that is appropriate to the device.
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.
NO_CHECKSUM | CHECKSUM:
Controls whether backup checksums are enabled.
NO_CHECKSUM Explicitly disables the generation of backup checksums (and the validation of page checksums). This is the default behavior.
CHECKSUM Specifies that the backup operation verifies each page for checksum and torn page, if enabled and available, and generate a checksum for the entire backup.
STOP_ON_ERROR | CONTINUE_AFTER_ERROR:
Controls whether a backup operation stops or continues after encountering a page checksum error.
STOP_ON_ERROR Instructs BACKUP to fail if a page checksum does not verify. This is the default behavior.
CONTINUE_AFTER_ERROR Instructs BACKUP to continue despite encountering errors such as invalid checksums or torn pages.
If you are unable to back up the tail of the log using the NO_TRUNCATE option when the database is damaged, you can attempt a tail-log log backup by specifying CONTINUE_AFTER_ERROR instead of NO_TRUNCATE.
STATS
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.
REWIND | NOREWIND
REWIND
Specifies that SQL Server releases and rewinds the tape. REWIND is the default.
NOREWIND
Specifies that SQL Server will keep the tape open after the backup operation. You can use this option to help improve performance when performing multiple backup operations to a tape.
Log-specific options
These options are only used with BACKUP LOG.
{ 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.
STANDBY = standby_file_name
Backs up the tail of the log and leaves the database in a read-only and STANDBY state. The STANDBY clause writes standby data (performing rollback, but with the option of further restores).
Using standby mode requires a standby file, specified by standby_file_name, whose location is stored in the log of the database. If the specified file already exists, the Database Engine overwrites it; if the file does not exist, the Database Engine creates it. The standby file becomes part of the database.
NO_TRUNCATE
Specifies that the log 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 log in situations where the database is damaged.
The NO_TRUNCATE option of BACKUP LOG is equivalent to specifying both COPY_ONLY and CONTINUE_AFTER_ERROR.