Urgent Help Need for DB Restoration

Question

Urgent Help Need for DB Restoration

ramana3327

SSCoach

Points: 19700
More actions
September 23, 2016 at 8:09 am

#313452

Hello Experts,
I am facing issues with database restoration using different server backup.
We have SQL 2005 instance. I took backup of the database (Nearly 1 TB) using Litespeed.
The command I used to backup
execute master.dbo.xp_backup_database
@database = 'TestDB',
@filename = '\\NetworkPath\TestDB_9_22_2016.bak',
@init = 1,
@compressionlevel = 5
I tried to restore in SQL 2008 instance. The paths are different so I used with move option
exec master.dbo.xp_restore_database
@database = 'TestDB',
@filename = '\\NetworkPath\TestDB_9_22_2016.bak',
@with = 'replace',
@with = 'move "Logicalfilename" to "F:\Data\TestDB.mdf"',
@with = 'move "LogicalFilenameofLog" to "F:\Logs\TestDB.ldf"'
I am receiving the following error
Msg -2147417259, Level 16, State 1, Line 0
Failed to create destination folder
When I execute the command verify
EXEC master.dbo.xp_restore_verifyonly
@filename = '\\Path'
The backup set on file 1 is valid.
Directory lookup for the file "D:\DATABASES\TestDB_1.LDF" failed with the operating system error 2(The system cannot find the file specified.).
The path specified by "D:\DATABASES\TestDB.mdf" is not in a valid directory.
Attempting to restore this backup may encounter storage space problems. Subsequent messages will provide details.
I need help to resolve this issue and restore the DB

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 1

ramana3327 (9/23/2016)
Hello Experts,
I am facing issues with database restoration using different server backup.
We have SQL 2005 instance. I took backup of the database (Nearly 1 TB) using Litespeed.
The command I used to backup
execute master.dbo.xp_backup_database
@database = 'TestDB',
@filename = '\\NetworkPath\TestDB_9_22_2016.bak',
@init = 1,
@compressionlevel = 5
I tried to restore in SQL 2008 instance. The paths are different so I used with move option
exec master.dbo.xp_restore_database
@database = 'TestDB',
@filename = '\\NetworkPath\TestDB_9_22_2016.bak',
@with = 'replace',
@with = 'move "Logicalfilename" to "F:\Data\TestDB.mdf"',
@with = 'move "LogicalFilenameofLog" to "F:\Logs\TestDB.ldf"'
I am receiving the following error
Msg -2147417259, Level 16, State 1, Line 0
Failed to create destination folder
When I execute the command verify
EXEC master.dbo.xp_restore_verifyonly
@filename = '\\Path'
The backup set on file 1 is valid.
Directory lookup for the file "D:\DATABASES\TestDB_1.LDF" failed with the operating system error 2(The system cannot find the file specified.).
The path specified by "D:\DATABASES\TestDB.mdf" is not in a valid directory.
Attempting to restore this backup may encounter storage space problems. Subsequent messages will provide details.
I need help to resolve this issue and restore the DB

Quick thought, the @with additional parameters should be all in one line, something along this line:

😎

@with = 'replace ,move "Logicalfilename" to "F:\Data\TestDB.mdf" ,move LogicalFilenameofLog" to "F:\Logs\TestDB.ldf"'

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Eirikur Eiriksson (9/23/2016)
the @with additional parameters should be all in one line, something along this line:
@with = 'replace ,move "Logicalfilename" to "F:\Data\TestDB.mdf" ,move LogicalFilenameofLog" to "F:\Logs\TestDB.ldf"'

Restore is whitespace agnostic, the WITH can be on multiple lines, I usually put one option per line for readability

One thing that does look off is that you've got double quotes around the logical file names and physical paths. That should probably be two single quotes to make an escaped single quote.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 3

GilaMonster (9/23/2016)
Eirikur Eiriksson (9/23/2016)
the @with additional parameters should be all in one line, something along this line:
@with = 'replace ,move "Logicalfilename" to "F:\Data\TestDB.mdf" ,move LogicalFilenameofLog" to "F:\Logs\TestDB.ldf"'
Restore is whitespace agnostic, the WITH can be on multiple lines, I usually put one option per line for readability
One thing that does look off is that you've got double quotes around the logical file names and physical paths. That should probably be two single quotes to make an escaped single quote.

Found this example:

😎

EXEC master.dbo.xp_restore_database @database = 'MyDB'

, @filename = 'C:\MSSQL\Backup\MyDB_Backup.BAK'

, @with = 'MOVE ''MyDB_Data'' TO ''C:\MSSQL\Data\MyDB_data.MDF'''

, @with = 'MOVE ''MyDB_Data2'' TO ''C:\MSSQL\Data\MyDB_data2.NDF'''

, @with = 'MOVE ''MyDB_Log'' TO ''C:\MSSQL\Data\MyDB_log.LDF'''

Spot on again Gail.

ramana3327 SSCoach Points: 19700 More actions · Answer 4

Thank you.

I copied your syntax and restore started working fine.

Jeff Moden SSC Guru Points: 1003888 More actions · Answer 5

Now that you've solved your problem, prepare for the next problem by practicing how to do a restore until you can do it in your sleep. Having restore scripts that would require only paths would be the right thing to do.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)