December 13, 2004 at 11:42 am
My server_1 has network access problem so I need to restore the DB to server_2.
The backup job on server_1 uses a device that backs up to a file on the backup server.
So I have created a device on server2 pointing to the backup server and then using QA I am unable to restore, because the DB on server_1 had .MDF, .NDF and .LDF has files pointing to drives that do no exit on server_2. Here is my syntax
RESTORE DATABASE CDS FROM D2BACKUP_DAY5 WITH FILE=8,
MOVE 'CDS_Data' TO 'H:\mssql\data\data\CDS_Data.MDF'
MOVE 'CDS_Data1' TO 'H:\mssql\data\CDS_Data1.NDF',
MOVE 'CDS_OP_Index' TO 'H:\MSSQL\Data\CDS_OP_Index_Data.NDF',
MOVE 'CDS_IP_Index' TO 'H:\MSSQL\Data\CDS_IP_Index_Data.NDF',
MOVE 'CDS_IP_1' TO 'H:\msssql\data\CDS_IP_1_Data.NDF',
MOVE 'CDS_OP_1' TO 'H:\msssql\data\CDS_OP_1_Data.NDF',
MOVE 'CDS_WL_1' TO 'H:\msssql\data\CDS_WL_1_Data.NDF',
MOVE 'CDS_Log' TO 'H:\mssql\data\CDS_Log.LDF',
MOVE 'CDS_Log1' TO 'H:\mssql\data\CDS_Log1.LDF',
MOVE 'CDS_Log2' TO 'H:\Mssql\data\CDS_Log2_Log.LDF'
and the error I get is this:
Server: Msg 5105, Level 16, State 2, Line 1
Device activation error. The physical file name 'H:\mssql\data\data\CDS_Data.MDF' may be incorrect.
Server: Msg 3156, Level 16, State 1, Line 1
File 'CDS_Data' cannot be restored to 'H:\mssql\data\data\CDS_Data.MDF'. Use WITH MOVE to identify a valid location for the file.
Server: Msg 5105, Level 16, State 1, Line 1
Device activation error. The physical file name 'H:\msssql\data\CDS_IP_1_Data.NDF' may be incorrect.
Server: Msg 3156, Level 16, State 1, Line 1
File 'CDS_IP_1' cannot be restored to 'H:\msssql\data\CDS_IP_1_Data.NDF'. Use WITH MOVE to identify a valid location for the file.
Server: Msg 5105, Level 16, State 1, Line 1
Device activation error. The physical file name 'H:\msssql\data\CDS_OP_1_Data.NDF' may be incorrect.
Server: Msg 3156, Level 16, State 1, Line 1
File 'CDS_OP_1' cannot be restored to 'H:\msssql\data\CDS_OP_1_Data.NDF'. Use WITH MOVE to identify a valid location for the file.
Server: Msg 5105, Level 16, State 1, Line 1
Device activation error. The physical file name 'H:\msssql\data\CDS_WL_1_Data.NDF' may be incorrect.
Server: Msg 3156, Level 16, State 1, Line 1
File 'CDS_WL_1' cannot be restored to 'H:\msssql\data\CDS_WL_1_Data.NDF'. Use WITH MOVE to identify a valid location for the file.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I thought it was possible to restore to another server and point the files to a new location. What am I doing wrong.
As always, any help appreciated greatly. Regards
December 13, 2004 at 11:48 am
Are you sure the H drive is valid and the directories exist? I'm not sure that restore will create new directories.
Also do you need the REPLACE option as well?
December 13, 2004 at 11:51 am
The new path must be on the server you are trying to restore to and the folders must already exist. So, check to make sure this path, H:\mssql\data\data\ exists on server2.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
December 13, 2004 at 11:52 am
the directories do exist as I created them (H:\mssql\data) but not the files.
I even tried creating empty file in the right directories but got same errors. Will try using REPLACE. Thanks
December 13, 2004 at 12:01 pm
If you pasted your restore command exactly as you were running it, your problem may be the missing comma at the end of the first move.
Steve
December 13, 2004 at 12:08 pm
How can I thank you all, I don't deserve to be a DBA. I put in the missing comma as suggested and the restore has started. I have been staring at this all day checking the syntax in despair.
The DB is about 80 GB so it will take time. I will report back.
Thank you all, I love this forum! regards
December 13, 2004 at 12:11 pm
You do deserve to be a DBA! When you had a problem you couldn't figure out, you went to the right place for help.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
December 13, 2004 at 1:57 pm
As promised, I am reporting back. In QA I got the message that the restore was sucessful. Also in EM I was able to see a list of all the tables/views associated with the DB.
However, when I try to open any table/view I get the message " An unexpected error happened during this operation"
I have stopped the sql service and restarted but same details appear again. I am convinced that restore worked ok but cannot see the contents of any table/view. Any pointers. Regards
December 13, 2004 at 2:38 pm
I assume that you are trying to "open any table/view" from EM. Have you tried a select query from QA?
By the way, about the comma, I can't tell you how many times I have shown code to another programmer/dba that I couldn't get to run, and they pointed out a missing period, comma, quote, ... Sometimes, it just takes a different set of eyes!
Steve
December 13, 2004 at 2:56 pm
I would also try to open the tables from another workstation. Maybe there is a problem with EM where you are running it. Also, try to open a table in master and see what happens.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
December 14, 2004 at 3:20 am
Yes, thanks again, My DB is now up and running on the new server. I re-booted my PC and that cured it. I can now see the tables etc. Best regards.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply