CREATE         PROCEDURE ls_backup

AS SET NOCOUNT ON

CREATE TABLE #dblist (IDxxx int identity(1,1), dbname varchar(255))

INSERT #dblist (dbname)
SELECT dbname FROM ls

DECLARE @ID int, @MAXID int
DECLARE @thisdbname varchar(255)

SELECT @MAXID = MAX(IDxxx), @ID = 0 FROM #dblist

WHILE @ID < @MAXID
BEGIN
SELECT @ID = MIN(IDxxx) FROM #dblist WHERE IDxxx>@ID

	SELECT @thisdbname = dbname FROM #dblist WHERE IDxxx= @ID

	DECLARE @localdirectoryname varchar(255)
	DECLARE @filename varchar(255)
	DECLARE @fulllocalpath varchar(255)

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

	BACKUP DATABASE @thisdbname TO DISK = @fulllocalpath WITH RETAINDAYS = 2, INIT
	WAITFOR DELAY '00:00:05'

 	DECLARE @cmd varchar(255)
 	SET @cmd = 'copy ' + @fulllocalpath + ' \\linkedSTANDBYSERVER\F$\MSSQL\BACKUP\LSBackup\' + @thisdbname
 	EXEC master..xp_cmdshell @cmd , NO_OUTPUT
 	WAITFOR DELAY '00:00:05'
 
 	DECLARE @sourcefilenameout varchar(255)
 	SELECT @sourcefilenameout =  @filename
 	EXEC linkedSTANDBYSERVER.abdba.dbo.ls_backup_restore @dbnamels = @thisdbname, @sourcefilename = @sourcefilenameout

	INSERT ls_files (lsfilename,createdate) VALUES (@fulllocalpath, GETDATE())

END

DROP TABLE #dblist

CREATE       PROCEDURE ls_backup_restore (@dbnamels varchar(255), @sourcefilename varchar(255)) AS

DECLARE @fulllocalpath varchar(255)

SELECT @fulllocalpath = 'F:\MSSQL\BACKUP\LSBackup\' + @dbnamels + '\' + @sourcefilename

DECLARE @a varchar(255)
DECLARE @b varchar(255)
DECLARE @c varchar(255)
DECLARE @d varchar(255)
DECLARE @e varchar(255)


SET @a = 'F:\MSSQL\BACKUP\LSBackup\' + @dbnamels +'\undo_'+ @dbnamels +'_logship.ldf'
SET @b = @dbnamels+'_Data'
SET @c = 'F:\MSSQL\Data\'+ @dbnamels +'_data.mdf'
SET @d = @dbnamels+'_Log'
SET @e = 'D:\SQL-Logs\'+ @dbnamels +'_log.ldf'

RESTORE DATABASE @dbnamels
FROM DISK = @fulllocalpath
WITH
DBO_ONLY,
REPLACE,
STANDBY = @a, 

MOVE @b TO @c, 
MOVE @d TO @e

INSERT ls_files (lsfilename,createdate) VALUES (@fulllocalpath, GETDATE())

WAITFOR DELAY '00:00:05'




CREATE   PROCEDURE ls_backup_tlog_restore (@dbnamels varchar(255), @sourcefilename varchar(255)) AS 

DECLARE @fulllocalpath varchar(255)
SELECT @fulllocalpath = 'F:\MSSQL\BACKUP\LSBackup\' + @dbnamels + '\' + @sourcefilename

DECLARE @a varchar(255)

SET @a = 'F:\MSSQL\BACKUP\LSBackup\' + @dbnamels +'\undo_' + @dbnamels + '_logship.ldf'


RESTORE LOG @dbnamels
FROM DISK = @fulllocalpath
WITH 
DBO_ONLY,
STANDBY = @a


INSERT ls_files (lsfilename,createdate) VALUES (@fulllocalpath, GETDATE())

WAITFOR DELAY '00:00:05'



CREATE    PROCEDURE ls_delete_old_backup_files (@timeoffset int = -20)

-- SELECT * FROM ls_files

AS SET NOCOUNT ON

DECLARE @backdate smalldatetime
SET @backdate = DATEADD(mi,@timeoffset,GETDATE())

DECLARE @ID int, @MAXID int
DECLARE @thisfilename varchar(255)

SELECT @MAXID = MAX(IDxxx), @ID = 0 FROM ls_files WHERE createdate < @backdate

WHILE @ID < @MAXID
BEGIN
SELECT @ID = MIN(IDxxx) FROM ls_files WHERE IDxxx>@ID AND createdate < @backdate

	SELECT @thisfilename = lsfilename FROM ls_files WHERE IDxxx= @ID

	DECLARE @cmd varchar(255)
 	SET @cmd = 'del ' + @thisfilename
	EXEC master..xp_cmdshell @cmd , NO_OUTPUT

END

DELETE FROM ls_files WHERE createdate < @backdate

quote:

---

RECOVERY
Instructs the restore operation to roll back any uncommitted transactions. After the recovery process, the database is ready for use.
If subsequent RESTORE operations (RESTORE LOG, or RESTORE DATABASE from differential) are planned, NORECOVERY or STANDBY should be specified instead.
If neither NORECOVERY, RECOVERY, or STANDBY is specified, RECOVERY is the default. When restoring backup sets from an earlier version of SQL Server, a database upgrade may be required. This upgrade is performed automatically when WITH RECOVERY is specified. For more information, see Transaction Log Backups .

STANDBY = undo_file_name

Specifies the undo file name so the recovery effects can be undone. The size required for the undo file depends on the volume of undo actions resulting from uncommitted transactions. If neither NORECOVERY, RECOVERY, or STANDBY is specified, RECOVERY is the default.
STANDBY allows a database to be brought up for read-only access between transaction log restores and can be used with either warm standby server situations or special recovery situations in which it is useful to inspect the database between log restores.
If the specified undo file name does not exist, SQL Server creates it. If the file does exist, SQL Server overwrites it.

---