DanBrother的部落格

The Following Stored Procedure demonstrates a way to generate a batch file that transfers daily local db backups (db backup & transaction log backups) to a remote site:

-- Transfer db backup
CREATE PROCEDURE sp_transfer_db_backup AS
SET NOCOUNT ON
DECLARE @Z VARCHAR(100)
DECLARE @SQL VARCHAR(7000)
DECLARE @DBName VARCHAR(100)
DECLARE @BkpFileName NVARCHAR(260)
DECLARE @RemoteDir NVARCHAR(100)
DECLARE @RowCnt INT
DECLARE @COPYTEXT VARCHAR(7000)

SELECT bs.database_name AS DatabaseName, MAX(bms.physical_device_name) AS FullBackupName,' z:\user_db\testdb\' AS RemoteDir
INTO #Backups
FROM msdb.dbo.backupset bs
INNER JOIN msdb.dbo.backupmediafamily bms
ON bs.media_set_id = bms.media_set_id
INNER JOIN master.dbo.sysdatabases s
ON bs.database_name = s.name
WHERE CONVERT(VARCHAR(20), bs.backup_finish_date, 101) = CONVERT(VARCHAR(20), GETDATE(), 101) AND
s.name in ('testdb') AND bs.type = 'D'
GROUP BY bs.database_name

SET @RowCnt = @@ROWCOUNT

EXEC master.dbo.xp_cmdshell 'del c:\db_backup_transfer.bat'

WHILE @RowCnt <> 0
BEGIN
SELECT TOP 1 @DBName = DatabaseName, @BkpFileName = FullBackupName, @RemoteDir = RemoteDir
FROM #Backups
ORDER BY DatabaseName

SET @RowCnt = @@ROWCOUNT

IF @RowCnt <> 0
BEGIN

SELECT @SQL = 'xcopy /Y /S /E "' + @BkpFileName + '"' + @RemoteDir

SELECT @COPYTEXT = 'echo ' + @SQL + '>> c:\db_backup_transfer.bat'
EXEC master.dbo.xp_cmdshell @COPYTEXT

DELETE FROM #Backups
WHERE DatabaseName = @DBName and FullBackupName = @BkpFileName
END

END

DROP TABLE #Backups

SET NOCOUNT OFF
GO

C:\db_backup_transfer.bat would contain text similar to the followings:
xcopy /Y /S /E "D:\BackUp\user_db\testdb\testdb_db_201204200530.BAK" z:\user_db\testdb\

-----------------------------------------------------------------------------------------------------------------------------

-- Transfer latest transaction log backup
CREATE PROCEDURE sp_transfer_transaction_backup AS
SET NOCOUNT ON
DECLARE @Z VARCHAR(100)
DECLARE @SQL VARCHAR(7000)
DECLARE @DBName VARCHAR(100)
DECLARE @BkpFileName NVARCHAR(260)
DECLARE @RemoteDir NVARCHAR(100)
DECLARE @RowCnt INT
DECLARE @COPYTEXT VARCHAR(7000)

SELECT TOP 1 bs.database_name AS DatabaseName, bms.physical_device_name AS FullBackupName,' z:\transaction_backup\testdb\' AS RemoteDir
INTO #Backups
FROM msdb.dbo.backupset bs
INNER JOIN msdb.dbo.backupmediafamily bms
ON bs.media_set_id = bms.media_set_id
INNER JOIN master.dbo.sysdatabases s
ON bs.database_name = s.name
WHERE CONVERT(VARCHAR(20), bs.backup_finish_date, 101) = CONVERT(VARCHAR(20), GETDATE(), 101) AND
s.name in ('testdb') AND bs.type = 'L'
ORDER BY bs.backup_finish_date DESC

SET @RowCnt = @@ROWCOUNT

EXEC master.dbo.xp_cmdshell 'del c:\transaction_backup_transfer.bat'

WHILE @RowCnt <> 0
BEGIN
SELECT TOP 1 @DBName = DatabaseName, @BkpFileName = FullBackupName, @RemoteDir = RemoteDir
FROM #Backups
ORDER BY DatabaseName

SET @RowCnt = @@ROWCOUNT

IF @RowCnt <> 0
BEGIN

SELECT @SQL = 'xcopy /Y /S /E "' + @BkpFileName + '"' + @RemoteDir

SELECT @COPYTEXT = 'echo ' + @SQL + '>> c:\transaction_backup_transfer.bat'
EXEC master.dbo.xp_cmdshell @COPYTEXT

DELETE FROM #Backups
WHERE DatabaseName = @DBName and FullBackupName = @BkpFileName
END

END

DROP TABLE #Backups

SET NOCOUNT OFF
GO

exec sp_transfer_transaction_backup
GO

C:\transaction_backup_transfer.bat would contain text similar to the followings:
xcopy /Y /S /E "D:\BackUp\transaction_backup\testdb\testdb_tlog_201204201200.TRN" z:\transaction_backup\testdb\

---------------------------------------------------------------------------------------------------------
Following the same methd, system db transfer procedure can be created as follows:

CREATE PROCEDURE sp_transfer_system_db_backup AS
SET NOCOUNT ON
DECLARE @Z VARCHAR(100)
DECLARE @SQL VARCHAR(7000)
DECLARE @DBName VARCHAR(100)
DECLARE @BkpFileName NVARCHAR(260)
DECLARE @RemoteDir NVARCHAR(100)
DECLARE @RowCnt INT
DECLARE @COPYTEXT VARCHAR(7000)

SELECT bs.database_name AS DatabaseName, MAX(bms.physical_device_name) AS FullBackupName,' z:\system_db\' AS RemoteDir
INTO #Backups
FROM msdb.dbo.backupset bs
INNER JOIN msdb.dbo.backupmediafamily bms
ON bs.media_set_id = bms.media_set_id
INNER JOIN master.dbo.sysdatabases s
ON bs.database_name = s.name
WHERE CONVERT(VARCHAR(20), bs.backup_finish_date, 101) = CONVERT(VARCHAR(20), GETDATE(), 101) AND
s.name in ('master','model','msdb') AND bs.type = 'D'
GROUP BY bs.database_name

SET @RowCnt = @@ROWCOUNT

EXEC master.dbo.xp_cmdshell 'del c:\system_db_backup_transfer.bat'

WHILE @RowCnt <> 0
BEGIN
SELECT TOP 1 @DBName = DatabaseName, @BkpFileName = FullBackupName, @RemoteDir = RemoteDir
FROM #Backups
ORDER BY DatabaseName

SET @RowCnt = @@ROWCOUNT

IF @RowCnt <> 0
BEGIN

SELECT @SQL = 'xcopy /Y /S /E "' + @BkpFileName + '"' + @RemoteDir

SELECT @COPYTEXT = 'echo ' + @SQL + '>> c:\system_db_backup_transfer.bat'
EXEC master.dbo.xp_cmdshell @COPYTEXT

DELETE FROM #Backups
WHERE DatabaseName = @DBName and FullBackupName = @BkpFileName
END

END

DROP TABLE #Backups

SET NOCOUNT OFF
GO