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

      END

END

DROP TABLE #Backups

SET NOCOUNT OFF