LOG SHIPPING FROM SCRATCH

,

We were experiencing significant issues with Cross Domain Log Shipping, even after getting help from Microsoft. The main reason was that in SQL Server 2000 you cannot access the Log Shipping Wizard from a scripting interface. This set of stored Procedures is all you need to setup a good log shipping framework in your organization Two of them will be put into scheduled SQL SERVER AGENT JOBS to run and backup, copy and apply logs at a schedule you determine. If you are going Cross Domain, I suggest reading the FAQ on Security for Log Shipping from M$ as you will need a trust in AD for your Agent Users.
You will need to fill in your network shares, etc as variables in the stored procedures. Most of them take input variables for these and are pretty portable.
Feel Free to Contact me with any questions....

/*====================================================================
LOG SHIPPING FROM SCRATCH

This set of stored Procedures is all you need to setup a good log shipping 
framework in your organization Two of them will be put into scheduled 
SQL SERVER AGENT JOBS to run and backup, copy and apply logs at a schedule 
you determine.

Feel Free to Contact me with any questions....
Adam Jorgensen
07/17/2005
adamJ@Diamond.com
====================================================================*/

/*-----------------------------------------------------------
This procedure  Initializes the backups  needed for log shipping and copies them to your target Server
USAGE:  EXEC SOURCE>MASTER.dbo.Sp_logship_init <'dbname'>,'\\networkShare\BackupInit.bak',  ' \\target\networkshare\'
-- @DBNAME - <'dbname'>
-- @CURRENTBACKUPFILE - '\\networkShare\BackupInit.bak'
-- @LOGTARGETSHARE - ' \\target\networkshare\' 
--------------------------------------------------------------- */
USE SOURCE.MASTER
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO
ALTER PROCEDURE DBO.SP_LogShip_Init
	@DBName Sysname,
	@CURRENTLOGFILE VARCHAR (50),
	@LOGTARGETSHARE VARCHAR (50)
AS
DECLARE 
@SQL  VARCHAR (256)
--CREATE BACKUP DEVICE
		EXEC sp_addumpdevice 'disk', 'DBBackupInit', @CURRENTBACKUPFILE
		PRINT 'BACKUP DEVICE CREATED'
--BACKUP LOG TO DEVICE
		BACKUP Database @DBName
		TO DBBackupInit
		PRINT 'BACKUP COMPLETED'
--Copy File To Target Server - suppressing Possible Overwrite with /Y
		SELECT @SQL = 'xcopy ' + @CURRENTBACKUPFILE + @LOGTargetShare+' /Y'
		EXEC master.dbo.xp_cmdshell @SQL
		PRINT 'FILE COPY COMPLETE'
--DROP BACKUP DEVICE SO IT CAN BE USED NEXT TIME - SIMPLE TO REPLACE WITH IF/EXISTS CHECK @ THE TOP
		EXEC sp_dropdevice 'DBBackupInit'
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
----------------------------------*/
/*TEST FOR STEP 1
 --CREATE BACKUP OF DB and Copy to Verio
 EXEC SOURCE.MASTER.dbo.Sp_logship_init <'dbname'>
 GO
*/
/*==============================================================================*/

/*-----------------------------------------------------------
This procedure  Initializes the backups  needed for log shipping on the target server 
USAGE: EXEC TARGET.MASTER.dbo.sp_logship_dbrestore ,<'dbname'>, 'L:\Undo.DAT', 'D:\MSSQL\Data\db_Data.MDF', 'L:\MSSQL\Data\db_log.LDF', '\\VSQL01V\Odimo_DB_Target\BackupInit.BAK'

--@DBNAME - <'dbname'>
--@UndoFile - <'L:\Undo.DAT'>
--@DataFile - <'D:\MSSQL\Data\DB_Data.MDF'>
--@LOGFILE - <'L:\MSSQL\Data\DB_log.LDF'>
--@BackupLocation - <'networkshare\BackupInit.BAK'>
----------------------------------*/
USE TARGET.MASTER
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO
ALTER PROCEDURE DBO.sp_logship_dbrestore
	@DBName Sysname,
	@UndoFile varchar(12),
	@DATAFILE VARCHAR(50),
	@LOGFILE VARCHAR(50),
	@BACKUPLOCATION VARCHAR (50)
AS
--RESTORE DB FROM BACKUPLOCATION in STANDBY MODE
RESTORE DATABASE @dbname
FROM DISK = @BackupLocation
WITH 
  MOVE 'DB_Data' TO @DATAFILE,
  MOVE 'DB_Log' TO @LOGFILE,
  STANDBY = @UNDOFILE
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
----------------------------------*/
/* TEST FOR STEP 2
 --RESTORE DB in Standby mode for Log Shipping
 EXEC TARGET.MASTER.dbo.sp_logship_dbrestore ,<'dbname'>, 'L:\Undo.DAT', 
	'D:\MSSQL\Data\db_Data.MDF', 'L:\MSSQL\Data\db_log.LDF', 
	'\\VSQL01V\Odimo_DB_Target\BackupInit.BAK'
 GO
*/
/*---------------------------------*/
/*==============================================================================*/
----------------------------------*/
--This procedure  backs the log up from the Source Server and pushes it to the Target Server
-- USAGE EXEC MASTER.DBO.SP_LOGSHIP <'DBNAME'>, '<NETWORKSHARE>\LogShip.TRN', '<NETWORKSHARE> /Y', ' <NETWORKSHARE> /Y'
	-- @DBNAME - <'DBNAME'>
	-- @LOGBACKUPSHARE - '<NETWORKSHARE>\LogShip.TRN'
	-- @TARGETLOGSHARE - ' <NETWORKSHARE> /Y'
	-- @SOURCEARCHIVE - ' <NETWORKSHARE> /Y'
----------------------------------*/
USE SOURCE.MASTER
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO
ALTER PROCEDURE DBO.SP_LogShip
	@DBName Sysname,
	@LOGBACKUPSHARE VARCHAR(50),
	@VERIOSHARE VARCHAR(50),
	@SOURCEARCHIVE VARCHAR(50)
AS
DECLARE
	@SQL Varchar (256)
--CREATE BACKUP DEVICE
EXEC sp_addumpdevice 'disk', 'LogShipSource', @LOGBACKUPSHARE
--BACKUP LOG TO DEVICE
BACKUP LOG @DBname
TO LogShipSource
--Copy File To Target Server
 SELECT @SQL = 'xcopy ' + @LOGBACKUPSHARE + @TARGETLOGSHARE
 EXEC master.dbo.xp_cmdshell @SQL
--Archive File - YOU WILL WANT TO ADD YOUR OWN INCREMENTING SCHEME TO THE FILENAMES
 SELECT @SQL = 'xcopy ' + @LOGBACKUPSHARE + @SOURCEARCHIVE
 EXEC master.dbo.xp_cmdshell @SQL
--Drop BackupDevice
 sp_dropdevice 'LogShipSource'
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
----------------------------------*/
/* TEST FOR STEP 3
--BACKUP LOG ON SOURCE AND MOVE TO TARGET
USAGE EXEC MASTER.DBO.SP_LOGSHIP 
		<'DBNAME'>, 
		'<NETWORKSHARE>\LogShip.TRN', 
		'<NETWORKSHARE> /Y', 
		' <NETWORKSHARE> /Y'
 GO
*/
/*---------------------------------*/
/*==============================================================================*/
----------------------------------*/
--This procedure  backs the log up from the Source Server and pushes it to the Target Server
--USAGE EXEC TARGET.MASTER.DBO.SP_LogShip_APPLYLOGS <'DBNAME'>,  '<NETWORKSHARE>\LogShip.TRN', ' <NETWORKSHARE> /Y'
	--@DBNAME SYSNAME,  - <'DBNAME'>
	--@LOGLOCATION VARCHAR(50), -  '<NETWORKSHARE>\LogShip.TRN'
	--@LOGARCHIVE VARCHAR (50) - ' <NETWORKSHARE> /Y'
----------------------------------*/
USE TARGET.MASTER
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO
			ALTER PROCEDURE DBO.SP_LogShip_APPLYLOGS
					@DBNAME SYSNAME,
					@LOGLOCATION VARCHAR(50),
					@LOGARCHIVE VARCHAR (50)
			AS
			DECLARE 
				@SQL VARCHAR (256)
			--Apply Transaction LOG and Recover Database
				SELECT @SQL = 'RESTORE LOG '+@DBNAME+' FROM DISK = '+@LOGLOCATION+ ' WITH NORECOVERY'
				EXEC @SQL
			--ARCHIVE LOG FILE ON TARGET
				SELECT @SQL = 'xcopy ' + @LOGLOCATION + @LOGARCHIVE
				EXEC master.dbo.xp_cmdshell @SQL
			--Delete Existing File
				SELECT @SQL = 'DEL ' + @LOGLOCATION
				EXEC master.dbo.xp_cmdshell @SQL
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
----------------------------------*/
/* TEST FOR STEP 3
--APPLY LAST LOG BACKED UP AND COPY IT TO TARGET DB THEN RESTORE IT AND ARCHIVE IT TO ARCHIVE LOCATION
EXEC TARGET.MASTER.DBO.SP_LogShip_APPLYLOGS <'DBNAME'>,  '<NETWORKSHARE>\LogShip.TRN', ' <NETWORKSHARE> /Y'
 GO
*/
/*---------------------------------*/
/*==============================================================================*/
----------------------------------*/
--This procedure  backs the log up from the Source Server and pushes it to the Target Server
--USAGE EXEC TARGET.MASTER.DBO.SP_LogShiprecover <'DBNAME'>,  '<NETWORKSHARE>\LogShip.TRN', ' <NETWORKSHARE> /Y'
	--@DBNAME SYSNAME,  - <'DBNAME'>
	--@LOGLOCATION VARCHAR(50), -  '<NETWORKSHARE>\LogShip.TRN'
	--@LOGARCHIVE VARCHAR (50) - ' <NETWORKSHARE> /Y'
----------------------------------*/
USE TARGET.MASTER
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

ALTER PROCEDURE DBO.SP_LogShipRecover
		@DBNAME SYSNAME,
		@LOGLOCATION VARCHAR(50),
		@LOGARCHIVE VARCHAR (50)
AS
DECLARE 
	@SQL VARCHAR (256)
--Apply Transaction LOG and Recover Database
SELECT @SQL = 'RESTORE LOG '+@DBNAME+' FROM DISK = '+@LOGLOCATION+ ' WITH RECOVERY'
EXEC @SQL
--ARCHIVE LOG FILE ON TARGET
 SELECT @SQL = 'xcopy ' + @LOGLOCATION + @LOGARCHIVE
 EXEC master.dbo.xp_cmdshell @SQL
--Delete Existing File
SELECT @SQL = 'DEL ' + @LOGLOCATION
EXEC master.dbo.xp_cmdshell @SQL

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

----------------------------------*/
/* TEST FOR STEP 3
--APPLY LAST LOG BACKED UP AND COPY IT TO TARGET DB THEN RESTORE IT AND ARCHIVE IT TO ARCHIVE LOCATION
EXEC TARGET.MASTER.DBO.SP_LogShipRecover <'DBNAME'>,  '<NETWORKSHARE>\LogShip.TRN', ' <NETWORKSHARE> /Y'
 GO
*/
/*---------------------------------*/
/*==============================================================================*/
--This section will Drop any users you need to fix in the DB and then 
--re-add them to the DB in the specified role

--CLEAN UP USERS
EXEC VSQL01v.MASter.dbo.SP_configure 'Allow Updates', 1
Reconfigure With Override
GO
eXEC VSQL01V.ODIMO.dbo.SP_DROPUSER 'dbadmin'
EXEC VSQL01V.ODIMO.dbo.SP_ADDUSER 'dbadmin', 'dbadmin', 'db_owner'
GO
EXEC VSQL01v.MASter.dbo.SP_configure 'Allow Updates', 0
Reconfigure With Override
/*==============================================================================*/

Rate

Share

Share

Rate