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 @ServerNamevarchar(400)
DECLARE @DatabaseNamevarchar(400)
DECLARE @BackupPathvarchar(400)
DECLARE @TSQLCommandvarchar(2000)
DECLARE @FullBackupJobvarchar(2000)
DECLARE @DiffBackupJobvarchar(2000)
DECLARE @BackupDevicevarchar(2000)
DECLARE @FullStepNamevarchar(2000)
DECLARE @DiffStepNamevarchar(2000)
DECLARE @FullSchNamevarchar(2000)
DECLARE @DiffSchNamevarchar(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
