counting to 10 not working - restore with move error

dterrie · March 26, 2008 2:03AM

Be thankful you are a ocean and a continent away. I was asked to restore a db to a new test server on short notice tonight, and SQL Backup turned a simple job into a nightmare. Your UI allows me to set the target drives for the files in my database, but the app errors out when you try to use them. I believe I asked about this previously, and was given the unbelievable answer that the inability to handle file moves was intentional, but that the feature might be added at some point. I'm just speechless you have not fixed this. A less sophisticated user can simply not change any file paths, and go on to the next step, but if you offer the option in the UI, you are oblidged to follow through in the execution. Someone should be fired for not knowing the meaning of the word automation.

In the meantime, can someone translate the information below into a T-SQL statement that works properly with your

master..sqlbackup sproc?

Here's a dump of the error:

This operation failed with errors.

Restoring XXX(database) on SQL_SE instance from:
h:\backup\database\XXX.sqb
SQL Server error
SQL error 3013: SQL error 3013: RESTORE DATABASE is terminating abnormally.
SQL error 3119: SQL error 3119: Problems were identified while planning for the RESTORE statement. Previous messages provide details.
SQL error 3156: SQL error 3156: File 'XXX_Log' cannot be restored to 'H:\FileGroups\Logs\XXX_Log.ldf'. Use WITH MOVE to identify a valid location for the file.
SQL error 3634: SQL error 3634: The operating system returned the error '32(The process cannot access the file because it is being used by another process.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'H:\FileGroups\Logs\XXX_
Log.ldf'.
SQL error 3156: SQL error 3156: File XXX_Indexes' cannot be restored to 'G:\FileGroups\Indexes\XXX_Indexes.ndf'. Use WITH MOVE to identify a valid location for the file.
SQL error 3634: SQL error 3634: The operating system returned the error '32(The process cannot access the file because it is being used by another process.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'G:\FileGroups\Indexes\XXX_Indexes.ndf'.
SQL error 3156: SQL error 3156: File 'XXX_Data' cannot be restored to 'F:\FileGroups\Data\XXX_Data.ndf'. Use WITH MOVE to identify a valid location for the file.
SQL error 3634: SQL error 3634: The operating system returned the error '32(The process cannot access the file because it is being used by another process.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'F:\FileGroups\Data\XXX_
Data.ndf'.
SQL error 3156: SQL error 3156: File 'XXX' cannot be restored to 'F:\FileGroups\Data\XXX.mdf'. Use WITH MOVE to identify a valid location for the file.
SQL error 3634: SQL error 3634: The operating system returned the error '32(The process cannot access the file because it is being used by another process.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'F:\FileGroups\Data\XXX.
mdf'.
SQL Backup exit code: 1100
SQL error code: 3634

petey · March 26, 2008 2:55AM

It appears you need to use the MOVE options to relocate the data and log files to valid locations on the new server e.g.

EXEC master..sqlbackup '-sql "RESTORE DATABASE XXX FROM DISK = &#91;h:\backup\database\XXX.sqb&#93; WITH 
MOVE &#91;XXX&#93; TO &#91;&lt;new location of XXX.mdf&gt;&#93;, 
MOVE &#91;XXX_Data&#93; TO &#91;&lt;new location of XXX_Data.ndf&gt;&#93;, 
MOVE &#91;XXX_Indexes&#93; TO &#91;&lt;new location of XXX_Indexes.ndf&gt;&#93;, 
MOVE &#91;XXX_Log&#93; TO &#91;&lt;new location of XXX_log.ldf&gt;&#93;" '

dterrie · March 26, 2008 4:04AM

RE the MOVE clauses, I'm not actually trying to change any file paths. Both servers are identical - so I'm just tying to restore the database backed up from server A onto server B with all files residing in the same drives/folders.

Based on your reply, I tried this (actual DB name and file names):

EXEC master..sqlbackup '-sql "RESTORE DATABASE California FROM DISK = [h:\backup\database\California.sqb] WITH
MOVE [California.mdf] TO [F:\FileGroups\Data],
MOVE [California_Data.ndf] TO [F:\FileGroups\Data],
MOVE [California_Indexes.ndf] TO [G:\FileGroups\Indexes],
MOVE [California_Log.ldf] TO [H:\FileGroups\Logs]"'

And get this error:

SQL error 3013: SQL error 3013: RESTORE DATABASE is terminating abnormally.
SQL error 3234: SQL error 3234: Logical file 'California.mdf' is not part of database 'California'. Use RESTORE FILELISTONLY to list the logical file names.

If I remove the file name extensions, like this:

EXEC master..sqlbackup '-sql "RESTORE DATABASE California FROM DISK = [h:\backup\database\California.sqb] WITH
MOVE [California] TO [F:\FileGroups\Data],
MOVE [California_Data] TO [F:\FileGroups\Data],
MOVE [California_Indexes] TO [G:\FileGroups\Indexes],
MOVE [California_Log] TO [H:\FileGroups\Logs]"'

I get the same error:

SQL error 3234: SQL error 3234: Logical file 'California_Data' is not part of database 'California'. Use RESTORE FILELISTONLY to list the logical file names.

Clearly, I'm missing something here...

petey · March 26, 2008 4:17AM

Do this:

EXEC master..sqlbackup '-sql "RESTORE FILELISTONLY FROM DISK = &#91;h:\backup\database\California.sqb&#93;" '

If the folders in the PhysicalName values are valid and existing folders, you should be able to restore the database without having to use the MOVE options e.g.

EXEC master..sqlbackup '-sql "RESTORE DATABASE California FROM DISK = &#91;h:\backup\database\California.sqb&#93;" '

The MOVE syntax requires the LogicalName value in the first parameter, followed by the new path/file name e.g.
RESTORE ... WITH MOVE [<LogicalName>] TO [<new file name>]

and is used only when the database is to be restored to a path or file name that is different from the original database.

dterrie · March 26, 2008 4:44AM

Ok, I did find an ealier thread - thought I had a workaround before - which had me leave file names in place and then copy/alter the script from the script tab. This, below, works:

EXECUTE master..sqlbackup N'-SQL "RESTORE DATABASE [CaliforniaSE]
FROM DISK = ''h:\backup\database\california.sqb'' WITH RECOVERY,
MOVE ''California'' TO ''F:\FileGroups\Data\CaliforniaSE.mdf'',
MOVE ''California _Data'' TO ''F:\FileGroups\Data\California_DataSE.ndf'',
MOVE ''California_Indexes'' TO ''G:\FileGroups\Indexes\California_IndexesSE.ndf'',
MOVE ''California_Log'' TO ''H:\FileGroups\Logs\California_LogSE.ldf''"'

6:33 for a 20GB database.

Your second option below, works as well.

EXEC master..sqlbackup '-sql "RESTORE DATABASE California FROM DISK = [h:\backup\database\California.sqb]" '

All of which begs the question as to why you don't fix the product to do this itself. Thanks for your help. Late here. Nite.

counting to 10 not working - restore with move error

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk