quote:

---

Originally posted by Sitka

So what is my question?
If I make jobs to handle the full backup and transaction log backup with the intent of using them for log shipping how can I present them to someone in a maintenance plan?

---

quote:

---

You don't create a maintenance plan on the destination server.

---

quote:

---

Also, you should not have any other maintenance plans or jobs that are doing BACKUP LOG command. All logs need to be backed up by log shipping plan.

---

quote:

---

Actually you should have different names for the transaction logs that way they aren't overwritten. By overwriting them, you could cause log shipping to break if ever the copy fails.

---

quote:

---

A database maintenance plan is not required for log shipping

---

quote:

---

After that, the maintenance plan takes over and only uses transaction log backups.

---

LIVE SERVER - > @@servername = FRED

ALTER    PROCEDURE ls_userlist_backup

AS SET NOCOUNT ON

BACKUP LOG userlist WITH TRUNCATE_ONLY
WAITFOR DELAY '00:00:05'

DECLARE @localdirectoryname nvarchar(128)
DECLARE @filename nvarchar(128)
DECLARE @fulllocalpath nvarchar(128)

SELECT @localdirectoryname = 'F:\LSBackup\'
SELECT @filename = 'userlist_' + CONVERT(CHAR(10),GETDATE(),110) + '_' + REPLACE(CONVERT(VARCHAR(5),GETDATE(),108), ':','') + '.bak'
SELECT @fulllocalpath = @localdirectoryname + @filename

BACKUP DATABASE userlist TO DISK = @fulllocalpath WITH INIT
WAITFOR DELAY '00:00:05'

DECLARE @cmd nvarchar(128)
SET @cmd = 'copy ' + @fulllocalpath + ' \\WILMA\F$\LSBackup'
EXEC master..xp_cmdshell @cmd , NO_OUTPUT
WAITFOR DELAY '00:00:05'

DECLARE @sourcefilenameout nvarchar(128)
SELECT @sourcefilenameout =  @filename
EXEC WILMA.master.dbo.ls_userlist_backup_restore @sourcefilename = @sourcefilenameout



ALTER  PROCEDURE ls_userlist_backup_tlog

AS SET NOCOUNT ON

DECLARE @localdirectoryname nvarchar(128)
DECLARE @filename nvarchar(128)
DECLARE @fulllocalpath nvarchar(128)

SELECT @localdirectoryname = 'F:\LSBackup\'
SELECT @filename = 'userlist_' + CONVERT(CHAR(10),GETDATE(),110) + '_' + REPLACE(CONVERT(VARCHAR(5),GETDATE(),108), ':','') + '.trn'
SELECT @fulllocalpath = @localdirectoryname + @filename

BACKUP LOG userlist TO DISK = @fulllocalpath WITH NO_TRUNCATE
WAITFOR DELAY '00:00:05'

DECLARE @cmd nvarchar(128)
SET @cmd = 'copy ' + @fulllocalpath + ' \\WILMA\F$\LSBackup'
EXEC master..xp_cmdshell @cmd , NO_OUTPUT
WAITFOR DELAY '00:00:05'


DECLARE @sourcefilenameout nvarchar(128)
SELECT @sourcefilenameout =  @filename
EXEC WILMA.master.dbo.ls_userlist_backup_tlog_restore @sourcefilename = @sourcefilenameout






STANDBY SERVER - > @@servername = WILMA


ALTER     PROCEDURE ls_userlist_backup_restore (@sourcefilename nvarchar(128))

AS SET NOCOUNT ON

DECLARE @fulllocalpath nvarchar(128)
SELECT @fulllocalpath = 'F:\LSBackup\' + @sourcefilename

RESTORE DATABASE userlist 
FROM DISK = @fulllocalpath
WITH
DBO_ONLY,
REPLACE,
STANDBY = 'F:\LSBackup\undo_userlist_logship.ldf',
MOVE 'UserList_Data' TO 'F:\MSSQL7\data\UserList_data.mdf', 
MOVE 'UserList_Log' TO 'D:\SQL-Logs\UserList_log.ldf'

WAITFOR DELAY '00:00:05'

EXEC sp_dboption 'userlist', 'single user', true


ALTER  PROCEDURE ls_userlist_backup_tlog_restore (@sourcefilename nvarchar(128)) 

AS SET NOCOUNT ON 

DECLARE @fulllocalpath nvarchar(128)
SELECT @fulllocalpath = 'F:\LSBackup\' + @sourcefilename

RESTORE LOG userlist
FROM DISK = @fulllocalpath
WITH 
DBO_ONLY,
STANDBY = 'F:\LSBackup\undo_userlist_logship.ldf'

WAITFOR DELAY '00:00:05'

EXEC sp_dboption 'userlist', 'single user', true


CREATE PROCEDURE ls_userlist_recovery AS

RESTORE DATABASE userlist WITH RECOVERY
EXEC SP_DBOPTION 'userlist', 'read only', 'false'
EXEC SP_DBOPTION 'userlist', 'dbo use only', 'false'