Backup SQL Server Databases

<< Defragmenting Indexes in SQL Server 2005 | Home | SQL Server Script to Display Job History >>

Backup SQL Server Databases

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.

Feedback

# re: Backup SQL Server Databases

4/9/2009 12:08 AM | qaisar

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

4/9/2009 8:59 AM | Jessica

# re: Backup SQL Server Databases

iTS COOL

4/21/2009 12:08 AM | collen

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

4/21/2009 1:29 AM | Dirk Hondong

# 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?

4/22/2009 7:57 AM | Tony Fountain

# 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!

5/19/2009 7:20 AM | Michael B

# re: Backup SQL Server Databases

thanks. nice

try using EZManage SQL Pro for 5 time faster VDI sql backup

http://www.futureitsoft.com

5/27/2009 3:20 PM | itay

Comments have been closed on this topic.

Ramblings of a DBA

My Links

Advertisement

News

Archives

Post Categories

Work