Building Automated Backups in MSDN

,

We write this script due we needed to implement a backup strategy in some machines with MSDE located in remote offices.
In that offices there isn't Enterprise Manager, so we send the script to the office and they just execute it.

Comments will be welcome

use master
go

DECLARE @ServerName		varchar(400)
DECLARE @DatabaseName	varchar(400)
DECLARE @BackupPath		varchar(400)
DECLARE @TSQLCommand	varchar(2000)
DECLARE @FullBackupJob	varchar(2000)
DECLARE @DiffBackupJob	varchar(2000)
DECLARE @BackupDevice	varchar(2000)
DECLARE @FullStepName	varchar(2000)
DECLARE @DiffStepName	varchar(2000)
DECLARE @FullSchName	varchar(2000)
DECLARE @DiffSchName	varchar(2000)

SET @ServerName			= HOST_NAME()
SET @DatabaseName		= '<DatabaseName'
SET @BackupPath			= '<Unit>:\<CompletePath>\' + @DatabaseName + '.bk!'
SET @FullBackupJob		= @DatabaseName + '_Weekly_Full_Backup'
SET @DiffBackupJob		= @DatabaseName + '_Daily_Diff_Backup'
SET @BackupDevice		= @DatabaseName + '_Backup'
SET @FullStepName		= @DatabaseName + '_Full_Backup'
SET @FullSchName		= 'Schedule_' + @DatabaseName + '_Weekly_Full_Backup'
SET @DiffSchName		= 'Schedule_' + @DatabaseName + '_Daily_Diff_Backup'
SET @DiffStepName		= @DatabaseName + '_Diff_Backup'

IF EXISTS (SELECT name FROM master.dbo.sysdevices WHERE name = @BackupDevice)  
BEGIN
	EXEC sp_dropdevice @BackupDevice
END

IF EXISTS (SELECT name FROM msdb.dbo.sysjobs WHERE name = @FullBackupJob)
BEGIN
	EXEC msdb.dbo.sp_delete_job @job_name = @FullBackupJob
END

IF EXISTS (SELECT name FROM msdb.dbo.sysjobs WHERE name = @DiffBackupJob)
BEGIN
	EXEC msdb.dbo.sp_delete_job @job_name = @DiffBackupJob
END

EXEC sp_addumpdevice 'disk', @BackupDevice, @BackupPath

EXEC msdb.dbo.sp_add_job @job_name = @FullBackupJob, 
	@owner_login_name = 'sa'				-- Creates the job of Full Backup

EXEC msdb.dbo.sp_add_jobserver @job_name = @FullBackupJob, 
	@server_name = @ServerName

SET @TSQLCommand = 'BACKUP DATABASE [' + @DatabaseName + '] TO [' + @DatabaseName + '_Backup] WITH  INIT ,  NOUNLOAD ,  NAME = N''' + @DatabaseName + '_Weekly_Full_Backup'',  NOSKIP ,  STATS = 10,  NOFORMAT' 
EXEC msdb.dbo.sp_add_jobstep @job_name = @FullBackupJob,
   @step_name = @FullStepName,
   @subsystem = 'TSQL',
   @command = @TSQLCommand, 
   @retry_attempts = 5,
   @retry_interval = 5

EXEC msdb.dbo.sp_add_jobschedule @job_name = @FullBackupJob, 
	@name = @FullSchName,					-- Creates the Job Schedule
	@freq_type = 8,						-- Weekly
	@freq_interval = 2,					-- Monday
	@freq_recurrence_factor = 1,				-- Every week
	@active_start_time = 083000				-- HHMMSS (08:30:00 AM)
	


EXEC msdb.dbo.sp_add_job @job_name = @DiffBackupJob, 
	@owner_login_name = 'sa'				-- Creates the job of Differencial Backup

EXEC msdb.dbo.sp_add_jobserver @job_name = @DiffBackupJob, 
	@server_name = @ServerName
	
EXEC msdb.dbo.sp_add_jobschedule @job_name = @DiffBackupJob, 
	@name = @DiffSchName,					-- Creates the Job Schedule
	@freq_type = 8,						-- Weekly
	@freq_interval = 60,					-- Tuesday, Wednesday, Thursday and Friday
	@freq_recurrence_factor = 1,				-- Every week
	@active_start_time = 083000				-- HHMMSS (08:30:00 AM)
	
SET @TSQLCommand = 'BACKUP DATABASE [' + @DatabaseName + '] TO [' + @DatabaseName + '_Backup] WITH  NOINIT ,  NOUNLOAD ,  DIFFERENTIAL ,  NAME = N''' + @DatabaseName + '_Daily_Diff_Backup'',  NOSKIP ,  STATS = 10,  NOFORMAT '
EXEC msdb.dbo.sp_add_jobstep @job_name = @DiffBackupJob,
   @step_name = @DiffStepName,
   @subsystem = 'TSQL',
   @command = @TSQLCommand,
   @retry_attempts = 5,
   @retry_interval = 5

Rate

Share

Share

Rate