Backup SQL Server Databases

Tue Apr 7, 2009 by Tara Kizer in sql-server-database-administration

EDIT: new version of stored procedure is located here. Use the new version for 2005/2008. Use the below version for 2000.

I have modified isp_Backup, which is the stored procedure that I use to backup SQL Server databases. In this version, I fixed two bug and added a feature. Here are the changes:

Fixed "2000 backupset" bug by qualifying the object to msdb.dbo.backupset
Fixed the full backup check for SIMPLE recovery model databases (bug found by Eugene from PerfSpot.com)
Added Red Gate SQL Backup functionality

If you have scheduled jobs that are using any of my previous versions, you might need to modify the job step so that it uses the new @bkpSwType input parameter rather than the @liteSpeed parameter.

You can download the revised stored procedure here.

In the download, I have provided a script, ModifyJobStep.sql, to update the job step by replacing @liteSpeed with @bkpSwType. This only needs to be run if the job step includes @liteSpeed. You may not have this if you were using native backups since "@liteSpeed = N" was the default. If the script fails, run it one more time. There is some sort of SQL Server bug that is encountered on some systems, but it doesn't appear if you run it a second time. It is not a bug with the script though.

Let me know if you encounter any issues with this version so that I can get them fixed.

In the next version, I will add support for SQL Server 2008. It is a rather quick modification, but I need to do some additional testing of it before I release it to the Internet. If you'd like the next version before I finish my testing, just send me an email.

Legacy Comments


qaisar 2009-04-09	re: Backup SQL Server Databases ok

Jessica 2009-04-09	re: Backup SQL Server Databases Hello Tara, thanks so much for this code, works really great! I am having a problem though when the path I'm passing is not on the local server, but on the network, it gives me this error: Executed as user: NT AUTHORITY\SYSTEM. Cannot open backup device '\\USERS\ELIME2\ELIME2_20090409115041.BAK'. Operating system error 3(The system cannot find the path specified.) It also happens if I map the drive and write it that way too. Do you know what could be happening? Thank you.

collen 2009-04-21	re: Backup SQL Server Databases iTS COOL

Dirk Hondong 2009-04-21	re: Backup SQL Server Databases Hello Tara, first of all thank you for this great script. It helped me to change our backup concept a little bit in our company. There is one little issue in the script I get when I parse it. It is in Line 185, before the IF @dbType= 'All' The message there is 'Incorrect syntax near the keyword 'ELSE' Just before the IF @dbType there is an 'END' and an 'ELSE' Once END and ELSE commted out I can parse the script and it seems to work. Is there any special purpose for the END and ELSE? Thanks in advance. Best regards Dirk P.S.: Sorry for the Email, I just have found the post commet link.

Tony Fountain 2009-04-22	re: Backup SQL Server Databases Aside from the code that forces using the REORGANIZE option, what rule of thumb do you follow to set the @defragType parameter to REORGANIZE or REBUILD?

Michael B 2009-05-19	re: Backup SQL Server Databases Tara, could I make a request of a "WITH" option? Specifically, I've found lately the need to use the COPY command so as to not break the tlog backups. Thanks!

itay 2009-05-27	re: Backup SQL Server Databases thanks. nice try using EZManage SQL Pro for 5 time faster VDI sql backup http://www.futureitsoft.com