Sql-server – mdf,ldf file attach problem

sql serversql-server-2005sql-server-2008

I've created one database on my local system. consider database name is test. This database primary files are test.mdf, test_log.ldf. and contains ndf files too.

Then i've stopped sql server service. then copied these primary files to another server machine*(not ndf files).*

Now i've to create one database 'test1' on that server machine, and have to attach these test.mdf and test_log.ldf files.

create database test1 on primary ( file name='C:\test.mdf'), ( file name='C:\test_log.ldf') for attach go

this query executed in server machine. it says error as """ Msg 5120, Level 16, State 5, Line 1 Unable to open the physical file "D:\data\ExistTable\FG1_lt_4000.ndf". Operating system error 2: "2(The system cannot find the file specified.)".

Msg 5120, Level 16, State 5, Line 1 Unable to open the physical file "D:\data\ExistTable\FG2_4000_8000.ndf". Operating system error 2: "2(The system cannot find the file specified.)".

Msg 5120, Level 16, State 5, Line 1 Unable to open the physical file "D:\data\ExistTable\FG3_8000_inf.ndf". Operating system error 2: "2(The system cannot find the file specified.)".

Msg 1813, Level 16, State 2, Line 1 Could not open new database 'test'. CREATE DATABASE is aborted. """

now what to do???

Best Answer

Well, did you try:

create database test1 on primary 
  ( filename='C:\test.mdf'), 
  ( filename='C:\FG1_lt_4000.ndf'),
  ( filename='C:\FG2_4000_8000.ndf'),
  ( filename='C:\FG3_8000_inf.ndf'),
  ( filename='C:\test_log.ldf')
for attach;

And yes, you do need to copy the ndf files as well. These are critical parts of your database, not garbage.

Otherwise, go back to your original server, take a backup, and then do a restore with move on the new server.

Related Question