sql backup v5 does not move file on restore

lculley · December 11, 2007 3:21PM

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

petey · December 12, 2007 5:14AM

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'.

sql backup v5 does not move file on restore

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk