September 25, 2007 at 10:42 am
Hi there. Newbie question for you. I have backed up my test database that resided in an instance with the folder structure \MSSQL.4\ , and am attempting to restore it into an instance on another server. The instance has the same name, but the folder name is \MSSQL.2\ , which I suspect is the reason I am experiencing restore errors:
Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\Duwamish7.mdf" failed with the operating system error 3(error not found). (Microsoft.SqlServer.Smo)
How do I get the restore to complete? Alternatively, would it be better to try a detach/attach?
Thanks for your help.
September 25, 2007 at 11:13 am
The most likely culprit is the internal location of the data files which still point to their original location. If the instance you backed up from is still running then those files are in use and the Instance you are restoring to would not be able to over write those datafiles. First step is to use the RestoreFilesOnly command
RESTORE FILELISTONLY FROM
DISK="c:\xxxx\DB1.bak"
This will give you an output similiar to the following:
LogicalName PhysicalName Type FileGroupName Size MaxSize
------------ ----------------- ------ -------------- ---------- --------------
DB1_Data D:\Data\DB1.mdf D PRIMARY 3276800 35184372080640
DB1_Log D:\Data\DB1.ldf L NULL 59047936 35184372080640
Then use the following restore command to MOVE the physical file to a new location:
RESTORE DATABASE army_amcom_nl
FROM disk = "c:\xxxx\DB1.bak"
WITH
REPLACE,
MOVE 'DB1_Data' TO 'd:\NewData\DB1.MDF',
MOVE 'DB1_Log' TO 'd:\NewData\DB1.LDF'
You can actually change the physical file name as well as the path if desired.
HTH,
James.
September 26, 2007 at 9:43 am
That worked a treat, thanks!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply