What are the challenges you face when working across database platforms? Take the survey
Options

WITH COPYTO function

oberionoberion Posts: 41
edited January 24, 2006 6:40PM in SQL Backup Previous Versions
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.

Comments

  • Options
    peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
Sign In or Register to comment.