SQLBkup 32 - Auto-create scheduled jobs

• 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