SQL Clone
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: 8908 | Views in the last 30 days: 58
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...


Versions of Stored Procedures

I never knew this, but stored procedures have versions. I was browsing the CREATE PROCEDURE doc pag...


Getting the SQL Version from a backup using dbatools ………. on PowerShell Core

Following an upgrade to SQL Server the backup share had a number of backups, some from the old versi...


retrieve the previous version of a stored procedure

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