SQL Database Backup Scripts

• Download source code - 11.8 KB

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.