December 10, 2008 at 1:10 pm
I'm looking for a script that will grab the latest backup file and then copy that file to another location on my network. The backups are taken using a maintenance plan in SQL Server 2005 Enterprise Edition (9.0.3257)
For example the file naming convention is like: DBNAME_backup_200812092300.bak
_______________________________________________
So... As I was writing my question I was able to come up with a solution just by digging around the MSDB Database and dissecting a similar script to copy sql 2000 backup files. Please let me know if you like this method or know of a better method to copy backup files that get created with a maintenance plan.
SET NOCOUNT ON
DECLARE @Command varchar(1000)
DECLARE @BackupFile nvarchar(1000)
SET @BackupFile= (
SELECT TOP 1 (physical_device_name)
FROM dbo.backupmediafamily
WHERE physical_device_name like '%YOURDBNAME_backup%' and physical_device_name like '%.bak%'
ORDER BY media_set_id DESC
)
--print @BackupFile
SELECT @BackupFile
SELECT @Command = ' COPY ' + @BackupFile+ ' \\Your\Network\Location\'
EXEC master..xp_cmdshell @Command
December 10, 2008 at 1:37 pm
You could always backup directly to the remote location, but I wouldn't recommend it.
So far as I can tell you have the best method for taking care of it. The only thing I can think of is to download and use Robocopy (freely available from Microsoft), this can handle potential issues related to network problems and such.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply