CODEWITHSUNDEEP
sql-servercopysql-server-agent
John Pasquet
John Pasquet

Reputation: 1842

How to copy a database backup file to another server in SQL Server Agent job

I realize I'm probably missing something simple, but I can't figure it out.

If I use the following "copy" command in a batch file on the destination server, it works fine. However, if I add it in a step on the SQL Server Agent job that does the backup, I get a syntax error. So, what am I doing wrong? It seems like it might be getting stuck on the "/" on the "/y" parameter, but I do want it to overwrite the existing file.

copy /y "\\DBServer\c$\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\db_daily.bak" "\\DESTINATION\\db\db_daily.bak"

The error is:

Incorrect syntax near '/'.  [SQLSTATE 42000] (Error 102).  The step failed.

I removed the /y altogether and tried everything I can think of. Any help would be much appreciated.

Upvotes: 0

Views: 6229

Answers (1)

dbamex
dbamex

Reputation: 211

Make sure your step was created as Type "Operating System (Cmd Exec) not as transact sql.

Or use the XP xp_cmdshell 

   exec xp_cmdshell 'copy /y "\\DBServer\c$\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\db_daily.bak" "\\DESTINATION\\db\db_daily.bak"'

Please note you may need to enable the xp_cmdshell.

Upvotes: 2

Related Questions