Database Backup Script
By Nigel Rivett
on 28 October 2002
| 25 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 |
| DatabaseBackupName | Name 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.
