- Date: 4 Sep 2005 (revision)
- Versions affected: SQL backup 3.2
As we have had quite a few customers ask about deploying SQL Backup in conjunction with MSDE databases, questions about adding a SQL Backup job to an MSDE server are fairly frequent.
Because MSDE does not ship with a management interface like the Enterprise Manager that comes with SQL Server, it's difficult to schedule recurring backup jobs given only command-line tools. The following VBScript can be used to create a scheduled SQL Server backup job using SQL backup's extended stored procedure.
Copy and paste the script into Notepad, then save it with a VBS extension. Then you can either double-click it or run it from a command prompt with cscript.exe.
'Script to create backup jobs on the local server (ie MSDE)
'This required a WINNT logon that has access to the server.
'Use an argument to specify an instance ie. cscript createsqbjob.vbs SECONDARY
'$VER: 1.4 Added job category "SQL Backup", allowed overwrite if job exists, added INSTANCE argument,
'remove extra SQL Server connection
If MsgBox("This will create a SQL Backup full backup job"&vbCrLf&_
"and schedule it to run on the local SQL Server."&vbCrLf &_
"You may schedule daily or weekly backups with or without"&vbCrLf &_
"encryption and choose to mail error reports to an email address.", vbOKCancel, "Schedule SQL Backup")=2 Then WScript.Quit
Dim obDMO
Dim chosenDatabase
Dim strServerName
Dim obShell
Dim obSQLServer
Dim obDatabase
Dim bDaily
Dim strWeekDays
Dim strTime
Dim iCompression
Dim iEraseFiles
Dim strEmailError
Dim strBackupCommand
Dim strEncryptPassword
Set obShell=CreateObject("WScript.Shell")
strServerName=obShell.ExpandEnvironmentStrings("%COMPUTERNAME%")
If WScript.Arguments.Length>0 Then strServerName=strServerName&"\"&WScript.Arguments(0)
chosenDatabase=InputBox("Enter the name of the database you want to back up.", "Choose database")
Set obSQLServer=CreateObject("SQLDMO.SQLServer")
obSQLServer.LoginTimeout = 10
' Windows NT security
obSQLServer.LoginSecure = True
obSQLServer.Connect strServerName
Set obDataBase=Nothing
ON ERROR RESUME NEXT
Set obDataBase=obSqlServer.Databases(chosenDatabase)
ON ERROR GOTO 0
If obDataBase Is Nothing Then
obShell.Popup "The database does not exist on the local server or you do not have rights to it.", 10, "Cannot create job",16
WScript.Quit
End If
Set obDataBase=Nothing
' Ask the backup frequency
bDaily=False
If bDaily=False Then
strWeekDays=InputBox("Enter the days of the week that you would like the job to run on." &vbCrLf &_
"Enter comma-separated list of days in this format:M,Tu,W,Th,F,Sa,Su", "Enter the days to run job")
End If
strTime=InputBox("Enter the time to do this backup in 24-hour format (HHMM)", "Time")
iCompression=InputBox("Enter compression level: 1=fast, 2=slow, 3=slowest (1, 2, or 3)","Compression")
If Not (iCompression="1" Or iCompression="2" Or iCompression="3") Then
obShell.Popup "Compression must be 1, 2, or 3", 10, "Cannot create job",16
WScript.Quit
End If
iEraseFiles=InputBox("To erase backup old files after the backup completes," &_
"enter the number of days that you would like to retain old backups" &vbCrLf &_
"Leave this blank to keep all backup files.","Delete old backups")
If LEN(iEraseFiles)>0 AND IsNumeric(iEraseFiles)=False Then
obShell.Popup iEraseFiles&" is not a valid number.", 10, "Cannot create job",16
WScript.Quit
End If
strEmailError=InputBox("Enter an email address to send error reports" &vbCrLf&"Leave this blank if you do not want error reports.","Email errors")
strEncryptPassword=InputBox("Enter a password for encryption:" &vbCrLf&"Leave blank to disable encryption."&vbCrLf&"Note that the password will be visible in the job.","Encryption")
'Verify Settings
Dim strSettings
strSettings="SQL Agent job settings:"&vbCrLf &_
"Perform a full backup on "&strWeekDays&" at "&strTime &vbCrLf &" with compression "&iCompression&vbCrLf
If LEN(iEraseFiles)>0 Then strSettings=strSettings & "Erase previous backups older than "&iEraseFiles&" days."&vbCrLf
If LEN(strEmailError)>0 Then strSettings=strSettings&"Email error reports to "&strEmailError&"."&vbCrLf
If LEN(strEncryptPassword)>0 Then strSettings=strSettings&"Encrypt these backups."&vbCrLf
Dim iAcceptable
If MsgBox(strSettings,33, "Verify settings...")=2 Then WScript.Quit
' create and initialize the Job object
Dim SQLJob
Dim obJobStep
Dim obCategory
Set SQLJob=Nothing
ON ERROR RESUME NEXT
'add a job category "SQL Backup" to the job server
Set obCategory=CreateObject("SQLDMO.Category")
obCategory.Name="SQL Backup"
obSQLServer.JobServer.JobCategories.Add(obCategory)
'If the job already exists, just modify that one
Set SQLJob=obSQLServer.JobServer.Jobs("SQL Backup of "&chosenDatabase)
ON ERROR GOTO 0
If Not SQLJob Is Nothing Then SQLJob.Remove
Set SQLJob=CreateObject("SQLDMO.Job")
SQLJob.Name = "SQL Backup of "&chosenDatabase
SQLJob.Description = "SQL Backup of "&chosenDatabase
SQLJob.Category="SQL Backup"
' add to the Jobs collection
obSQLServer.JobServer.Jobs.Add SQLJob
Set obJobStep=CreateObject("SQLDMO.JobStep")
obJobStep.Name = "Step 1: Do full backup"
obJobStep.StepId = 1
obJobStep.SubSystem = "TSQL"
obJobStep.DatabaseName = chosenDatabase
'Construct the backup command
strBackupCommand="DECLARE @success int" &vbCrLf &_
"DECLARE @datestamp varchar(30)" &vbCrLf &_
"DECLARE @backupcmd varchar(512)" &vbCrLf &_
"SET @datestamp = left(replace(replace(replace(convert(varchar(30), getdate(), 120), '-', ''), ' ', ''), ':', ''), 12)" &vbCrLf &_
"SET @backupcmd = '-SQL ""BACKUP DATABASE["&chosenDatabase&"] TO DISK=''<AUTO>'''+" &vbCrLf &_
"' WITH COMPRESSION="&iCompression&", NAME=''Database "&chosenDatabase&", (full) '+@datestamp +"&vbCrLf &_
"''', DESCRIPTION=''Database "&chosenDatabase&", (full) '+@datestamp+'''"
If LEN(strEmailError)>0 Then strBackupCommand=strBackupCommand&", MAILTO_ONERROR=''"&strEmailError&"''"
If LEN(strEncryptPassword)>0 Then strBackupCommand=strBackupCommand&", PASSWORD=''"&strEncryptPassword&"''"
If LEN(iEraseFiles)>0 Then strBackupCommand=strBackupCommand &", ERASEFILES="&iEraseFiles
strBackupCommand=strBackupCommand & """ -E"
'Specify Instance, if necessary
If InStr(1, strServerName, "\", 1)>0 Then strBackupCommand=strBackupCommand&" -I "&SPLIT(strServerName, "\", -1, 1)(1)
strBackupCommand=strBackupCommand &"'"&vbCrLf &"EXEC @success=master..sqlbackup @backupcmd" &vbCrLf &_
"IF @success=0" &vbCrLf &_
"BEGIN" &vbCrLf &_
"RAISERROR('Backup failed for database "&chosenDatabase&"', 16,1)" &vbCrLf &_
"END"
obJobStep.Command = strBackupCommand
obJobStep.OnFailAction = 2 'Quit with failure
'Add the job step to the job
SQLJob.JobSteps.Add obJobStep
SQLJob.ApplyToTargetServer(strServerName)
'Schedule this job
' a new JobSchedule for daily backup
Dim SQLSchedule
Set SQLSchedule=CreateObject("SQLDMO.JobSchedule")
SQLSchedule.Name = "Backup of "&chosenDatabase
If bDaily=True Then
SQLSchedule.Schedule.FrequencyType = 4
SQLSchedule.Schedule.FrequencyInterval = 1
End If
If bDaily=False Then
SQLSchedule.Schedule.FrequencyType = 8
'Construct a SQLDMO weekday type
Dim aDays
Dim enDays
Dim Day
enDays=0
aDays=SPLIT(strWeekDays, ",",-1,1)
For Each Day in aDays
If Day="M" Then enDays=enDays Or 2
If Day="Tu" Then enDays=enDays Or 4
If Day="W" Then enDays=enDays Or 8
If Day="Th" Then enDays=enDays Or 16
If Day="F" Then enDays=enDays Or 32
If Day="Sa" Then enDays=enDays Or 64
If Day="Su" Then enDays=enDays Or 1
Next
SQLSchedule.Schedule.FrequencyInterval = enDays
SQLSchedule.Schedule.FrequencyRecurrenceFactor=1
End If
'This is sort of arbitrary, but won't hurt anything...
SQLSchedule.Schedule.ActiveStartDate = 20050816
SQLSchedule.Schedule.ActiveStartTimeOfDay = strTime &"00"
' this schedule has no end time or end date (they might be omitted)
SQLSchedule.Schedule.ActiveEndDate = SQLDMO_NOENDDATE
SQLSchedule.Schedule.ActiveEndTimeOfDay = SQLDMO_NOENDTIME
' add the schedule to the Job
SQLJob.BeginAlter
SQLJob.JobSchedules.Add SQLSchedule
SQLJob.DoAlter
'Disconnect from SQL Server
obSQLServer.Disconnect
'Make sure the SQL Server agent is running...
Dim colServiceList
Dim obService
Dim objWMIService
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\.\root\cimv2")
Dim strSvcQuery
strSvcQuery="Select * from Win32_Service where Name='SQLSERVERAGENT'"
If WScript.Arguments.Length=1 Then strSvcQuery="Select * from Win32_Service where Name='SQLAgent$"&WScript.Arguments(0)&"'"
Set colServiceList = objWMIService.ExecQuery(strSvcQuery)
For each obService in colServiceList
If obService.Started = False Then
MsgBox "The SQL Server Agent is not running." &_
vbCrLf &"Your job will not run until you start the SQLSERVERAGENT service!", 48, "SQL Agent warning"
End If
Next
MsgBox "SQL Agent job created successfully", 64, "Success!"
'Clean up
Set obService=Nothing
Set objWMIService=Nothing
Set SQLSchedule=Nothing
Set obJob=Nothing
Set obSQLServer=Nothing
Set obJobStep=Nothing
Set obShell=Nothing
Set obCategory=Nothing
- Date: 4 Sep 2005 (revision)
- Versions affected: SQL backup 3.2
As we have had quite a few customers ask about deploying SQL Backup in conjunction with MSDE databases, questions about adding a SQL Backup job to an MSDE server are fairly frequent.Because MSDE does not ship with a management interface like the Enterprise Manager that comes with SQL Server, it's difficult to schedule recurring backup jobs given only command-line tools. The following VBScript can be used to create a scheduled SQL Server backup job using SQL backup's extended stored procedure.
Copy and paste the script into Notepad, then save it with a VBS extension. Then you can either double-click it or run it from a command prompt with cscript.exe.
'Script to create backup jobs on the local server (ie MSDE) 'This required a WINNT logon that has access to the server. 'Use an argument to specify an instance ie. cscript createsqbjob.vbs SECONDARY '$VER: 1.4 Added job category "SQL Backup", allowed overwrite if job exists, added INSTANCE argument, 'remove extra SQL Server connection If MsgBox("This will create a SQL Backup full backup job"&vbCrLf&_ "and schedule it to run on the local SQL Server."&vbCrLf &_ "You may schedule daily or weekly backups with or without"&vbCrLf &_ "encryption and choose to mail error reports to an email address.", vbOKCancel, "Schedule SQL Backup")=2 Then WScript.Quit Dim obDMO Dim chosenDatabase Dim strServerName Dim obShell Dim obSQLServer Dim obDatabase Dim bDaily Dim strWeekDays Dim strTime Dim iCompression Dim iEraseFiles Dim strEmailError Dim strBackupCommand Dim strEncryptPassword Set obShell=CreateObject("WScript.Shell") strServerName=obShell.ExpandEnvironmentStrings("%COMPUTERNAME%") If WScript.Arguments.Length>0 Then strServerName=strServerName&"\"&WScript.Arguments(0) chosenDatabase=InputBox("Enter the name of the database you want to back up.", "Choose database") Set obSQLServer=CreateObject("SQLDMO.SQLServer") obSQLServer.LoginTimeout = 10 ' Windows NT security obSQLServer.LoginSecure = True obSQLServer.Connect strServerName Set obDataBase=Nothing ON ERROR RESUME NEXT Set obDataBase=obSqlServer.Databases(chosenDatabase) ON ERROR GOTO 0 If obDataBase Is Nothing Then obShell.Popup "The database does not exist on the local server or you do not have rights to it.", 10, "Cannot create job",16 WScript.Quit End If Set obDataBase=Nothing ' Ask the backup frequency bDaily=False If bDaily=False Then strWeekDays=InputBox("Enter the days of the week that you would like the job to run on." &vbCrLf &_ "Enter comma-separated list of days in this format:M,Tu,W,Th,F,Sa,Su", "Enter the days to run job") End If strTime=InputBox("Enter the time to do this backup in 24-hour format (HHMM)", "Time") iCompression=InputBox("Enter compression level: 1=fast, 2=slow, 3=slowest (1, 2, or 3)","Compression") If Not (iCompression="1" Or iCompression="2" Or iCompression="3") Then obShell.Popup "Compression must be 1, 2, or 3", 10, "Cannot create job",16 WScript.Quit End If iEraseFiles=InputBox("To erase backup old files after the backup completes," &_ "enter the number of days that you would like to retain old backups" &vbCrLf &_ "Leave this blank to keep all backup files.","Delete old backups") If LEN(iEraseFiles)>0 AND IsNumeric(iEraseFiles)=False Then obShell.Popup iEraseFiles&" is not a valid number.", 10, "Cannot create job",16 WScript.Quit End If strEmailError=InputBox("Enter an email address to send error reports" &vbCrLf&"Leave this blank if you do not want error reports.","Email errors") strEncryptPassword=InputBox("Enter a password for encryption:" &vbCrLf&"Leave blank to disable encryption."&vbCrLf&"Note that the password will be visible in the job.","Encryption") 'Verify Settings Dim strSettings strSettings="SQL Agent job settings:"&vbCrLf &_ "Perform a full backup on "&strWeekDays&" at "&strTime &vbCrLf &" with compression "&iCompression&vbCrLf If LEN(iEraseFiles)>0 Then strSettings=strSettings & "Erase previous backups older than "&iEraseFiles&" days."&vbCrLf If LEN(strEmailError)>0 Then strSettings=strSettings&"Email error reports to "&strEmailError&"."&vbCrLf If LEN(strEncryptPassword)>0 Then strSettings=strSettings&"Encrypt these backups."&vbCrLf Dim iAcceptable If MsgBox(strSettings,33, "Verify settings...")=2 Then WScript.Quit ' create and initialize the Job object Dim SQLJob Dim obJobStep Dim obCategory Set SQLJob=Nothing ON ERROR RESUME NEXT 'add a job category "SQL Backup" to the job server Set obCategory=CreateObject("SQLDMO.Category") obCategory.Name="SQL Backup" obSQLServer.JobServer.JobCategories.Add(obCategory) 'If the job already exists, just modify that one Set SQLJob=obSQLServer.JobServer.Jobs("SQL Backup of "&chosenDatabase) ON ERROR GOTO 0 If Not SQLJob Is Nothing Then SQLJob.Remove Set SQLJob=CreateObject("SQLDMO.Job") SQLJob.Name = "SQL Backup of "&chosenDatabase SQLJob.Description = "SQL Backup of "&chosenDatabase SQLJob.Category="SQL Backup" ' add to the Jobs collection obSQLServer.JobServer.Jobs.Add SQLJob Set obJobStep=CreateObject("SQLDMO.JobStep") obJobStep.Name = "Step 1: Do full backup" obJobStep.StepId = 1 obJobStep.SubSystem = "TSQL" obJobStep.DatabaseName = chosenDatabase 'Construct the backup command strBackupCommand="DECLARE @success int" &vbCrLf &_ "DECLARE @datestamp varchar(30)" &vbCrLf &_ "DECLARE @backupcmd varchar(512)" &vbCrLf &_ "SET @datestamp = left(replace(replace(replace(convert(varchar(30), getdate(), 120), '-', ''), ' ', ''), ':', ''), 12)" &vbCrLf &_ "SET @backupcmd = '-SQL ""BACKUP DATABASE["&chosenDatabase&"] TO DISK=''<AUTO>'''+" &vbCrLf &_ "' WITH COMPRESSION="&iCompression&", NAME=''Database "&chosenDatabase&", (full) '+@datestamp +"&vbCrLf &_ "''', DESCRIPTION=''Database "&chosenDatabase&", (full) '+@datestamp+'''" If LEN(strEmailError)>0 Then strBackupCommand=strBackupCommand&", MAILTO_ONERROR=''"&strEmailError&"''" If LEN(strEncryptPassword)>0 Then strBackupCommand=strBackupCommand&", PASSWORD=''"&strEncryptPassword&"''" If LEN(iEraseFiles)>0 Then strBackupCommand=strBackupCommand &", ERASEFILES="&iEraseFiles strBackupCommand=strBackupCommand & """ -E" 'Specify Instance, if necessary If InStr(1, strServerName, "\", 1)>0 Then strBackupCommand=strBackupCommand&" -I "&SPLIT(strServerName, "\", -1, 1)(1) strBackupCommand=strBackupCommand &"'"&vbCrLf &"EXEC @success=master..sqlbackup @backupcmd" &vbCrLf &_ "IF @success=0" &vbCrLf &_ "BEGIN" &vbCrLf &_ "RAISERROR('Backup failed for database "&chosenDatabase&"', 16,1)" &vbCrLf &_ "END" obJobStep.Command = strBackupCommand obJobStep.OnFailAction = 2 'Quit with failure 'Add the job step to the job SQLJob.JobSteps.Add obJobStep SQLJob.ApplyToTargetServer(strServerName) 'Schedule this job ' a new JobSchedule for daily backup Dim SQLSchedule Set SQLSchedule=CreateObject("SQLDMO.JobSchedule") SQLSchedule.Name = "Backup of "&chosenDatabase If bDaily=True Then SQLSchedule.Schedule.FrequencyType = 4 SQLSchedule.Schedule.FrequencyInterval = 1 End If If bDaily=False Then SQLSchedule.Schedule.FrequencyType = 8 'Construct a SQLDMO weekday type Dim aDays Dim enDays Dim Day enDays=0 aDays=SPLIT(strWeekDays, ",",-1,1) For Each Day in aDays If Day="M" Then enDays=enDays Or 2 If Day="Tu" Then enDays=enDays Or 4 If Day="W" Then enDays=enDays Or 8 If Day="Th" Then enDays=enDays Or 16 If Day="F" Then enDays=enDays Or 32 If Day="Sa" Then enDays=enDays Or 64 If Day="Su" Then enDays=enDays Or 1 Next SQLSchedule.Schedule.FrequencyInterval = enDays SQLSchedule.Schedule.FrequencyRecurrenceFactor=1 End If 'This is sort of arbitrary, but won't hurt anything... SQLSchedule.Schedule.ActiveStartDate = 20050816 SQLSchedule.Schedule.ActiveStartTimeOfDay = strTime &"00" ' this schedule has no end time or end date (they might be omitted) SQLSchedule.Schedule.ActiveEndDate = SQLDMO_NOENDDATE SQLSchedule.Schedule.ActiveEndTimeOfDay = SQLDMO_NOENDTIME ' add the schedule to the Job SQLJob.BeginAlter SQLJob.JobSchedules.Add SQLSchedule SQLJob.DoAlter 'Disconnect from SQL Server obSQLServer.Disconnect 'Make sure the SQL Server agent is running... Dim colServiceList Dim obService Dim objWMIService Set objWMIService = GetObject("winmgmts:" _ & "{impersonationLevel=impersonate}!\\.\root\cimv2") Dim strSvcQuery strSvcQuery="Select * from Win32_Service where Name='SQLSERVERAGENT'" If WScript.Arguments.Length=1 Then strSvcQuery="Select * from Win32_Service where Name='SQLAgent$"&WScript.Arguments(0)&"'" Set colServiceList = objWMIService.ExecQuery(strSvcQuery) For each obService in colServiceList If obService.Started = False Then MsgBox "The SQL Server Agent is not running." &_ vbCrLf &"Your job will not run until you start the SQLSERVERAGENT service!", 48, "SQL Agent warning" End If Next MsgBox "SQL Agent job created successfully", 64, "Success!" 'Clean up Set obService=Nothing Set objWMIService=Nothing Set SQLSchedule=Nothing Set obJob=Nothing Set obSQLServer=Nothing Set obJobStep=Nothing Set obShell=Nothing Set obCategory=Nothing