Click here to Skip to main content
Click here to Skip to main content

SQL Database Backup Scripts

, 3 May 2007
Rate this:
Please Sign up or sign in to vote.
Quickly create a full backup regime for your SQL database(s).

Introduction

"An ounce of prevention is worth a pound of cure". This rings true in the context of an enterprise application, and a good backup regime is the prevention for data loss. This set of scripts gives you a quick and simple way of creating this backup regime for any given SQL database.

The scripts available for download are for Large and Regular databases, and with and without notifications. I consider a Large database to be anything over 1 GB, but this may vary depending your own personal preferences and the rate of change that occurs within the database on a daily basis.

Outcomes

The scripts perform the following:

  • Create a folder (in the specified location) to store the backup files.
  • Create a sub folder for each day of the week, thus organising the backups.
  • Create a series of SQL Server Agent jobs to perform the backups.
  • A "standard" job will perform a transaction log backup at 6 AM, 12 PM, and 6 PM daily.
  • A "standard" job will perform a full database backup at 7 PM daily.
  • A "large" job will perform a transaction log backup hourly, between 6 AM and 6 PM daily.
  • A "large" job will perform a full database backup at 7 PM daily.
  • A shrink and reorganise job will occur at 9 AM on Sunday.
  • All daily transaction log backup files will be overwritten at the first occurrence of the job for the day.
  • All full database backup files will be overwritten each day.

Using the Code

The code is relatively straightforward, and there is only a small amount of information required. Set the values of the variables.

Without notification: Both regular and large scripts ask for the same information. Set @Database and @Path, and then execute the script.

use master
declare @Database nvarchar(50)
declare @Path nvarchar(255)
set @Database = '[Database]'
set @Path = '[Backup Path]'

With notification: Both regular and large scripts ask for the same information. Set the @Database and @Path variables, and nominate the @OperatorName, @OperatorEmail, and @OperatorNetSend variables. Then execute the script.

use master
declare @Database nvarchar(50)
declare @Path nvarchar(255)
declare @OperatorName nvarchar(255) 
declare @OperatorEmail nvarchar(255)
declare @OperatorNetSend nvarchar(50) 
set @Database = '[DataBase]'
set @Path = '[Backup Path]'
set @OperatorName = '[Insert Name Here]' 
set @OperatorEmail = '[Email Address]' 
set @OperatorNetSend = '[Net Send Address]' 

The @Operator... variables provide SQL Server with the information required to set the notifications.

The @OperatorEmail allows for an email address; however, this will require SQLMail to be configured in the SQL Server Agent.

The @OperatorNetSend allows SQL Server to perform a net send to a computer; however, with increased security these days, the Messenger service is typically disabled and this may not function.

Points of Interest

You will require access to the Master database and elevated permissions.

Using the Query Analyzer will certainly make things easy, and will also allow you to parse the script before execution.

I've been using the above regimes since 2001 with much success. The only downside is the total size of the backup set, especially when dealing with large (> 1 GB) databases.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Edward Steward
Chief Technology Officer
Australia Australia
No Biography provided

Comments and Discussions

 
GeneralI am using EZManage SQL Pro to backup my mssql server Pinmemberitayl27-May-09 12:07 
GeneralSqlBackupAndFTP PinmemberRuslan Sudentas15-Oct-08 4:28 
GeneralCongratulations!!! PinmemberAndreLyra30-May-08 12:59 
Very, very good post! I didn`t still use the script files but I read them and found outstanding!!

Once more time: Prety good work!!

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140709.1 | Last Updated 3 May 2007
Article Copyright 2007 by Edward Steward
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid