Restore the database to new location

Question

Restore the database to new location

msalmaan

SSChasing Mays

Points: 621
More actions
April 1, 2009 at 7:33 am

#201681

hi to all
i want the database restore to a new location. how can i perform this task. i have a database backup with version 9.0.8042 and wants to restore on 9.0.3999
can it be possible to perform this task. If not what is the solution to restore this database.
can anyone help me please.....

Viewing 10 posts - 1 through 10 (of 10 total)

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

Lowell SSC Guru Points: 323546 More actions · Answer 1

among the same versions (ie 8.* to any other v8 or 9.* to any other v9) all backups are compatible with the same version..

so if i backup with SQL2005 SP3, i can restore on the same version, SQL2005, but with no service packs...it's just back and restore. no problem, since backups are a specific format, not tied to any version of applied service packs.

you only run into trouble if you backup a v 9 database and try to retore on a verison 8, for example.

are you having uissues, are just doubting yourself before proceeding?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

maechismo_8514 SSCoach Points: 17510 More actions · Answer 2

Yes, true as what Lowell advised and also you can restore v8 to v9 too. you should not face any problem in that.

Jerry Hung SSChampion Points: 12968 More actions · Answer 3

Assuming you have no compatiblity issue (you shouldn't)

RESTORE DATABASE [db]

FROM DISK = '.............'

WITH MOVE

'logicalDataName' TO 'D:\....',

'logicalLogName' TO 'L:\....'

,REPLACE -- if you want to replace current DB

,STATS -- show progress

SQLServerNewbieMCITP: Database Administrator SQL Server 2005

Steve Jones - SSC Editor SSC Guru Points: 738642 More actions · Answer 4

Keep in mind while the format between two builds of SS2K5 is the same, the functionality might not be the same for the app. Builds change the way things work, bugs get fixed, etc.

May or may not be an issue, but be aware.

msalmaan SSChasing Mays Points: 621 More actions · Answer 5

The above is my restore statement and it gets terminated abnormally..please help me

restore database JDE_PRODUCTION

from disk='\\baupsrv\d$\JDE_PROD\JDE_PROD_DB_FULL_BKUP'

with move

'JDE_PRODUCTION_Data' to 'C:\JDE_PRODUCTION\JDE_PRODUCTION_Data.MDF',

Move

'JDE_PRODUCTION_Data_1' to 'C:\JDE_PRODUCTION\JDE_PRODUCTION_Data_1.NDF',

Move

'JDE_PRODUCTION_Data_2' to 'C:\JDE_PRODUCTION\JDE_PRODUCTION_Data_2.NDF',

Move

'JDE_PRODUCTION_Data_3' to 'C:\JDE_PRODUCTION\JDE_PRODUCTION_Data_3.NDF',

Move

'JDE_PRODUCTION_Data_4' to 'C:\JDE_PRODUCTION\JDE_PRODUCTION_Data_4.NDF',

Move

'JDE_PRODUCTION_Data_5' to 'C:\JDE_PRODUCTION\JDE_PRODUCTION_Data_5.NDF',

Move

'JDE_PRODUCTION_Data_6' to 'C:\JDE_PRODUCTION\JDE_PRODUCTION_Data_6.NDF',

Move

'JDE_PRODUCTION_Data_7' to 'C:\JDE_PRODUCTION\JDE_PRODUCTION_Data_7.NDF',

Move

'JDE_PRODUCTION_Data_8' to 'C:\JDE_PRODUCTION\JDE_PRODUCTION_Data_8.NDF',

Move

'JDE_PRODUCTION_Data_9' to 'C:\JDE_PRODUCTION\JDE_PRODUCTION_Data_9.NDF',

Move

'JDE_PRODUCTION_Data_10' to 'C:\JDE_PRODUCTION\JDE_PRODUCTION_Data_10.NDF',

Move

'JDE_PRODUCTION_Log' to 'C:\JDE_PRODUCTION\JDE_PRODUCTION_Log.LDF',

Move

'JDE_PRODUCTION_Log_1' to 'C:\JDE_PRODUCTION\JDE_PRODUCTION_Log_1.LDF',

stats

THE RESULT STATEMENT IS AS FOLLOWS:

Msg 5184, Level 16, State 2, Line 1

Cannot use file 'C:\JDE_PRODUCTION\JDE_PRODUCTION_Data.MDF' for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used. Either the disk resource containing the file is not present in the cluster group or the cluster resource of the Sql Server does not have a dependency on it.

Msg 3156, Level 16, State 3, Line 1

File 'JDE_PRODUCTION_Data' cannot be restored to 'C:\JDE_PRODUCTION\JDE_PRODUCTION_Data.MDF'. Use WITH MOVE to identify a valid location for the file.

msalmaan SSChasing Mays Points: 621 More actions · Answer 6

The above is my restore statement and it gets terminated abnormally..please help me

restore database JDE_PRODUCTION

from disk='\\baupsrv\d$\JDE_PROD\JDE_PROD_DB_FULL_BKUP'

with move

'JDE_PRODUCTION_Data' to 'C:\JDE_PRODUCTION\JDE_PRODUCTION_Data.MDF',

Move

'JDE_PRODUCTION_Data_1' to 'C:\JDE_PRODUCTION\JDE_PRODUCTION_Data_1.NDF',

Move

'JDE_PRODUCTION_Data_2' to 'C:\JDE_PRODUCTION\JDE_PRODUCTION_Data_2.NDF',

Move

'JDE_PRODUCTION_Data_3' to 'C:\JDE_PRODUCTION\JDE_PRODUCTION_Data_3.NDF',

Move

'JDE_PRODUCTION_Data_4' to 'C:\JDE_PRODUCTION\JDE_PRODUCTION_Data_4.NDF',

Move

'JDE_PRODUCTION_Data_5' to 'C:\JDE_PRODUCTION\JDE_PRODUCTION_Data_5.NDF',

Move

'JDE_PRODUCTION_Data_6' to 'C:\JDE_PRODUCTION\JDE_PRODUCTION_Data_6.NDF',

Move

'JDE_PRODUCTION_Data_7' to 'C:\JDE_PRODUCTION\JDE_PRODUCTION_Data_7.NDF',

Move

'JDE_PRODUCTION_Data_8' to 'C:\JDE_PRODUCTION\JDE_PRODUCTION_Data_8.NDF',

Move

'JDE_PRODUCTION_Data_9' to 'C:\JDE_PRODUCTION\JDE_PRODUCTION_Data_9.NDF',

Move

'JDE_PRODUCTION_Data_10' to 'C:\JDE_PRODUCTION\JDE_PRODUCTION_Data_10.NDF',

Move

'JDE_PRODUCTION_Log' to 'C:\JDE_PRODUCTION\JDE_PRODUCTION_Log.LDF',

Move

'JDE_PRODUCTION_Log_1' to 'C:\JDE_PRODUCTION\JDE_PRODUCTION_Log_1.LDF',

stats

THE RESULT STATEMENT IS AS FOLLOWS:

Msg 5184, Level 16, State 2, Line 1

Cannot use file 'C:\JDE_PRODUCTION\JDE_PRODUCTION_Data.MDF' for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used. Either the disk resource containing the file is not present in the cluster group or the cluster resource of the Sql Server does not have a dependency on it.

Msg 3156, Level 16, State 3, Line 1

File 'JDE_PRODUCTION_Data' cannot be restored to 'C:\JDE_PRODUCTION\JDE_PRODUCTION_Data.MDF'. Use WITH MOVE to identify a valid location for the file.

msalmaan SSChasing Mays Points: 621 More actions · Answer 7

The above is my restore statement and it gets terminated abnormally..please help me

restore database JDE_PRODUCTION

from disk='\\baupsrv\d$\JDE_PROD\JDE_PROD_DB_FULL_BKUP'

with move

'JDE_PRODUCTION_Data' to 'C:\JDE_PRODUCTION\JDE_PRODUCTION_Data.MDF',

Move

'JDE_PRODUCTION_Data_1' to 'C:\JDE_PRODUCTION\JDE_PRODUCTION_Data_1.NDF',

Move

'JDE_PRODUCTION_Data_2' to 'C:\JDE_PRODUCTION\JDE_PRODUCTION_Data_2.NDF',

Move

'JDE_PRODUCTION_Data_3' to 'C:\JDE_PRODUCTION\JDE_PRODUCTION_Data_3.NDF',

Move

'JDE_PRODUCTION_Data_4' to 'C:\JDE_PRODUCTION\JDE_PRODUCTION_Data_4.NDF',

Move

'JDE_PRODUCTION_Data_5' to 'C:\JDE_PRODUCTION\JDE_PRODUCTION_Data_5.NDF',

Move

'JDE_PRODUCTION_Data_6' to 'C:\JDE_PRODUCTION\JDE_PRODUCTION_Data_6.NDF',

Move

'JDE_PRODUCTION_Data_7' to 'C:\JDE_PRODUCTION\JDE_PRODUCTION_Data_7.NDF',

Move

'JDE_PRODUCTION_Data_8' to 'C:\JDE_PRODUCTION\JDE_PRODUCTION_Data_8.NDF',

Move

'JDE_PRODUCTION_Data_9' to 'C:\JDE_PRODUCTION\JDE_PRODUCTION_Data_9.NDF',

Move

'JDE_PRODUCTION_Data_10' to 'C:\JDE_PRODUCTION\JDE_PRODUCTION_Data_10.NDF',

Move

'JDE_PRODUCTION_Log' to 'C:\JDE_PRODUCTION\JDE_PRODUCTION_Log.LDF',

Move

'JDE_PRODUCTION_Log_1' to 'C:\JDE_PRODUCTION\JDE_PRODUCTION_Log_1.LDF',

stats

THE RESULT STATEMENT IS AS FOLLOWS:

Msg 5184, Level 16, State 2, Line 1

Cannot use file 'C:\JDE_PRODUCTION\JDE_PRODUCTION_Data.MDF' for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used. Either the disk resource containing the file is not present in the cluster group or the cluster resource of the Sql Server does not have a dependency on it.

Msg 3156, Level 16, State 3, Line 1

File 'JDE_PRODUCTION_Data' cannot be restored to 'C:\JDE_PRODUCTION\JDE_PRODUCTION_Data.MDF'. Use WITH MOVE to identify a valid location for the file.

Lowell SSC Guru Points: 323546 More actions · Answer 8

edit your new posts so they do not use huge font, and remove the two duplicates...very annoying.

the error message says it all...Google could have saved you hours of waiting.

you are trying to restore on the C:\ drive.

on a cluster, you can only use a drive that can be seen by the all the servers in the cluster.

find that drive, and change the path tot he mdf files.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jerry Hung SSChampion Points: 12968 More actions · Answer 9

Another reason to always post the error message

Solved by SQL error message

Msg 5184, Level 16, State 2, Line 1
Cannot use file 'C:\JDE_PRODUCTION\JDE_PRODUCTION_Data.MDF' for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used. Either the disk resource containing the file is not present in the cluster group or the cluster resource of the Sql Server does not have a dependency on it.

SQLServerNewbieMCITP: Database Administrator SQL Server 2005