.NET Information center: Scheduling Backups for SQL Server 2005 Express

Problem
One problem with SQL Server 2005 Express is that it does not offer a way to schedule jobs. In a previous tip, Free Job Scheduling Tool for SQL Server Express and MSDE, we looked at a free tool that allows you to create scheduled jobs for SQL Server. The one issue people often face though is what to install and what not to install on their production servers and therefore these items go without resolution. One very important part of managing SQL Server is to ensure you run backups on a set schedule. I often hear about corrupt databases and no backups, so let's take a look at another approach of scheduling backups using the included tools in both the operating system and SQL Server.

Solution
There are two components to this; the first is the backup command and the second is the scheduling needed to run the backups.

Backup Commands
There are a few things that we need to setup. The first is to create a stored procedure that allows us to dynamically generate the backup file name as well as what type of backup to run Full, Differential or Transaction Log backup. The default for this stored procedure is to create the backups in the "C:\Backup" folder. This can be changed to any folder you like.

The following stored procedure should be created in the master database. This is just one way of handling this. There are several other options and enhancements that can be made.
USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_BackupDatabase] Script Date: 02/07/2007 11:40:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author: Edgewood Solutions
-- Create date: 2007-02-07
-- Description: Backup Database
-- Parameter1: databaseName
-- Parameter2: backupType F=full, D=differential, L=log
-- =============================================
CREATE PROCEDURE [dbo].[sp_BackupDatabase]
@databaseName sysname, @backupType CHAR(1)
AS
BEGIN
SET NOCOUNT ON;

DECLARE @sqlCommand NVARCHAR(1000)
DECLARE @dateTime NVARCHAR(20)

SELECT @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),111),'/','') +
REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','')

IF @backupType = 'F'
SET @sqlCommand = 'BACKUP DATABASE ' + @databaseName +
' TO DISK = ''C:\Backup\' + @databaseName + '_Full_' + @dateTime + '.BAK'''

IF @backupType = 'D'
SET @sqlCommand = 'BACKUP DATABASE ' + @databaseName +
' TO DISK = ''C:\Backup\' + @databaseName + '_Diff_' + @dateTime + '.BAK'' WITH DIFFERENTIAL'

IF @backupType = 'L'
SET @sqlCommand = 'BACKUP LOG ' + @databaseName +
' TO DISK = ''C:\Backup\' + @databaseName + '_Log_' + @dateTime + '.TRN'''

EXECUTE sp_executesql @sqlCommand
END

The second part of this is to create a SQLCMD file to run the backup commands. Here is a simple SQLCMD file that backups databases master, model and msdb.

Sunday, February 21, 2010

Scheduling Backups for SQL Server 2005 Express