SQLBkup 32 - Script to create log shipping for a database

Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
edited February 20, 2006 12:42PM in Knowledge Base
  • 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
· Share on Twitter

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    SQL Backup version 4 now has a log shipping wizard. This automates the process of setting up log shipping.

    You may still need to use this script if you are going to perform log shipping using the standard version of SQL Backup, because the log shipping wizard in the standard version can only 'see' the local SQL Servers.

    Also bear in mind, if you are going to use this VBScript on SQL Server 2005, you will need to meke sure you have the legacy components installed (sqldmo.dll, which used to come as standard with SQL Server 2000).
    · Share on Twitter
Sign In or Register to comment.