question

sabari avatar image
sabari asked

mdf,ldf file attach problem???

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???
sql-server-2008sql-server-2005
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
Copy over the ndf files as well, and make sure that the CREATE DATABASE command references those files in the same way as you reference the mdf & ldf files. You may also need to ensure that the CREATE DATABASE command makes reference to both the logical / internal name and the filename.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sabari avatar image sabari commented ·
so u r saying, i've to mention these secondary files too on server machine side. rite?? i mean create database test1 on (file name = '...\\primary.mdf'), (file name = '...\\primary.ldf'), (file name = '...\\secondary1.ndf'), ..... (file name = '...\\secondaryN.ndf') for attach go it'll work now. rite??
0 Likes 0 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
Should do. you might need to also mention which file is which, in terms of the internal filename vs the filesystem filename.
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
Any time you define a database, the files that make up that database will always be a part of that database until or unless you specifically remove them. So if you have two data files and a log file when you create a database, you'll need to move both data files to attach that database to another server (you can actually get away with not moving the log). Further, you'll have to define these same files (including the log) when you run a RESTORE operation on another server.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.