restore to different machine(instance) problem

Question

restore to different machine(instance) problem

SQL*

SSCertifiable

Points: 6638
More actions
April 6, 2009 at 9:44 pm

#384303

hi,
i've a backup file, which has two or more files like log file on two different paths( drive E: drive , D: Drive). how can i restore this backup file on some other instance having single drive ( C:).
thanks.
🙂

Viewing 14 posts - 1 through 13 (of 13 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

You need to read the RESTORE DATABASE command in Books Online. Specifically, you need to read about the MOVE clause. If, after reading, you still have questions come back and let us know. One of the things we will need to know as well are the logical and physical filenames of the database and log files.

SQL* SSCertifiable Points: 6638 More actions · Answer 2

Please explain why the error is comming.The backup set holds a backup of a database other than the existing 'SASE' database

🙂

SQL* SSCertifiable Points: 6638 More actions · Answer 3

Server: Msg 5133, Level 16, State 1, Line 1

Directory lookup for the file "E:\MSSQL\data\SASE_log2_Log.LDF" failed with the operating system error 3(The system cannot find the path specified.).

Server: Msg 3156, Level 16, State 1, Line 1

File 'SASE_log2' cannot be restored to 'E:\MSSQL\data\SASE_log2_Log.LDF'. Use WITH MOVE to identify a valid location for the file.

Server: Msg 5133, Level 16, State 1, Line 1

Directory lookup for the file "E:\MSSQL\data\SASE_log3_Log.LDF" failed with the operating system error 3(The system cannot find the path specified.).

Server: Msg 3156, Level 16, State 1, Line 1

File 'SASE_log3' cannot be restored to 'E:\MSSQL\data\SASE_log3_Log.LDF'. Use WITH MOVE to identify a valid location for the file.

Server: Msg 3119, Level 16, State 1, Line 1

Problems were identified while planning for the RESTORE statement. Previous messages provide details.

Server: Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

🙂

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

As I stated in your other thread, I also need the code you are using for the restore. Let us use this one thread for this problem instead of going back and forth between them.

SQL* SSCertifiable Points: 6638 More actions · Answer 5

Thanks,

here is the t-sql

RESTORE DATABASE SASE

FROM DISK = 'C:\SASE\DatabaseOldBackup\SASE041202'

WITH REPLACE

🙂

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Please explain why the error is comming.The backup set holds a backup of a database other than the existing 'SASE' database

I'm not really sure what you mean by this, can you explain it in a little more detail?

SQL* SSCertifiable Points: 6638 More actions · Answer 7

i need the reason why the specified error is coming when i'm restoring, T-SQL in before post.

🙂

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Simple answer, the server you are attempting the restore on is not configured the same as the server from which you are attempting a restore.

Since they are not the same, you need to use the MOVE clause as well to restore the database on this server.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

If you remember, I also asked you for the logical and physical files names. To assist further, I will need this information from both servers for this database.

SQL* SSCertifiable Points: 6638 More actions · Answer 10

Is the SQL I have written:

i have only the backup of this databse. at present we dont have that server.

RESTORE DATABASE SASE

FROM DISK = 'C:\SASE\DatabaseOldBackup\SASE041202'

WITH REPLACE, MOVE 'SASE_log2' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SASE_log.LDF'

, MOVE 'SASE_log3'

TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SASE_log.LDF'

, MOVE 'SASE_DATA' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SASE.mdf'

🙂

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

vrabhadram (4/6/2009)
Is the SQL I have written:
i have only the backup of this databse. at present we dont have that server.
RESTORE DATABASE SASE
FROM DISK = 'C:\SASE\DatabaseOldBackup\SASE041202'
WITH REPLACE, MOVE 'SASE_log2' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SASE_log.LDF'
, MOVE 'SASE_log3'
TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SASE_log.LDF'
, MOVE 'SASE_DATA' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SASE.mdf'

Does this code give you an error as well? If so, what is the error message here?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 12

Run the following code, putting the output to a .txt file and upload that file here.

RESTORE FILELISTONLY FROM DISK = 'C:\SASE\DatabaseOldBackup\SASE041202'

SQL* SSCertifiable Points: 6638 More actions · Answer 13

Thanks for your great help its really helpful for me.

RESTORE DATABASE devlop

FROM DISK = 'C:\devlop\DatabaseOldBackup\devlop041202'

WITH move 'devlop_Data' to 'C:\RESTORETEST\devlop.mdf'

, MOVE 'devlop_Log' TO 'C:\RESTORETEST\devlop_log.LDF'

, MOVE 'devlop_log2' TO 'C:\RESTORETEST\devlop_log.LDF'

, MOVE 'devlop_log3' TO 'C:\RESTORETEST\devlop_log.LDF'

, REPLACE

is the second server ( restoring) database path 'C:\RESTORETEST\devlop.mdf'

🙂