Scheduling a Backup Job in Microsoft SQL 2005 Express Edition

No comments

SQL 2005 Express Edition does not offer a way to schedule jobs. The following steps outline a way to schedule jobs in SQL 2005 Express using SQL and Windows Task Scheduler. There are three components: 1. SQL command used to backup the database. 2. Script used to call the SQL command and zip the database files (uses WinZip). 3. Schedule the backup script. Much of these steps and scripts were borrowed from the geniuses at mssqltips.com. Please take a look at the reference below.

1. 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). 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. 

USE [master] 
GO 

-- check if stored procedure exists and drop
IF EXISTS (SELECT * FROM [dbo].[sysobjects]
       WHERE ID = object_id(N'[dbo].[sp_BackupDatabase]') AND 
             OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[sp_BackupDatabase]
GO

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 = N''C:\Backup\' + @databaseName + '_Full.BAK'' WITH  DESCRIPTION = N''' + @databaseName + '_Full'', NOFORMAT, INIT,  NAME = N''' + @databaseName + '_Full'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10'
        
       IF @backupType = 'D' 
               SET @sqlCommand = 'BACKUP DATABASE [' + @databaseName + 
               '] TO  DISK = N''C:\Backup\' + @databaseName + '_Differential.BAK'' WITH  DIFFERENTIAL , DESCRIPTION = N''' + @databaseName + '_Differential'', NOFORMAT, INIT,  NAME = N''' + @databaseName + '_Differential'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10'
        
       IF @backupType = 'L' 
               SET @sqlCommand = 'BACKUP LOG [' + @databaseName + 
               '] TO  DISK = N''C:\Backup\' + @databaseName + '_Log.BAK'' WITH  DESCRIPTION = N''' + @databaseName + '_Log'', NOFORMAT, INIT,  NAME = N''' + @databaseName + '_Log'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10'
        
       EXECUTE sp_executesql @sqlCommand 
END

2. Scripts

We will use two scripts, one to hold the SQL commands and another to call the SQL commands and zip the backup files.

a. SQL Commands

This simple SQLCMD file backups the master, model, and msdb databases.
Save this file as SQLBackup.sql. For our purposes this file is created in the “C:\Backup” folder, but again this could be put anywhere.

sp_BackupDatabase 'master', 'F'
GO
sp_BackupDatabase 'model', 'F'
GO
sp_BackupDatabase 'msdb', 'F'
GO
QUIT

b. Batch Script

The next script is the one we will work with, the one that will be scheduled to run daily. Anything can go in this script, but it is a good idea to use this to zip the backup files. This file should be saved as SQLBackup.bat and can be created in the “C:\Backup” folder. A second folder, named Backup_zipped will be used to store the files after they have been zipped.

@echo off

:: SQLBackup.bat
:: Author: Kevin G. Verhoeven
:: Description: This script backs up the selected database, then zips up 
:: the backup file from the listed PATH into the PATHZIPPED directory.

:: backup the sql databases
:: the SQLBackup.sql file is used for configs
sqlcmd -S SERVER\INSTANCE -E -i C:\Scripts\SQLBackup.sql

:: zip the backup file
FOR /f "delims=" %%i in ('dir/b "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\*.BAK"') do (
  echo %%i
  "C:\Program Files\WinZip\winzip32.exe" -a -en "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Backup_zipped\%%i.zip" "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\%%i")
:: end of SQLBackup.bat

3. Scheduling

The final step is to schedule the SQLBackup.bat script to run as a scheduled task. You can do this through the Windows Task Scheduler, found under the Control Panel as Scheduled Tasks.

You have now scheduled a backup job on SQL 2005 Express.

Reference: http://www.mssqltips.com/tip.asp?tip=1174

Tags

Leave a Reply