- Date: 4 Sep 2005
- Versions Affected: SQL Backup 3.2
The following Visual Basic script can be run on a SQL Server to replicate a database to another server as a read-only copy and periodically update the copy by restoring the logs of the source database to the target server.
For simplicity, it assumes the following:
- You are logged in with a Windows domain account that has sysadmin access to both servers
- You have created a share on one of these servers that you and the account that SQLSERVERAGENT uses has full rights to it
- The SQLSERVERAGENT service is running on both servers
- The SQL Backup software is installed on both servers, including the extended stored procedure
- The database does not already exist on the target server
To install the script, copy and paste it into Notepad and save it as logship.vbs. Then you can run it either by double-clicking it or running it from the command prompt using cscript.exe. It will accept an instance name of a SQL Server if the database being shipped is not on the default instance:
cscript logship.vbs SECONDINSTANCE
'Script to ship a database on the local server to another server for SQL Backup
'This requires a WINNT logon that has access to both servers + a common share.
'$VER: 1.1 - fixed instance support (pass -I to SQL Backup), added multiple file/filegroup/log support
If MsgBox("This will initialize log shipping (replication)"&vbCrLf&_
"for a database on the local server to another server."&vbCrLf &_
"Please ensure that you are logged in using an account that has admin access to both servers."&vbCrLf &_
"You will also need a share that is accessible by the SQL Server agent account,"&vbCrLf &_
"the SQL Server account, and the logged in user account.", vbOKCancel, "SQL Backup Log Shipping")=2 Then WScript.Quit
Dim obDMO
Dim chosenDatabase
Dim bDatabaseNameOK
Dim obShell
Dim obLocalServer
Dim obDestServer
Dim obDatabase
Dim bDaily
Dim iInterval
Dim iCompression
Dim iEraseFiles
Dim strEmailError
Dim strBackupCommand
Dim strEncryptPassword
Dim strLocalServerName
Dim strDestServerName
'Get the local server name
Set obShell=CreateObject("Wscript.Shell")
Set obEnv=obShell.Environment("PROCESS")
strLocalServerName=obEnv("COMPUTERNAME")
'You can specify an instance name as an argument...
If WScript.Arguments.Count>0 Then strLocalServerName=strLocalServerName &"\"&WScript.Arguments(0)
chosenDatabase=InputBox("Enter the name of the local database that you want to ship.", "Choose database")
Set obLocalServer=CreateObject("SQLDMO.SQLServer")
obLocalServer.LoginTimeout = 10
' Windows NT security
obLocalServer.LoginSecure = True
obLocalServer.Connect strLocalServerName
'Let script warn user if the database doesn't exist
Set obDataBase=Nothing
ON ERROR RESUME NEXT
Set obDataBase=obLocalServer.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
'Check the recovery model. Do not allow log shipping unless it is the Full model
If Not obDataBase.DBOption.RecoveryModel=2 Then
obShell.Popup "The logs cannot be shipped unless the recovery model for "&chosenDatabase&" is ""Full Recovery"".", 10, "Cannot create job",16
WScript.Quit
End If
'Let user enter the destination server name
strDestServerName=InputBox("Enter the name of the SQL Server to ship the logs to" &vbCrLf &_
"for example SERVER or SERVER\INSTANCE","Enter destination server")
Set obDestServer=Nothing
ON ERROR RESUME NEXT
Set obDestServer=CreateObject("SQLDMO.SQLServer")
obDestServer.LoginTimeout = 10
' Windows NT security
obDestServer.LoginSecure = True
obDestServer.Connect strDestServerName
ON ERROR GOTO 0
If obDestServer Is Nothing Then
obShell.Popup "The server does not exist or you do not have rights to it.", 10, "Cannot create job",16
WScript.Quit
End If
iInterval=InputBox("Specify the interval, in minutes, that you want the log shipping to occur." _
,"Log shipping interval")
If Not IsNumeric(iInterval) Then
obShell.Popup "You must enter a log shipping interval!", 10, "Cannot create job",16
WScript.Quit
End If
Dim strCopyTo
strCopyTo=InputBox("Enter the UNC path to copy the log backups to" &vbCrLf &_
"ie. \\TargetServer\Share\"&chosenDataBase&"\logship"&vbCrLf &_
"Do NOT use a trailing slash!", "Choose log shipping share")
If Not LEN(strCopyTo)>0 Then
obShell.Popup "You must enter a share to copy the log files to!", 10, "Cannot create job",16
WScript.Quit
End If
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 log backup of "&chosenDataBase&" every "&iInterval&" minutes "&vbCrLf &" with compression "&iCompression&vbCrLf &_
"Copy these log files (ship) to "&strDestServerName&" via share "&strCopyTo&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
If MsgBox(strSettings,33, "Verify settings...")=2 Then WScript.Quit
'connect to share and add a 'complete' subfolder
Dim obFileSys
Set obFileSys=CreateObject("Scripting.FileSystemObject")
If Not obFileSys.FolderExists(strCopyTo&"\complete") Then obFileSys.CreateFolder(strCopyTo&"\complete")
'Do a full backup of source db to share
Dim strResult, backupCmd
backupCmd="EXEC master..sqlbackup '-SQL ""BACKUP DATABASE ["&chosenDataBase&"] To DISK=''"&strCopyTo&"\"&chosenDatabase&"_INIT_Log_Ship.sqb'' WITH INIT"
If LEN(strEncryptPassword)>0 Then strBackupCommand=backupCmd&" WITH PASSWORD=''"&strEncryptPassword&"''"
backupCmd=backupCmd & """ -E"
'If this server is an instance, we need to know so we can pass -I to SQL Backup
If InStr(1, strLocalServerName, "\", 1)>0 Then
backupCmd=backupCmd&" -I "&SPLIT(strLocalServerName, "\", -1, 1)(1)
End If
backupCmd=backupCmd&"'"
MsgBox "We will now do a full backup of "&chosenDatabase&_
", create a new database named """&chosenDatabase&""" on """&strDestServerName&""""&vbCrLf &_
" and restore the database to it.", 0, "Ready to backup/restore"
strResult=""
Dim obQryResult, i
Set obQryResult=obLocalServer.ExecuteWithResults(CStr(backupCmd))
For i=1 To obQryResult.Rows
strResult=strResult & obQryResult.GetColumnString(i, 1)&vbCrLf
Next
MsgBox "Backup result: "&vbCrLf&strResult
'Restore our backup to a new database
backupCmd="EXEC master..sqlbackup '-SQL ""RESTORE DATABASE ["&chosenDataBase&"] From DISK=''"&strCopyTo&"\"&chosenDatabase&"_INIT_Log_Ship.sqb''"&_
" WITH STANDBY=''"&obDestServer.Databases("model").PrimaryFilePath&chosenDatabase&"_UNDO.DAT'',"
For Each fg In obDatabase.FileGroups
For Each fgfile In fg.DBFiles
backupCmd=backupCmd&" MOVE ''"&fgfile.Name&"'' TO ''"&obDestServer.Databases("model").PrimaryFilePath &fgfile.Name&".MDF'',"
Next
Next
For Each lf In obDatabase.TransactionLog.LogFiles
backupCmd=backupCmd&" MOVE ''"&lf.Name&"'' TO ''"&obDestServer.Databases("model").PrimaryFilePath &lf.Name&".LDF'',"
Next
backupCmd=LEFT(backupCmd, LEN(backupCmd)-1) 'Lop off trailing comma
If LEN(strEncryptPassword)>0 Then strBackupCommand=backupCmd&", PASSWORD=''"&strEncryptPassword&"''"
backupCmd=backupCmd & """ -E"
'If this server is an instance, we need to know so we can pass -I to SQL Backup
If InStr(1, strDestServerName, "\", 1)>0 Then
backupCmd=backupCmd&" -I "&SPLIT(strDestServerName, "\", -1, 1)(1)
End If
backupCmd=backupCmd&"'"
Set obQryResult=obDestServer.ExecuteWithResults(CStr(backupCmd))
strResult=""
For i=1 To obQryResult.Rows
strResult=strResult & obQryResult.GetColumnString(i, 1)&vbCrLf
Next
MsgBox "Restore result: "&vbCrLf&strResult
obFileSys.DeleteFile(strCopyTo&"\"&chosenDatabase&"_INIT_Log_Ship.sqb")
'Create the log shipping job --
CreateJob False, chosenDatabase, iInterval
'Create the log restoring job
CreateJob True, chosenDatabase, iInterval
obDestServer.Disconnect
obLocalServer.Disconnect
Set obFileSys=Nothing
Private Function CreateJob(bRestore, strDatabaseName, iInterval)
' create and initialize the Job object
Dim SQLJob
Dim obJobStep
Dim obServer
If bRestore=True Then Set obServer=obDestServer
If bRestore=False Then Set obServer=obLocalServer
Set SQLJob=CreateObject("SQLDMO.Job")
SQLJob.Name = "SQL Backup log shipping "&strDatabaseName
SQLJob.Description = "SQL Backup log shipping "&strDatabasename
' add to the Jobs collection
obServer.JobServer.Jobs.Add SQLJob
Set obJobStep=CreateObject("SQLDMO.JobStep")
If bRestore=False Then obJobStep.Name = "Step 1: Log backup with copy"
If bRestore=True Then obJobStep.Name= "Step 1: Log restore with move"
obJobStep.StepId = 1
obJobStep.SubSystem = "TSQL"
If bRestore=False Then obJobStep.DatabaseName = strDatabaseName
If bRestore=True Then obJobStep.DatabaseName = "master"
'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
If bRestore=False Then
strBackupCommand=strBackupCommand &_
"SET @backupcmd = '-SQL ""BACKUP LOG ["&strDatabaseName&"] TO DISK=''<AUTO>'''+" &vbCrLf &_
"' WITH COMPRESSION="&iCompression&", NAME=''Database "&strDatabaseName&", (log) '+@datestamp +"&vbCrLf &_
"''', DESCRIPTION=''Database "&strDatabaseName&", (log) '+@datestamp+''', COPYTO=''"&strCopyTo&"''"
End If
If bRestore=True Then
strBackupCommand=strBackupCommand &_
"SET @backupcmd = '-SQL ""RESTORE LOG ["&strDatabaseName&"] FROM DISK=''"&strCopyTo&"\*.sqb'''+" &vbCrLf &_
"' WITH MOVETO=''"&strCopyTo&"\complete'', STANDBY=''"&obServer.Databases("model").PrimaryFilePath&chosenDatabase&"_UNDO.DAT''"
End If
If LEN(strEmailError)>0 Then strBackupCommand=strBackupCommand&", MAILTO_ONERROR=''"&strEmailError&"''"
If LEN(strEncryptPassword)>0 Then strBackupCommand=strBackupCommand&", PASSWORD=''"&strEncryptPassword&"''"
If LEN(iEraseFiles)>0 AND bRestore=False Then strBackupCommand=strBackupCommand &", ERASEFILES="&iEraseFiles
strBackupCommand=strBackupCommand & """ -E"
'Specify Instance, if necessary
If bRestore=True Then
If InStr(1, strDestServerName, "\", 1)>0 Then strBackupCommand=strBackupCommand&" -I "&SPLIT(strDestServerName, "\", -1, 1)(1)
End If
If bRestore=False Then
If InStr(1, strLocalServerName, "\", 1)>0 Then strBackupCommand=strBackupCommand&" -I "&SPLIT(strLocalServerName, "\", -1, 1)(1)
End If
strBackupCommand=strBackupCommand &"'"&vbCrLf &"EXEC @success=master..sqlbackup @backupcmd" &vbCrLf &_
"IF @success=0" &vbCrLf &_
"BEGIN" &vbCrLf &_
"RAISERROR('Backup failed for database "&strDatabaseName&"', 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(obServer.Name)
'Schedule this job
' a new JobSchedule for daily backup
Dim SQLSchedule
Set SQLSchedule=CreateObject("SQLDMO.JobSchedule")
SQLSchedule.Name = "Log shipping for "&strDatabaseName
SQLSchedule.Schedule.FrequencyType = 4 'Daily
SQLSchedule.Schedule.FrequencyInterval = 1 'Every day
SQLSchedule.Schedule.FrequencySubDay = 4 'Evaluate interval in minutes
SQLSchedule.Schedule.FrequencySubDayInterval = cLng(iInterval) 'number of minutes
SQLSchedule.Schedule.ActiveStartDate = 20050816 'arbitrary; not important
If bRestore=False Then SQLSchedule.Schedule.ActiveStartTimeOfDay = "000100"
'Make sure we stagger backups and restores...
If bRestore=True Then
Dim iHalfInterval
iHalfInterval=iInterval/2
If iHalfInterval>10 Then iHalfInterval="0"&iHalfInterval
SQLSchedule.Schedule.ActiveStartTimeOfDay = "00"&iHalfInterval&"00"
End If
' 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
End Function
MsgBox "Log shipping initialized successfully", 64, "Success!"
'Clean up
Set obDataBase=Nothing
Set obService=Nothing
Set objWMIService=Nothing
Set SQLSchedule=Nothing
Set obJob=Nothing
Set SQLServer=Nothing
Set obJobStep=Nothing
Set obShell=Nothing
SQL Backup version 4 now has a log shipping wizard. This automates the process of setting up log shipping.
- Date: 4 Sep 2005
- Versions Affected: SQL Backup 3.2
The following Visual Basic script can be run on a SQL Server to replicate a database to another server as a read-only copy and periodically update the copy by restoring the logs of the source database to the target server.For simplicity, it assumes the following:
- You are logged in with a Windows domain account that has sysadmin access to both servers
- You have created a share on one of these servers that you and the account that SQLSERVERAGENT uses has full rights to it
- The SQLSERVERAGENT service is running on both servers
- The SQL Backup software is installed on both servers, including the extended stored procedure
- The database does not already exist on the target server
To install the script, copy and paste it into Notepad and save it as logship.vbs. Then you can run it either by double-clicking it or running it from the command prompt using cscript.exe. It will accept an instance name of a SQL Server if the database being shipped is not on the default instance: cscript logship.vbs SECONDINSTANCE'Script to ship a database on the local server to another server for SQL Backup 'This requires a WINNT logon that has access to both servers + a common share. '$VER: 1.1 - fixed instance support (pass -I to SQL Backup), added multiple file/filegroup/log support If MsgBox("This will initialize log shipping (replication)"&vbCrLf&_ "for a database on the local server to another server."&vbCrLf &_ "Please ensure that you are logged in using an account that has admin access to both servers."&vbCrLf &_ "You will also need a share that is accessible by the SQL Server agent account,"&vbCrLf &_ "the SQL Server account, and the logged in user account.", vbOKCancel, "SQL Backup Log Shipping")=2 Then WScript.Quit Dim obDMO Dim chosenDatabase Dim bDatabaseNameOK Dim obShell Dim obLocalServer Dim obDestServer Dim obDatabase Dim bDaily Dim iInterval Dim iCompression Dim iEraseFiles Dim strEmailError Dim strBackupCommand Dim strEncryptPassword Dim strLocalServerName Dim strDestServerName 'Get the local server name Set obShell=CreateObject("Wscript.Shell") Set obEnv=obShell.Environment("PROCESS") strLocalServerName=obEnv("COMPUTERNAME") 'You can specify an instance name as an argument... If WScript.Arguments.Count>0 Then strLocalServerName=strLocalServerName &"\"&WScript.Arguments(0) chosenDatabase=InputBox("Enter the name of the local database that you want to ship.", "Choose database") Set obLocalServer=CreateObject("SQLDMO.SQLServer") obLocalServer.LoginTimeout = 10 ' Windows NT security obLocalServer.LoginSecure = True obLocalServer.Connect strLocalServerName 'Let script warn user if the database doesn't exist Set obDataBase=Nothing ON ERROR RESUME NEXT Set obDataBase=obLocalServer.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 'Check the recovery model. Do not allow log shipping unless it is the Full model If Not obDataBase.DBOption.RecoveryModel=2 Then obShell.Popup "The logs cannot be shipped unless the recovery model for "&chosenDatabase&" is ""Full Recovery"".", 10, "Cannot create job",16 WScript.Quit End If 'Let user enter the destination server name strDestServerName=InputBox("Enter the name of the SQL Server to ship the logs to" &vbCrLf &_ "for example SERVER or SERVER\INSTANCE","Enter destination server") Set obDestServer=Nothing ON ERROR RESUME NEXT Set obDestServer=CreateObject("SQLDMO.SQLServer") obDestServer.LoginTimeout = 10 ' Windows NT security obDestServer.LoginSecure = True obDestServer.Connect strDestServerName ON ERROR GOTO 0 If obDestServer Is Nothing Then obShell.Popup "The server does not exist or you do not have rights to it.", 10, "Cannot create job",16 WScript.Quit End If iInterval=InputBox("Specify the interval, in minutes, that you want the log shipping to occur." _ ,"Log shipping interval") If Not IsNumeric(iInterval) Then obShell.Popup "You must enter a log shipping interval!", 10, "Cannot create job",16 WScript.Quit End If Dim strCopyTo strCopyTo=InputBox("Enter the UNC path to copy the log backups to" &vbCrLf &_ "ie. \\TargetServer\Share\"&chosenDataBase&"\logship"&vbCrLf &_ "Do NOT use a trailing slash!", "Choose log shipping share") If Not LEN(strCopyTo)>0 Then obShell.Popup "You must enter a share to copy the log files to!", 10, "Cannot create job",16 WScript.Quit End If 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 log backup of "&chosenDataBase&" every "&iInterval&" minutes "&vbCrLf &" with compression "&iCompression&vbCrLf &_ "Copy these log files (ship) to "&strDestServerName&" via share "&strCopyTo&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 If MsgBox(strSettings,33, "Verify settings...")=2 Then WScript.Quit 'connect to share and add a 'complete' subfolder Dim obFileSys Set obFileSys=CreateObject("Scripting.FileSystemObject") If Not obFileSys.FolderExists(strCopyTo&"\complete") Then obFileSys.CreateFolder(strCopyTo&"\complete") 'Do a full backup of source db to share Dim strResult, backupCmd backupCmd="EXEC master..sqlbackup '-SQL ""BACKUP DATABASE ["&chosenDataBase&"] To DISK=''"&strCopyTo&"\"&chosenDatabase&"_INIT_Log_Ship.sqb'' WITH INIT" If LEN(strEncryptPassword)>0 Then strBackupCommand=backupCmd&" WITH PASSWORD=''"&strEncryptPassword&"''" backupCmd=backupCmd & """ -E" 'If this server is an instance, we need to know so we can pass -I to SQL Backup If InStr(1, strLocalServerName, "\", 1)>0 Then backupCmd=backupCmd&" -I "&SPLIT(strLocalServerName, "\", -1, 1)(1) End If backupCmd=backupCmd&"'" MsgBox "We will now do a full backup of "&chosenDatabase&_ ", create a new database named """&chosenDatabase&""" on """&strDestServerName&""""&vbCrLf &_ " and restore the database to it.", 0, "Ready to backup/restore" strResult="" Dim obQryResult, i Set obQryResult=obLocalServer.ExecuteWithResults(CStr(backupCmd)) For i=1 To obQryResult.Rows strResult=strResult & obQryResult.GetColumnString(i, 1)&vbCrLf Next MsgBox "Backup result: "&vbCrLf&strResult 'Restore our backup to a new database backupCmd="EXEC master..sqlbackup '-SQL ""RESTORE DATABASE ["&chosenDataBase&"] From DISK=''"&strCopyTo&"\"&chosenDatabase&"_INIT_Log_Ship.sqb''"&_ " WITH STANDBY=''"&obDestServer.Databases("model").PrimaryFilePath&chosenDatabase&"_UNDO.DAT''," For Each fg In obDatabase.FileGroups For Each fgfile In fg.DBFiles backupCmd=backupCmd&" MOVE ''"&fgfile.Name&"'' TO ''"&obDestServer.Databases("model").PrimaryFilePath &fgfile.Name&".MDF''," Next Next For Each lf In obDatabase.TransactionLog.LogFiles backupCmd=backupCmd&" MOVE ''"&lf.Name&"'' TO ''"&obDestServer.Databases("model").PrimaryFilePath &lf.Name&".LDF''," Next backupCmd=LEFT(backupCmd, LEN(backupCmd)-1) 'Lop off trailing comma If LEN(strEncryptPassword)>0 Then strBackupCommand=backupCmd&", PASSWORD=''"&strEncryptPassword&"''" backupCmd=backupCmd & """ -E" 'If this server is an instance, we need to know so we can pass -I to SQL Backup If InStr(1, strDestServerName, "\", 1)>0 Then backupCmd=backupCmd&" -I "&SPLIT(strDestServerName, "\", -1, 1)(1) End If backupCmd=backupCmd&"'" Set obQryResult=obDestServer.ExecuteWithResults(CStr(backupCmd)) strResult="" For i=1 To obQryResult.Rows strResult=strResult & obQryResult.GetColumnString(i, 1)&vbCrLf Next MsgBox "Restore result: "&vbCrLf&strResult obFileSys.DeleteFile(strCopyTo&"\"&chosenDatabase&"_INIT_Log_Ship.sqb") 'Create the log shipping job -- CreateJob False, chosenDatabase, iInterval 'Create the log restoring job CreateJob True, chosenDatabase, iInterval obDestServer.Disconnect obLocalServer.Disconnect Set obFileSys=Nothing Private Function CreateJob(bRestore, strDatabaseName, iInterval) ' create and initialize the Job object Dim SQLJob Dim obJobStep Dim obServer If bRestore=True Then Set obServer=obDestServer If bRestore=False Then Set obServer=obLocalServer Set SQLJob=CreateObject("SQLDMO.Job") SQLJob.Name = "SQL Backup log shipping "&strDatabaseName SQLJob.Description = "SQL Backup log shipping "&strDatabasename ' add to the Jobs collection obServer.JobServer.Jobs.Add SQLJob Set obJobStep=CreateObject("SQLDMO.JobStep") If bRestore=False Then obJobStep.Name = "Step 1: Log backup with copy" If bRestore=True Then obJobStep.Name= "Step 1: Log restore with move" obJobStep.StepId = 1 obJobStep.SubSystem = "TSQL" If bRestore=False Then obJobStep.DatabaseName = strDatabaseName If bRestore=True Then obJobStep.DatabaseName = "master" '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 If bRestore=False Then strBackupCommand=strBackupCommand &_ "SET @backupcmd = '-SQL ""BACKUP LOG ["&strDatabaseName&"] TO DISK=''<AUTO>'''+" &vbCrLf &_ "' WITH COMPRESSION="&iCompression&", NAME=''Database "&strDatabaseName&", (log) '+@datestamp +"&vbCrLf &_ "''', DESCRIPTION=''Database "&strDatabaseName&", (log) '+@datestamp+''', COPYTO=''"&strCopyTo&"''" End If If bRestore=True Then strBackupCommand=strBackupCommand &_ "SET @backupcmd = '-SQL ""RESTORE LOG ["&strDatabaseName&"] FROM DISK=''"&strCopyTo&"\*.sqb'''+" &vbCrLf &_ "' WITH MOVETO=''"&strCopyTo&"\complete'', STANDBY=''"&obServer.Databases("model").PrimaryFilePath&chosenDatabase&"_UNDO.DAT''" End If If LEN(strEmailError)>0 Then strBackupCommand=strBackupCommand&", MAILTO_ONERROR=''"&strEmailError&"''" If LEN(strEncryptPassword)>0 Then strBackupCommand=strBackupCommand&", PASSWORD=''"&strEncryptPassword&"''" If LEN(iEraseFiles)>0 AND bRestore=False Then strBackupCommand=strBackupCommand &", ERASEFILES="&iEraseFiles strBackupCommand=strBackupCommand & """ -E" 'Specify Instance, if necessary If bRestore=True Then If InStr(1, strDestServerName, "\", 1)>0 Then strBackupCommand=strBackupCommand&" -I "&SPLIT(strDestServerName, "\", -1, 1)(1) End If If bRestore=False Then If InStr(1, strLocalServerName, "\", 1)>0 Then strBackupCommand=strBackupCommand&" -I "&SPLIT(strLocalServerName, "\", -1, 1)(1) End If strBackupCommand=strBackupCommand &"'"&vbCrLf &"EXEC @success=master..sqlbackup @backupcmd" &vbCrLf &_ "IF @success=0" &vbCrLf &_ "BEGIN" &vbCrLf &_ "RAISERROR('Backup failed for database "&strDatabaseName&"', 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(obServer.Name) 'Schedule this job ' a new JobSchedule for daily backup Dim SQLSchedule Set SQLSchedule=CreateObject("SQLDMO.JobSchedule") SQLSchedule.Name = "Log shipping for "&strDatabaseName SQLSchedule.Schedule.FrequencyType = 4 'Daily SQLSchedule.Schedule.FrequencyInterval = 1 'Every day SQLSchedule.Schedule.FrequencySubDay = 4 'Evaluate interval in minutes SQLSchedule.Schedule.FrequencySubDayInterval = cLng(iInterval) 'number of minutes SQLSchedule.Schedule.ActiveStartDate = 20050816 'arbitrary; not important If bRestore=False Then SQLSchedule.Schedule.ActiveStartTimeOfDay = "000100" 'Make sure we stagger backups and restores... If bRestore=True Then Dim iHalfInterval iHalfInterval=iInterval/2 If iHalfInterval>10 Then iHalfInterval="0"&iHalfInterval SQLSchedule.Schedule.ActiveStartTimeOfDay = "00"&iHalfInterval&"00" End If ' 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 End Function MsgBox "Log shipping initialized successfully", 64, "Success!" 'Clean up Set obDataBase=Nothing Set obService=Nothing Set objWMIService=Nothing Set SQLSchedule=Nothing Set obJob=Nothing Set SQLServer=Nothing Set obJobStep=Nothing Set obShell=Nothing