how to copy backup files to fileshare?

• ggoble

  Hall of Fame

  Points: 3337

  More actions

  December 10, 2008 at 1:10 pm

  #194866

  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

• Nicholas Cain

  SSC-Dedicated

  Points: 33009

  More actions

  December 10, 2008 at 1:37 pm

  #910647

  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.

  ---

  
  Shamless self promotion - read my blog http://sirsql.net