Backup and Restore

Flags in this category fall into the below groupings:

  1. Informational –> Flags that return info about the BACKUP or RESTORE process.
  2. Functionality Toggles –> Flags that modify how BACKUP or RESTORE behavior.
  3. Fixes and Past Relevance –> Fix flags and flags which are no longer relevant.

See also:

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?


Short Descriptions

Flag Short Description
See Also  
205 Reports to error log when recompile occurs due to auto-update of stats.
 
Info  
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.
 
Func  
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.
 
Fix/PastRel  
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.

Informational

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.”

Erland_1

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

CSS_1_ | SQLPFE_1 | SQLMunkee

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).

CSS_2

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.

<links needed>

3212 (Info)

Prints “Backup stats” to the SQL log.

Nacho_1

3213 (Info)

Prints info about BACKUP parameter values used, especially regarding Buffer size/number and Max Transfer size.

CSS_2 | CSS_3

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.

JamesSQL

3226 Doc2008 (Info)

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.”

StorEng_1 | PRand_1

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)

Connect_1


Functionality Toggles

3034

(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.

Nacho_2

3035

(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.

Nacho_2

3039

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.

Nacho_2

3042 Doc2012

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.

2001026 | CSS_4

3205 Doc2005
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.”
3222

Disables the read ahead that is used by the recovery operation during roll forward operations.

268081

3422

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].

IO Basics, chapter 2 | PRand_2

3607

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.

Nacho_1

3608 Doc2008

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.”

Nacho_1 | Nacho_3

3609

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.

PRand_3 | Nacho_1

3660

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.

Nacho_4


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.

3023 Doc2014

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.”

2656988

3028

Enables a hotfix for a problem encountered when backing up to tape with specific backup options.

940379

3031

Will turn the NO_LOG and TRUNCATE_ONLY options into checkpoints in all recovery modes (applicable to SQL 2005).

PRand_4

3057

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.

2987585

3101

Causes the RESTORE process to bypass a CDC upgrade operation that can cause slow RESTORE operations under certain conditions.

2567366

3111

“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.

297104

3117

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.

915385

3231

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.

PRand_4 | KTripp_1

9109

Used to workaround a problem with query notifications and restoring a DB with the NEW_BROKER option enabled.

2483090

9958

Enables a fix that allows the restore of a Hekaton transaction log backup when a certain bug is hit.

3171002