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