Backup and Restore¶
Flags in this category fall into the below groupings:
- Informational –> Flags that return info about the BACKUP or RESTORE process.
- Functionality Toggles –> Flags that modify how BACKUP or RESTORE behavior.
- Fixes and Past Relevance –> Fix flags and flags which are no longer relevant.
TODO: 3028 may need to be moved to a fix flag section, ditto for 3057, 3111, 3117 TODO: 3607 should at least be referenced in the TranLog Recovery section, and in the CheckDB Corruption section. TODO: 9958 should at least be referenced in the TranLog Recovery section TODO: does 3222 belong here, or as a See Also anywhere else?
|205||Reports to error log when recompile occurs due to auto-update of stats.|
|3001||Suppresses logging to msdb.dbo.backuphistory|
|3004||Prints info (e.g. file initialization) re: BACKUP/RESTORE operations|
|3014||Prints info about config options used during BACKUP.|
|3210||Prints info about Async Disk Pool thread collision and wait times.|
|3212||Prints “backup stats” to the SQL log.|
|3213||Prints info about BACKUP param values used, e.g. Max Transfer Size|
|3216||Prints info about RESTORE internals.|
|* 3226||Suppresses BACKUP messages to the error log.|
|3400||Appears to print out various info about RESTORE process.|
|3034||Overrides server default and forces backup compression|
|3035||Overrides server default and avoids backup compression|
|3039||Allows VDI backups to be affected by the server default compression setting.|
|3042||Causes BACKUP to allocate size of .bak file as needed instead of up-front.|
|3205||Disables hardware compression for tape drivers during BACKUP or RESTORE.|
|3222||Disables read-ahead used during recovery operation during roll-forward.|
|3422||Enables auditing of t-log records during rollback or log recovery.|
|3607||Lets SQL Server open the master database w/out running recovery on it.|
|3608||SQL Server only starts up master, not any other DB.|
|3609||Skips creation of tempdb at startup.|
|3660||Forces database recovery at SQL Server startup even for auto-close DBs.|
|3023||Enables CHECKSUM option as default for all BACKUPs.|
|3028||Enables a hotfix for problems backing up to tape.|
|3031||Turns NO_LOG and TRUNCATE_ONLY into checkpoints.|
|3057||Allows log backups w/sector size 512 to be restore on sector size 4096.|
|3101||Causes RESTORE to bypass a CDC upgrade operation.|
|3111||Enables a fix for backing up very large T-log files.|
|3117||Enables a fix when restoring from a file/FG-based snapshot backup.|
|3231||Turns NO_LOG/TRUNCATE_ONLY into no-ops/log clears based on recov mode.|
|9109||Enables a workaround for restoring a DB w/query notifs and NEW_BROKER.|
|9958||Enables a fix for a bug restoring a t-log with hekaton records.|
- 3001 (Info)
Erland: “suppresses logging to msdb.backuphistory…undocumented with all that means. On the other hand, I got [it] from a Microsoft engineer who said it was OK to share them.”
- 3004 (Info)
Prints trace info to the SQL error log about RESTORE (and BACKUP?) operations and can be used to view file initialization; use with 3605 to direct to error log
- 3014 (Info)
Prints info to the error log about config option values chosen during the BACKUP command. In the CSS blog post, used with 3213 (not sure how they are different; more testing is needed).
- 3210 (Info)
Bob Ward PASS 2014 IO talk: prints information about “collisions and wait times” that occur between the various “Asynchronous Disk Pool” threads during BACKUP (what about RESTORE?) operations.
- 3212 (Info)
Prints “Backup stats” to the SQL log.
- 3213 (Info)
Prints info about BACKUP parameter values used, especially regarding Buffer size/number and Max Transfer size.
- 3216 (Info)
Prints info about RESTORE internals. Only seems to print to the error log (TF 3605 is required). Not able to find an official link.
BOL 2014: “By default, every successful backup operation adds an entry in the SQL Server error log and in the system event log. If you create very frequent log backups, these success messages accumulate quickly, resulting in huge error logs in which finding other messages is problematic.
With this trace flag, you can suppress these log entries. This is useful if you are running frequent log backups and if none of your scripts depend on those entries.”
- 3400 (Info)
Connect: appears (based on context) to print information re: the RESTORE process. BWard PASS 2014 IO talk: explained as printing out “checkpoint pages/sec” (to the Error Log, presumably)
(is this just for VDI?) overrides the server default, and thus always forces backup compression unless the backup command had the no_compression clause explicitly present.
(is this just for VDI?) overrides the server default to always avoid compression, unless the backup command explicitly uses the compression clause. If both 3034 and 3035 are enabled, 3035 takes precedence.
As long as the SQL edition supports backup compression, this will allow VDI backups to be affected by the default compression setting just as non-VDI BACKUP commands are affected.
BOL 2014: “Bypasses the default backup compression pre-allocation algorithm to allow the backup file to grow only as needed to reach its final size. This trace flag is useful if you need to save on space by allocating only the actual size required for the compressed backup. Using this trace flag might cause a slight performance penalty (a possible increase in the duration of the backup operation).”
The KB article discusses the algorithm used to estimate space when the TF is NOT on.
- BOL 2014: “By default, if a tape drive supports hardware compression, either the DUMP or BACKUP statement uses it. With this trace flag, you can disable hardware compression for tape drivers. This is useful when you want to exchange tapes with other sites or tape drives that do not support compression.”
Disables the read ahead that is used by the recovery operation during roll forward operations.
PRand: “Cause auditing of transaction log records as they’re read (during transaction rollback or log recovery). This is useful because there is no equivalent to page checksums for transaction log records and so no way to detect whether log records are being corrupted.” [There is a CPU hit for turning this on].
Khen2005, page 80: lets SQL open master w/out running recovery on it. Other sources say that SQL doesn’t try to “start up” master. The differences in wording may not be important.
BOL 2014: “Prevents SQL Server from automatically starting and recovering any database except the master database. If activities that require tempdb are initiated, then model is recovered and tempdb is created. User databases will be started and recovered when accessed. Some features, such as snapshot isolation and read committed snapshot, might not work.”
Skips the creation of the tempdb database at startup. Use this trace flag if the device or devices on which tempdb resides are problematic or problems exist in the model database.
W/o this flag, for DBs that have Auto_Close=true and for DBs on Express Edition, DB recovery is normally deferred until first user access when SQL starts up. This TF forces DB recovery to always run (well, only for DBs that actually need recovery done) at SQL Server startup.
Fixes and Past Relevance¶
These flags either are old and irrelevant for modern builds, appear only in CTPs, or enable a fix in a CU but are baselined in a later service pack or release.
BOL 2014: “Enables CHECKSUM option as default for BACKUP command. Note: Beginning with SQL Server 2014 this behavior is controlled by setting the backup checksum default configuration option.”
Enables a hotfix for a problem encountered when backing up to tape with specific backup options.
Will turn the NO_LOG and TRUNCATE_ONLY options into checkpoints in all recovery modes (applicable to SQL 2005).
Enables a hotfix change that allows a log backup that was taken on a t-log file hosted on a drive with “Bytes per physical sector”=512 to be restored onto a log file/drive that has “Bytes per physical sector”=4096.
Causes the RESTORE process to bypass a CDC upgrade operation that can cause slow RESTORE operations under certain conditions.
“FIX: Backup or Restore Using Large Transaction Logs May Return Error 3241” Causes LogMgr::ValidateBackedupBlock to be skipped during backup and restore operations, allowing backups of very large T-logs to succeed.
Enables a fix in 2005 when restoring from a file- or filegroup-based snapshot backup. 3117 causes the restore process to use an approach found in SQL 2000 rather than 2005’s logic.
SQL 2000/2005 - Will turn the NO_LOG and TRUNCATE_ONLY options into no-ops in FULL/BULK_LOGGED recovery mode, and will clear the log in SIMPLE recovery mode.
Used to workaround a problem with query notifications and restoring a DB with the NEW_BROKER option enabled.
Enables a fix that allows the restore of a Hekaton transaction log backup when a certain bug is hit.