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

sql backup v5 does not move file on restore

lculleylculley Posts: 36
edited December 12, 2007 5:14AM in SQL Backup Previous Versions
I'm trying to copy the backup file to a destination but nothing seems to work. Here's code I've tried:

exec master..sqlbackup N'-SQL "RESTORE DATABASE [<database>]
FROM DISK = ''\\server\share\folder1\<database>\subfolder3\<database>\<database>_*.sqb''
WITH RECOVERY, MOVE ''<database>'' TO ''c:\Program Files\Microsoft SQL Server2000\MSSQL\Data\<database>.mdf'',
MOVE ''<database>_log'' TO ''c:\Program Files\Microsoft SQL Server2000\MSSQL\Data\<database>_log.LDF",
COPYTO="\\server\share\folder1\<database>\",FILEOPTIONS=2, erasefiles=1hr"',@exitcode OUT,@sqlerrorcode OUT

exec master..sqlbackup N'-SQL "RESTORE DATABASE [<database>]
FROM DISK = ''\\server\share\folder1\<database>\subfolder3\<database>\<database>_*.sqb''
WITH RECOVERY, MOVE ''<database>'' TO ''c:\Program Files\Microsoft SQL Server2000\MSSQL\Data\<database>.mdf'',
MOVE ''<database>_log'' TO ''c:\Program Files\Microsoft SQL Server2000\MSSQL\Data\<database>_log.LDF",
COPYTO="\\server\share\folder1\<database>\",FILEOPTIONS=7, erasefiles=1hr"',@exitcode OUT,@sqlerrorcode OUT

exec master..sqlbackup N'-SQL "RESTORE DATABASE [<database>]
FROM DISK = ''\\server\share\folder1\<database>\subfolder3\<database>\<database>_*.sqb''
WITH RECOVERY, MOVE ''<database>'' TO ''c:\Program Files\Microsoft SQL Server2000\MSSQL\Data\<database>.mdf'',
MOVE ''<database>_log'' TO ''c:\Program Files\Microsoft SQL Server2000\MSSQL\Data\<database>_log.LDF",
COPYTO="\\server\share\folder1\<database>\",FILEOPTIONS=1, erasefiles=1hr"',@exitcode OUT,@sqlerrorcode OUT

exec master..sqlbackup N'-SQL "RESTORE DATABASE [<database>]
FROM DISK = ''\\server\share\folder1\<database>\subfolder3\<database>\<database>_*.sqb''
WITH RECOVERY, MOVE ''<database>'' TO ''c:\Program Files\Microsoft SQL Server2000\MSSQL\Data\<database>.mdf'',
MOVE ''<database>_log'' TO ''c:\Program Files\Microsoft SQL Server2000\MSSQL\Data\<database>_log.LDF",
MOVETO="\\server\share\folder1\<database>\""'

exec master..sqlbackup N'-SQL "RESTORE DATABASE [<database>]
FROM DISK = ''\\server\share\folder1\<database>\subfolder3\<database>\<database>_*.sqb''
WITH RECOVERY, MOVE ''<database>'' TO ''c:\Program Files\Microsoft SQL Server2000\MSSQL\Data\<database>.mdf'',
MOVE ''<database>_log'' TO ''c:\Program Files\Microsoft SQL Server2000\MSSQL\Data\<database>_log.LDF",
COPYTO="\\server\share\folder1\<database>\""'
I've also tried removing the archive bit on the from disk file but that doesn't help either.

What am I missing here? There are no errors in the log but it does not list that the files have been moved or deleted.

Thanks,
Laura

Comments

  • Options
    peteypetey Posts: 2,358 New member
    COPYTO is not supported for restores. You can only move a file to another folder after a successful restore, using the MOVETO option.

    You should have seen the following message when you tried to run your restore commands:

    Syntax error: 'COPYTO' after ','

    However, this was not displayed because the command was not correctly formed. The single quotes and double quotes sort of got tangled up, resulting in SQL Backup totally ignoring the COPYTO option. I would suggest using square brackets to delimit SQL Backup options to improve clarity. Its non-standard, but is easier to sort out. E.g. for the first restore commad, you can use the following:
    exec master..sqlbackup N'-SQL "RESTORE DATABASE &#91;&lt;database&gt;&#93; 
    FROM DISK = &#91;\\server\share\folder1\&lt;database&gt;\subfolder3\&lt;database&gt;\&lt;database&gt;_*.sqb&#93; 
    WITH RECOVERY, MOVE &#91;&lt;database&gt;&#93; TO &#91;c:\Program Files\Microsoft SQL Server2000\MSSQL\Data\&lt;database&gt;.mdf&#93;, 
    MOVE &#91;&lt;database&gt;_log&#93; TO &#91;c:\Program Files\Microsoft SQL Server2000\MSSQL\Data\&lt;database&gt;_log.LDF&#93;, 
    FILEOPTIONS=2, ERASEFILES = 1h"'
    
    Also note that ERASEFILES recognises 'h' as the hour indicator, not 'hr'.
    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.