Ramblings of a DBA

Tara Kizer
posts - 166, comments - 835, trackbacks - 75

My Links

Advertisement

News

Subscribe
Search this Blog

Archives

Post Categories

Work

T-SQL script to copy the last full backups of the user databases to a remote server

A question came up on SQLTeam that asked if anyone has a script to copy new backups to a remote server. Of course, you could do this in VBScript, but how would you know about backups that were put into a different directory than what your code is expecting?

Backup history information is stored in system tables in the msdb database. I posted code in the comment section of Mark's blog that displays the last backup (full or transaction log) of the user databases. Using that code, you can copy the files to a remote server using xcopy and xp_cmdshell. You don't even need to know where the backups are located as the msdb database has that information.

If you plan on using this code, I would suggest commenting out the xp_cmdshell line and adding PRINT @SQL before you run it.  If you want to copy over other backup files besides the full backups, then change the WHERE clause so that bs.type equals what type of file you want:

D = Database.
I = Database Differential.
L = Log.
F = File or Filegroup.

 

SET NOCOUNT ON

DECLARE @SQL VARCHAR(7000)
DECLARE @DBName SYSNAME
DECLARE @BkpFileName NVARCHAR(260)
DECLARE @RowCnt INT

SELECT bs.database_name AS DatabaseName, MAX(bms.physical_device_name) AS FullBackupName
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 NOT IN ('master', 'msdb', 'model', 'pubs', 'Northwind') AND
      bs.type = 'D'
GROUP BY bs.database_name


SET @RowCnt = @@ROWCOUNT

WHILE @RowCnt <> 0
BEGIN

      SELECT  TOP 1 @DBName = DatabaseName, @BkpFileName = FullBackupName
      FROM #Backups
      ORDER BY DatabaseName

      SET @RowCnt = @@ROWCOUNT

      IF @RowCnt <> 0
      BEGIN

            SELECT @SQL = 'xcopy ' + @BkpFileName + ' \\SomeServer\Someshare\'

            EXEC master.dbo.xp_cmdshell @SQL

            DELETE FROM #Backups
            WHERE DatabaseName = @DBName

      END

END

DROP TABLE #Backups

SET NOCOUNT OFF


Print | posted on Friday, April 02, 2004 2:41 PM | Filed Under [ SQL Server - Database Administration ]

Feedback

Gravatar

# re: T-SQL script to copy the last full backups of the user databases to a remote server

Great code, I'm going to have to save a link so that I can use this in the future.

However I do have a simple question, why not create another step in the backup job runs a batch file that just copies it instead of trying to do it all inside of SQL? Of course this would obviously require the batch file to know where the backup file is being stored which does indead add more coupling. So I guess I have answered my own question. However, I'm curious if you were thinking along those same lines or thinking about something different?
4/2/2004 3:54 PM | Aaron Weiker
Gravatar

# re: T-SQL script to copy the last full backups of the user databases to a remote server

I haven't yet incorporated this into our backup solution. We are currently using a variation of it. The one that we use does not loop. It just copies one file and has the database name hard coded in it (not flexible). We run the code several times to get all of the databases over, with the database name being different during each run. Also, the one that we use runs at a different time than the backup jobs do. We run it a couple of hours later. I think I will change the solution to use my version and also so that the backup job runs it as the next step in the job.
4/2/2004 4:29 PM | Tara
Gravatar

# Take Outs for 2 April 2004

Take Outs for 2 April 2004
4/3/2004 12:44 AM | Enjoy Every Sandwich
Gravatar

# Copying Most Recent SQL Server Backup to Remote Server

4/27/2004 9:02 AM | William.Blog()
Gravatar

# re: T-SQL script to copy the last full backups of the user databases to a remote server

Thank you for sharing your codes.
11/2/2005 7:16 AM | Christy
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET