Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts 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: 8884 | Views in the last 30 days: 63
 
Related Articles
FORUM

Store Procedure to update

Store Procedure to update

ARTICLE

Version Control your Stored Procedures

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

ARTICLE

Data Warehouse Version Management

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

FORUM

retrieve the previous version of a stored procedure

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

FORUM

VSS For Stored Procedures

Saving versions of Stored Procedures

Tags
 
Contribute