page contents

About the Post

Author Information

SQL Server Default Database Backup Syntax Values

Every so often I see an article talking about TSQL Database backups and they give an example like the one below:

BACKUP DATABASE [AdventureWorks] 

TO DISK = N’C:\MSSQL11.MSSQLSERVER\MSSQL\Backup\AdventureWorks.bak’

WITH NOFORMAT, NOINIT, NAME = N’AdventureWorks-Full Database Backup’,

NOSKIP, REWIND, UNLOAD, STATS = 10 

GO

Or I may run into a co-worker or friend who passes along a SQL backup job and I see the exact same syntax. Heck, even I was doing it in my older scripts as well, but luckily for me, Robert Davis (Blog | Twitter) pointed the default syntax that SQL Backup uses. Of course he asked me why I was wasting lines of code, which made me scratch my head. I have confirmed this information with Books Online and today I would like to share those with you.

First off before we get too far into this topic, let me answer the question… What are the default TSQL backup syntax? 

They are listed here:

NOINIT, NOSKIP, NOFORMAT, NO_CHECKSUM, STOP_ON_ERROR, REWIND, UNLOAD

As you can see from the example with backup above, I just put in all the default values. So really, I am just typing to practice and wasting time out of my day. :p

So how can you confirm this information that I am sharing with you today? Good question and an excellent practice to make a habit. 😉

You can open SQL Server Books Online (BOL) by going to the following:

  • Start
  • All Programs
  • Documentation and Tutorials
  • SQL Server Books Online

When then looks like this and click on Search:

For this search, just type the word backup and select the BACKUP (Transact-SQL)

Now Scroll down to Media Set Options and take note of the underlined syntax (as highlighted in purple boxes) These are your default syntax levels.

Want more proof? Click (at the top of the article in BOL) on Transact-SQL Syntax Conventions

Take note of this legend:


So, please pass along the word and let others know to stop typing a syntax that are already configured to be a default level. I am sure they would appreciate not having to double their efforts. J

Tags: , , , , ,

Comments are closed.

Copy Protected by Chetan's WP-Copyprotect.