Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Custom Log Shipping

By icata, 2005/02/15

There are mainly two components. One job that is managing the full database backup - restore and a second job that manage the transaction log backup - restore. At this two a new one similar with the full database backup - restore can be added for the differential backups if is the case.

I will post only the one for full backup. If you want to see the one for transaction log please let me know and I will post it. It is like the full backup one but more simpler.

Hope you will find it usefull.
I am adding also the Transaction Log shipping part of the Log Shipping.

Here are the steps for the full backup.
1. Disable the transaction log job
EXEC msdb.dbo.sp_update_job
            	@job_name                     = 'transaction log backup - Log Shipping', 
            	@enabled                      = 0
2. Full database backup to a dump device. I used the sp_executesql
3. Compress the backup using gzip. You have to test to make sure that it make sense for you (time, network load, etc)
\\server\C$\GZIP\gzip.exe "\\server\D$\Program Files\Microsoft SQL Server\MSSQL\BACKUP\FullBackup\database_DUMP.BAK" -f -v
4. Copy the compressed/uncompressed file
xcopy "\\server\D$\Program Files\Microsoft SQL Server\MSSQL\BACKUP\FullBackup\database_DUMP.BAK.gz" \\logshipping server\C$\FullBackup\  /S /E /Y
Instead of xcopy you can use the FTP. I think it is useful for big backups to prevent issues caused by the noise on the WAN or WAN failure.
5. Uncompress backup file
C:\GZIP\gzip.exe -d  \\logshipping server\C$\FullBackup\database_DUMP.BAK.gz -v
6. Restore database in stand by mode
7. Check database after restore 
DBCC CHECKDB ('database')
8. Enable transaction log backup job
EXEC msdb.dbo.sp_update_job
            	@job_name                     = 'transaction log backup - Log Shipping', 
            	@enabled                      = 1
9. Move backup to a history folder
USE database

DECLARE @cmdStr nvarchar(250), @RC int, @ErrDesc varchar(500)

--the format of the file will be yyyymmddhhmmss
SET @cmdStr = N'MOVE  \\logshipping server\C$\FullBackup\database_DUMP.BAK  C:\Test\database_DUMP' + 
	CONVERT(varchar(50), GETDATE(), 112) + 
	SUBSTRING(CONVERT(varchar(8), GETDATE(),108),1,2)+ 
	SUBSTRING(CONVERT(varchar(8), GETDATE(),108),4,2)+ 
	SUBSTRING(CONVERT(varchar(8), GETDATE(),108),7,2) + '.BAK'


EXEC @RC = master.dbo.xp_cmdshell @cmdStr,NO_OUTPUT

IF @RC <> 0
    GOTO Error_Handler
RETURN

Error_Handler:
  SET @ErrDesc = 
    'Error number ' + convert(varchar(9),@RC) + ' occurred copying the full database backup to history folder'
  RAISERROR (@ErrDesc,19,1)
    WITH LOG
  RETURN
10. Delete files older then a week - you can adapt it for your own situation (ActiveX Script)
Dim fso, Folder, Files, File, datecreated
  
Set fso = CreateObject("Scripting.FileSystemObject")
  
'Get handles to folder current location. 
Set Folder = fso.GetFolder("C:\Test")

Set Files = Folder.Files
If Files.Count > 0 Then
	For Each File In Files
		'Get files creation date 
		If file.DateLastModified < date - 7 Then
			' Delete the files.
			File.Delete
		End If
      	Next
End If


Set fso = Nothing
Set Folder  = Nothing
Set Files = Nothing
Set File = Nothing
11. Success email - I used xp_smtp_sendmail
DECLARE @rc int, @Message varchar(200), @getdate varchar(50)

SELECT @getdate = GETDATE()

SET @Message = 'Last step was executed ' + @getdate

exec @rc = master.dbo.xp_smtp_sendmail
	@FROM	= N'server',
	@TO		= N'email address',
	@server 	= N'smtp server',
	@subject	='Database Full backup - log shipping job succeed',
	@Message	=@Message

select RC = @rc 
12. Fail email
DECLARE @rc int, @Message varchar(200), @getdate varchar(50) , @attachments varchar(1000)

SELECT @getdate = GETDATE()

SET @Message = 'Last step was executed ' + @getdate
SET @attachments = 'log file to attach - use UNC'


exec @rc = master.dbo.xp_smtp_sendmail
	@FROM	= N'server',
	@TO		= N'email address',
	@server 	= N'SMTP server',
	@subject	='database Full backup - log shipping job failed',
	@Message	=@Message,
	@attachments	=@attachments

select RC = @rc 

--For the transaction log shipping. I have set up all the log shipping jobs to run out of destination server to make the management of the prod server easy and to take some load out of it.

1.Cluster - Transaction log backup

DECLARE @sqlStr nvarchar(4000)

SET @sqlStr = N'USE master; 
	BACKUP LOG [database] TO [database_Log_DUMP] 
	WITH  INIT ,  NOUNLOAD ,  NAME = N''database backup'',  
	NOSKIP ,  STATS = 10,  DESCRIPTION = N''Transaction log backup'',  NOFORMAT 

DECLARE @i INT

select @i = position from msdb..backupset 
where database_name=''database''and type!=''F'' and 
	backup_set_id=(select max(backup_set_id) 
		from msdb..backupset 
		where database_name=''database'')

RESTORE VERIFYONLY FROM  [database_Log_DUMP]  WITH FILE = @i'

EXEC [source server].master.dbo.sp_executesql @sqlStr

2. Cluster - Copy the database Log backup to [DR server]

copy "\\[source server]\D$\Program Files\Microsoft SQL Server\MSSQL\BACKUP\database_LogBackup\database_Log_DUMP.BAK" "\\[DR server]\D$\SQLBackups\database_LogBackup\" /V /Y

3. Restore Transaction Log Backup

RESTORE LOG database
FROM database_Log_DUMP 
WITH STANDBY = 'D:\SQLBackups\Standby\undo_database.ldf'

4. Set database in restricted mode access

EXEC sp_dboption 'database', 'dbo use only', 'TRUE'