0

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.

John Pasquet
  • 1,824
  • 15
  • 20
  • I'm not a batch expert, but I'm pretty sure you need to use [xp_cmdshell](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/xp-cmdshell-transact-sql?view=sql-server-2017). With that being said, I'd check our [dbatools](https://dbatools.io/) if you are into powershell integration with SQL Server. I'm also a fan of using Windows Task Scheduler and keeping batch and ps files external to SQL Server... just schedule those on the windows side. – S3S Nov 27 '18 at 17:49

1 Answers1

2

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.

dbamex
  • 201
  • 1
  • 5
  • 2
    You do not need to enable xp_cmdshell but your agent service must run under a security account that has permissions to access all the file shares. – Brian Pressler Nov 27 '18 at 17:56
  • You need to enable it, because it is disabled by default at the installation time. https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/xp-cmdshell-server-configuration-option?view=sql-server-2017 – dbamex Nov 27 '18 at 22:10
  • it is true that xp_commandshell is disabled by default, but it does not need to be enabled in order for Type "Operating System (Cmd Exec) job steps to run successfully. – Brian Pressler Nov 27 '18 at 22:36
  • 1
    It was never specified it was required for cmd exec jobs, It says: "Make sure your step was created as Type "Operating System (Cmd Exec) not as transact sql. **Or** use the XP xp_cmdshell" not sure where the confusion was. – dbamex Nov 27 '18 at 22:50
  • 1
    Sorry... I shouldn't skim read. You definitely do have to enable xp_cmdshell to exec xp_cmdshell :) – Brian Pressler Nov 27 '18 at 22:54