Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

Get your favourite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved in your SSC briefcase from the favorites tab.
Download now (direct download link)

Backup Stored Proc

By Scott Duncan,

This stored procedure was written to get around the lack of support for maintenance plans in MSDE (and to bypass using the naff SSIS-based maintenance plans in SQL 2005). The user specifies the type of backup (FULL, LOG or DIFFERENTIAL), the db name, the folder to hold the backup file and the retention period in days (anything older in the backup folder for the specified backup type is deleted). See usage instructions in the code or run the procedure with no parameters.

The stored procedure also has a parameter for debugging. Set to 1 to have commands printed out during the execution of the procedure.

23-Sep-2008 Update: I have been making updates to this script and posting them in the discussion forum but I have figured out how to update the original post. So I have.

This version has improved long filename handling (i.e. it does now), options to backup all system, all user or all databases, support for file and filegroup backups (full and differential) and better checking of SQL Server version (some things work in 2005 but not in 2000).

It is assumed that the version of SQL Server being used is 2005 SP2 or later or 2000 SP4 or later.

Total article views: 8861 | Views in the last 30 days: 61
Related Articles

Store Procedure to update

Store Procedure to update


Version Control your Stored Procedures

This shows a method for generating scripts for SQL Server stored procedures and functions so they ca...


Data Warehouse Version Management

In most tables it is enough to have a stored procedure that performs Update Else Insert logic for da...


retrieve the previous version of a stored procedure

how to retrieve the previous version of a stored procedure in sql server 2005


VSS For Stored Procedures

Saving versions of Stored Procedures


Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones