LOG SHIPPING FROM SCRATCH

/*====================================================================
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
/*==============================================================================*/