THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Tibor Karaszi

TSQL script to do backup like Maintenance Plans

This is frequently asked question, both in forums, as well as in class:

How can I though TSQL create new backup files for each backup and have date and time in the file names?

The reason for above is most often that you want a higher degree of customization than what maint plans allow for. An obvious such in 2005 is to add the CHECKSUM option to the BACKUP command. I know there already exist several script like mine "out there", this is just my simple version so I remember where to point whenever I get this question next. :-)

Since I probably have some bugs in it, I have it on my website. This way I can fix the bugs as needed. You are most welcome to report bugs in the script if you spot any! And here's the URL:

Published Tuesday, September 25, 2007 6:20 PM by TiborKaraszi
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



James Luetkehoelter said:

You could always use SQL Agent "Tokens" as well. Gert Drapers has some good examples at Although the 2005 token names have changed a bit, but they're still there :)

September 25, 2007 11:34 AM

TiborKaraszi said:

Yes, Agent Tokens, the often forgotten gem...

I prefer to use them for things such as naming the output file (say you want a unique output file for each execution). For the TSQL stuff, I find it valuable to keep everything as ... TSQL. Makes it easier to run and test in a query window. Matter of personal preference, of course. :-)

September 25, 2007 12:03 PM

James Luetkehoelter said:

I'd actually agree with your approach here and use the TSQL. Usually I only use Tokens for the output files as well. For a more focused backup scheme on a single database, I'll usually use tokens for the dynamic naming. If more than one database is involved (or a dynamic number, such as a hosting server or a development one that is in constant flux), a TSQL script such as what you've got here is the ticket :)

Ticket, Token? Almost sounds like comparing Kerberos to NTLM :) Ack! :)

September 26, 2007 12:11 AM

PeterW said:

You could always use the QUOTENAME function to make things a little safer:

SET @sql = 'BACKUP ' + CASE @backup_type WHEN 'LOG' THEN 'LOG' ELSE 'DATABASE' END + ' ' + QUOTENAME(@db_name) + @crlf

SET @sql = @sql + 'TO DISK = ' + QUOTENAME(@full_path_and_filename,'''') + @crlf

SET @sql = @sql + 'WITH' + @crlf

SET @sql = @sql + ' NOINIT,' + @crlf

SET @sql = @sql + ' NAME = ' + QUOTENAME(@filename,'''') + ',' + @crlf

September 26, 2007 6:11 AM

TiborKaraszi said:

Thanks for the reminder of using QUOTENAME, Peter. I added a few words about this in the article, let me know if you want to hyperlink your name, change it or so...

October 15, 2007 11:09 AM

Ola Hallengren said:

I would like to share some code for doing backups that works a little differently than Tibor's. It is available on

January 9, 2008 2:31 PM

Tibor Karaszi said:

January 11, 2008 5:04 AM

Leave a Comment


This Blog


Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement