WITH COPYTO function

oberion

Just a quick question for the sqlbackup WITH COPYTO function. I have some backup jobs setup via the wizard. The wizard generated the following set of code:

DECLARE @exitcode int
DECLARE @sqlerrorcode int
exec master..sqlbackup N'-SQL "BACKUP DATABASES EXCLUDE [master, msdb, model]  TO DISK = ''E:\Backups\env\usr_db_full\dev02\<AUTO>'' WITH NAME = ''<AUTO>'', DESCRIPTION = ''<AUTO>'', PASSWORD = ''<ENCRYPTEDPASSWORD>REMOVED</ENCRYPTEDPASSWORD>'', KEYSIZE = 256, ERASEFILES = 2, COMPRESSION = 3, THREADS = 1"', @exitcode OUTPUT, @sqlerrorcode OUTPUT
IF (@exitcode <>0) OR (@sqlerrorcode <> 0)
BEGIN
  RAISERROR ('SQL Backup job failed with exitcode: %d  SQL error code: %d', 16, 1, @exitcode, @sqlerrorcode)
END

Would I modify that code to look like this?

DECLARE @exitcode int
DECLARE @sqlerrorcode int
exec master..sqlbackup N'-SQL "BACKUP DATABASES EXCLUDE [master, msdb, model]  TO DISK = ''E:\Backups\env\usr_db_full\dev02\<AUTO>'' WITH NAME = ''<AUTO>'', DESCRIPTION = ''<AUTO>'', COPYTO = ''\\server\to\copy\to'', PASSWORD = ''<ENCRYPTEDPASSWORD>REMOVED</ENCRYPTEDPASSWORD>'', KEYSIZE = 256, ERASEFILES = 2, COMPRESSION = 3, THREADS = 1"', @exitcode OUTPUT, @sqlerrorcode OUTPUT
IF (@exitcode <>0) OR (@sqlerrorcode <> 0)
BEGIN
  RAISERROR ('SQL Backup job failed with exitcode: %d  SQL error code: %d', 16, 1, @exitcode, @sqlerrorcode)
END

Pretty lame question, I know but I tried it in version 3.x and it didn't work right.

thanks.

petey

Yes, that would be the command to use. In v3, you had to ensure that the SQL Server service startup account had write rights to the COPYTO folders. In v4, you have to ensure that the SQL Backup Agent service startup account has these rights.