Developer's Closet A place where I can put my PHP, SQL, Perl, JavaScript, and VBScript code.

24Jul/090
Scheduling a Backup Job in Microsoft SQL 2005 Express Edition

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

8Jun/090
Script to Add a New SQL Differential Backup Job With Daily Incremental Zip Archives

This script builds on the differential backup script, adding a zipped incremental backup. This script will create a zip file for each day of the week. I've used this script when I have a simple database type but need the flexibility of multiple daily differential backups. You will have to turn on xp_cmdshell to run this script.

-- This script creates a differential backup job to backup the DBName database as DBName_Differential.bak (daily at 5:00 PM). This script also creates daily incremental zip archives.

USE [msdb]
GO
DECLARE @jobId BINARY(16)
EXEC  msdb.dbo.sp_add_job @job_name=N'DBName_Differential.bak', 
	@enabled=1, 
	@notify_level_eventlog=2, 
	@notify_level_email=2, 
	@notify_level_netsend=2, 
	@notify_level_page=2, 
	@delete_level=0, 
	@category_name=N'[Uncategorized (Local)]', 
	@owner_login_name=N'sa', @job_id = @jobId OUTPUT
select @jobId
GO
EXEC msdb.dbo.sp_add_jobserver @job_name=N'DBName_Differential.bak', @server_name = N'SERVER\INSTANCE'
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'DBName_Differential.bak', @step_name=N'Step 1: Backup File', 
	@step_id=1, 
	@cmdexec_success_code=0, 
	@on_success_action=3, 
	@on_fail_action=2, 
	@retry_attempts=0, 
	@retry_interval=0, 
	@os_run_priority=0, @subsystem=N'TSQL', 
	@command=N'BACKUP DATABASE [PBSA] TO  DISK = N''C:\MSSQL.1\MSSQL\Backup\DBName_Differential.bak'' WITH  DIFFERENTIAL ,  DESCRIPTION = N''DBName_Differential.bak'', NOFORMAT, INIT,  NAME = N''DBName_Differential.bak'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
', 
	@database_name=N'master', 
	@flags=0
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'DBName_Differential.bak', @step_name=N'Step 2: Zip Backup File', 
	@step_id=2, 
	@cmdexec_success_code=0, 
	@on_success_action=1, 
	@on_fail_action=2, 
	@retry_attempts=0, 
	@retry_interval=0, 
	@os_run_priority=0, @subsystem=N'TSQL', 
	@command=N'xp_cmdshell ''C:\PROGRA~1\WinZip\winzip32.exe -a -en "C:\MSSQL.1\MSSQL\Backup\Backup_zipped\DBName_Differential.bak - %date:~0,3%.zip" "C:\MSSQL.1\MSSQL\Backup\DBName_Differential.bak"''', 
	@database_name=N'master', 
	@flags=0
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N'DBName_Differential.bak', 
	@enabled=1, 
	@start_step_id=1, 
	@notify_level_eventlog=2, 
	@notify_level_email=2, 
	@notify_level_netsend=2, 
	@notify_level_page=2, 
	@delete_level=0, 
	@description=N'', 
	@category_name=N'[Uncategorized (Local)]', 
	@owner_login_name=N'sa', 
	@notify_email_operator_name=N'', 
	@notify_netsend_operator_name=N'', 
	@notify_page_operator_name=N''
GO
USE [msdb]
GO
DECLARE @schedule_id int
EXEC msdb.dbo.sp_add_jobschedule @job_name=N'DBName_Differential.bak', @name=N'Schedule 1', 
	@enabled=1, 
	@freq_type=4, 
	@freq_interval=1, 
	@freq_subday_type=1, 
	@freq_subday_interval=0, 
	@freq_relative_interval=0, 
	@freq_recurrence_factor=1, 
	@active_start_date=20080725, 
	@active_end_date=99991231, 
	@active_start_time=193000, 
	@active_end_time=235959, @schedule_id = @schedule_id OUTPUT
select @schedule_id
GO
7Jun/090
Script to Add a New SQL Differential Backup Job

I know you've been waiting for this... This script will add a differential backup job to Microsoft SQL Server 2005. Use this script in conjunction with a full backup.

-- This script creates a differential backup job to backup the DBName database as DBName_Differential.bak (daily at 5:00 PM)

USE [msdb]
GO
DECLARE @jobId BINARY(16)
EXEC  msdb.dbo.sp_add_job @job_name=N'DBName_Differential.bak', 
	@enabled=1, 
	@notify_level_eventlog=2, 
	@notify_level_email=2, 
	@notify_level_netsend=2, 
	@notify_level_page=2, 
	@delete_level=0, 
	@category_name=N'[Uncategorized (Local)]', 
	@owner_login_name=N'sa', @job_id = @jobId OUTPUT
select @jobId
GO
EXEC msdb.dbo.sp_add_jobserver @job_name=N'DBName_Differential.bak', @server_name = N'SERVER\INSTANCE'
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'DBName_Differential.bak', @step_name=N'Step 1: Backup File', 
	@step_id=1, 
	@cmdexec_success_code=0, 
	@on_success_action=3, 
	@on_fail_action=2, 
	@retry_attempts=0, 
	@retry_interval=0, 
	@os_run_priority=0, @subsystem=N'TSQL', 
	@command=N'BACKUP DATABASE [DBName] TO  DISK = N''C:\MSSQL.1\MSSQL\Backup\DBName_Differential.bak'' WITH DIFFERENTIAL, DESCRIPTION = N''DBName_Differential.bak'', NOFORMAT, INIT,  NAME = N''DBName_Differential.bak'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
