0

I have a seemingly easy question. How can I move a xxx.bak file to another location with T-sql?

I have a job that takes a copy from database a to c:mycopy/copy1/a.bak and I want to create a step that moves the file to c:mycopy/copy2/a.bak I tried:

 MOVE N'c:mycopy/copy1/a.bak'  TO N'c:mycopy/copy2/a.bak'

Sorry for the stupid question and thanks for your help

1
  • 1
    Why is the backup located in the wrong path in the first place? Could you perhaps rewrite the backup script to target the right location? Commented Feb 6, 2015 at 14:13

2 Answers 2

0

'(There's) no such thing as a stupid question' is a popular phrase that has had a long history. It suggests that the quest for knowledge includes failure, and that just because one person may know less than others they should not be afraid to ask rather than pretend they already know. :)

I am using below code to transfer full backups to another location using XCOPY:

similarly you can modify and transfer the file from required path to another server.

 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 datepart(yyyy,backup_start_date)=datepart(yyyy,getdate())
 and datepart(dd,backup_start_date)=datepart(dd,getdate())
 and datepart(mm,backup_start_date)=datepart(mm,getdate())
 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 +SPACE(1)+ '\\your\path\tobackup\'

     EXEC master.dbo.xp_cmdshell @SQL

    --print @sql

        DELETE FROM #Backups
        WHERE DatabaseName = @DBName

   END

   END

  DROP TABLE #Backups

 SET NOCOUNT OFF

Ps note: You have to make sure XP_Cmdshell is enabled for above to work!

0

You should not use T-SQL to do filesystem tasks like move files, copy files, etc.

Instead use PowerShell. SQL Server has support of PowerShell and you can schedule it using SQL Agent job as well.

You should look into Move-Item or Copy-Item cmdlets of PowerShell. There are plenty of scripts that will help you.

3
  • @ Kin -For few of our servers we have sql server 2005 on windows 2003 platform, i believe we cant use power shell there. So i am using below T-SQL. As you said Should not, what approach can we use in that case. Please correct me if i am wrong, so that i can also approach correctly for my above servers.
    – KASQLDBA
    Commented Feb 6, 2015 at 21:01
  • You dont need powershell installed on those machines - thats the beauty of PowerShell. You can invoke commands remotely using powershell. Just run it from Win 2008R2 or higher machine.
    – Kin Shah
    Commented Feb 6, 2015 at 21:12
  • Sure Kin, i will give a try for that. thnx
    – KASQLDBA
    Commented Feb 6, 2015 at 21:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.