Database Backup Script

By Nigel Rivett on 28 October 2002 | 29 Comments | Tags: Backup/Restore


This procedure will backup every database on the server including any new ones. It will also delete any old backups after a defined retention period. It differs from production versions in that it will automatically back up any new databases.

The procedure should be placed in an Admin database and scheduled - normally to run every day. The table DatabaseBackup should be created as defined near the top of the procedure. The call to be scheduled is in comments near the top of the procedure.

It will backup every database on the server to the path defined except tempdb. The database entries will be placed in the DatabaseBackup table and may be configured from there. The table fields are:

Field Name Description
DatabaseBackupNameName of database - added automatically if it doesn't exist
BackupFlagFull 'Y' = Full backup of database, 'N' = No full backup
BackupFlagLog 'Y' = Log backup of database, 'N' = No log backup
RetentionPeriodFull Datetime period for which to keep the full backup
RetentionPeriodLog Datetime period for which to keep the log backup

Note - if an exact number of days is set for retention period and the procedure is scheduled daily then the last retained file will depend on the exact time of processing the backup of that database. The files are created with the following format:

[dbname]_Full_yyyymmdd_hhmmss.bak 
[dbname]_Log_yyyymmdd_hhmmss.bak

You can download the script here.

Discuss this article: 29 Comments so far. Print this Article.

If you like this article you can sign up for our weekly newsletter. There's an opt-out link at the bottom of each newsletter so it's easy to unsubscribe at any time.

Email Address:

Related Articles

Scheduling Jobs in SQL Server Express - Part 2 (1 December 2008)

Scheduling Jobs in SQL Server Express (27 August 2008)

Introduction to SQL Server 2005 Database Backups (19 December 2007)

BACKUP and RESTORE in SQL Server -- Full Backups (12 February 2007)

SQL Server administration best practices (20 September 2001)

Can I Backup Across the Network? (17 August 2000)

Error Message when dumping to hard drive backup device (12 July 2000)

Other Recent Forum Posts

COUNT and SUM in same Query (0 Replies)

Non-Aggregate PIVOT can't sort (2 Replies)

How to get last 7 days last update_dateTime (1 Reply)

RANK get previous record but not always (3 Replies)

Iterating through a list of strings in a function (0 Replies)

Migrating SQL2000 Databases to SQL2008 Enterprise (0 Replies)

Getting Worker History from Table (6 Replies)

outer joining 2 Select Statements (5 Replies)

Subscribe to SQLTeam.com

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -