Perform database backup with options
In this we will learn how to Perform database backups with options.
Additional considerations about BACKUP options
Interaction of SKIP, NOSKIP, INIT, and NOINIT
This describes interactions between the { NOINIT | INIT } and { NOSKIP | SKIP } options.
Skip option: NOSKIP
NOINIT: However, if the volume contains a valid media header, verifies that the media name matches the given MEDIANAME, if any. If it matches, appends the backup set, preserving all existing backup sets.
INIT: If the volume does not contain a valid media header, an error occurs. And, if the volume contains a valid media header, performs the following checks:
- Firstly, if MEDIANAME was specified, verifies that the given media name matches the media header’s media name.
- Secondly, verifies that there are no unexpired backup sets already on the media. If there are, terminates the backup.
Further, if these checks pass, overwrites any backup sets on the media, preserving only the media header. And, if the volume does not contain a valid media header, generates one with using specified MEDIANAME and MEDIADESCRIPTION, if any.
Skip Option: SKIP
NOINIT: If the volume contains a valid media header, appends the backup set, preserving all existing backup sets.
INIT: If the volume contains a valid2 media header, overwrites any backup sets on the media, preserving only the media header. However, if the media is empty, generates a media header using the specified MEDIANAME and MEDIADESCRIPTION, if any.
General remarks
Database or log backups can be appended to any disk or tape device, allowing a database and its transaction logs to be kept within one physical location. The BACKUP statement is not allowed in an explicit or implicit transaction. Further, cross-platform backup operations can be performed as long as the collation of the database is supported by the operating system.
However, starting with SQL Server 2016 (13.x), setting MAXTRANSFERSIZE larger than 65536 (64 KB) enables an optimized compression algorithm for Transparent Data Encryption (TDE) encrypted databases that first decrypts a page, compresses it, and then encrypts it again. And, if MAXTRANSFERSIZE is not specified, or if MAXTRANSFERSIZE = 65536 (64 KB) is used, backup compression with TDE encrypted databases directly compresses the encrypted pages, and may not yield good compression ratios.
Interoperability
SQL Server uses an online backup process to allow a database backup while the database is still in use. During a backup, most operations are possible. For example, INSERT, UPDATE, or DELETE statements are allowed during a backup operation.
Operations that cannot run during a database or transaction log backup include:
- Firstly, file management operations such as the ALTER DATABASE statement with either the ADD FILE or REMOVE FILE options.
- Secondly, shrink database or shrink file operations. This includes auto-shrink operations.
However, if a backup operation overlaps with a file-management or shrink operation, a conflict arises. Regardless of which of the conflicting operation began first, the second operation waits for the lock set by the first operation to time out. Further, if the lock releases during the time-out period, the second operation continues. And, if the lock times out, the second operation fails.
Reference: Microsoft Documentation