Click here to Skip to main content
15,867,956 members
Articles / Database Development / SQL Server

SQL Database Backup Scripts

Rate me:
Please Sign up or sign in to vote.
4.10/5 (8 votes)
3 May 2007CPOL2 min read 59.1K   545   51   3
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.

SQL
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.

SQL
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)


Written By
Chief Technology Officer
Australia Australia
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralI am using EZManage SQL Pro to backup my mssql server Pin
itayl27-May-09 12:07
itayl27-May-09 12:07 

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

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