', 
	@database_name=N'master', 
	@flags=0
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'DBName_Differential.bak', @step_name=N'Step 2: Zip Backup File', 
	@step_id=2, 
	@cmdexec_success_code=0, 
	@on_success_action=1, 
	@on_fail_action=2, 
	@retry_attempts=0, 
	@retry_interval=0, 
	@os_run_priority=0, @subsystem=N'CmdExec', 
	@command=N'"C:\Program Files\WinZip\winzip32.exe" -a -en "C:\MSSQL.1\MSSQL\Backup\Backup_zipped\DBName_Differential.zip" "C:\MSSQL.1\MSSQL\Backup\DBName_Differential.bak"', 
	@database_name=N'master', 
	@flags=0
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N'DBName_Differential.bak', 
	@enabled=1, 
	@start_step_id=1, 
	@notify_level_eventlog=2, 
	@notify_level_email=2, 
	@notify_level_netsend=2, 
	@notify_level_page=2, 
	@delete_level=0, 
	@description=N'', 
	@category_name=N'[Uncategorized (Local)]', 
	@owner_login_name=N'sa', 
	@notify_email_operator_name=N'', 
	@notify_netsend_operator_name=N'', 
	@notify_page_operator_name=N''
GO
USE [msdb]
GO
DECLARE @schedule_id int
EXEC msdb.dbo.sp_add_jobschedule @job_name=N'DBName_Differential.bak', @name=N'Schedule 1', 
	@enabled=1, 
	@freq_type=4, 
	@freq_interval=1, 
	@freq_subday_type=1, 
	@freq_subday_interval=0, 
	@freq_relative_interval=0, 
	@freq_recurrence_factor=1, 
	@active_start_date=20080725, 
	@active_end_date=99991231, 
	@active_start_time=170000, 
	@active_end_time=235959, @schedule_id = @schedule_id OUTPUT
select @schedule_id
GO
6Jun/090
Script to Add a New SQL Backup Job

This script is for the times when I like to automate the task of adding database backup jobs to Microsoft SQL Servers. I'll sometimes add a dozen jobs at once. This script saves time and keeps things consistent. The script adds two tasks and a schedule:

1. Database backup
2. Zip backup file

Using WinZip (you'll need a license) to zip the backup file makes it easier to move the file to an offsite location. This script also makes it easy to add or remove steps. Enjoy...

-- This script creates a backup job to backup the DBName database as DBName_Full.bak (daily at 10:00 PM)

USE [msdb]
GO
DECLARE @jobId BINARY(16)
EXEC  msdb.dbo.sp_add_job @job_name=N'DBName_Full.bak', 
	@enabled=1, 
	@notify_level_eventlog=2, 
	@notify_level_email=2, 
	@notify_level_netsend=2, 
	@notify_level_page=2, 
	@delete_level=0, 
	@category_name=N'[Uncategorized (Local)]', 
	@owner_login_name=N'sa', @job_id = @jobId OUTPUT
select @jobId
GO
EXEC msdb.dbo.sp_add_jobserver @job_name=N'DBName_Full.bak', @server_name = N'SERVER\INSTANCE'
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'DBName_Full.bak', @step_name=N'Step 1: Backup File', 
	@step_id=1, 
	@cmdexec_success_code=0, 
	@on_success_action=3, 
	@on_fail_action=2, 
	@retry_attempts=0, 
	@retry_interval=0, 
	@os_run_priority=0, @subsystem=N'TSQL', 
	@command=N'BACKUP DATABASE [DBName] TO  DISK = N''C:\MSSQL.1\MSSQL\Backup\DBName_Full.bak'' WITH  DESCRIPTION = N''DBName_Full.bak'', NOFORMAT, INIT,  NAME = N''DBName_Full.bak'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
', 
	@database_name=N'master', 
	@flags=0
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'DBName_Full.bak', @step_name=N'Step 2: Zip Backup File', 
	@step_id=2, 
	@cmdexec_success_code=0, 
	@on_success_action=1, 
	@on_fail_action=2, 
	@retry_attempts=0, 
	@retry_interval=0, 
	@os_run_priority=0, @subsystem=N'CmdExec', 
	@command=N'"C:\Program Files\WinZip\winzip32.exe" -a -en "C:\MSSQL.1\MSSQL\Backup\Backup_zipped\DBName_Full.zip" "C:\MSSQL.1\MSSQL\Backup\DBName_Full.bak"', 
	@database_name=N'master', 
	@flags=0
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N'DBName_Full.bak', 
	@enabled=1, 
	@start_step_id=1, 
	@notify_level_eventlog=2, 
	@notify_level_email=2, 
	@notify_level_netsend=2, 
	@notify_level_page=2, 
	@delete_level=0, 
	@description=N'', 
	@category_name=N'[Uncategorized (Local)]', 
	@owner_login_name=N'sa', 
	@notify_email_operator_name=N'', 
	@notify_netsend_operator_name=N'', 
	@notify_page_operator_name=N''
GO
USE [msdb]
GO
DECLARE @schedule_id int
EXEC msdb.dbo.sp_add_jobschedule @job_name=N'DBName_Full.bak', @name=N'Schedule 1', 
	@enabled=1, 
	@freq_type=4, 
	@freq_interval=1, 
	@freq_subday_type=1, 
	@freq_subday_interval=0, 
	@freq_relative_interval=0, 
	@freq_recurrence_factor=1, 
	@active_start_date=20080725, 
	@active_end_date=99991231, 
	@active_start_time=220000, 
	@active_end_time=235959, @schedule_id = @schedule_id OUTPUT
select @schedule_id
GO