THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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:

http://www.karaszi.com/SQLServer/util_backup_script_like_MP.asp

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

Comments

 

James Luetkehoelter said:

You could always use SQL Agent "Tokens" as well. Gert Drapers has some good examples at http://sqldev.com. 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 http://ola.hallengren.com.

January 9, 2008 2:31 PM
 

Tibor Karaszi said:

January 11, 2008 5:04 AM

Leave a Comment

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