mdf,ldf file attach problem?

Question

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???

Answer 1

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.

Answer 2

(not ndf files).

And that is your problem. The MDF contains a list of all the files (and their location) that make the database. If a single file is missing then you don't have the entire database and you cannot attach only a fragment of the original database. You need to copy all the files, including NDFs and any secondary LDF you may have. All the files. Then try again to attach, specifying all the fragments (MDFs, NDFs, LDFs).

But, as Aaron already pointed out, there are already better ways to do it. Take a backup and then restore WITH MOVE:

restore database test1 from disk='...'
  with move 'test' to '...',
  move 'fg1' to '...',
  move 'fg2' to '...',
  ...
  move 'test_log' to '...';

To see the content of backup file use RESTORE FILELIST ONLY so you'll know exactly what files are to be moved during the restore. Not specifying WITH MOVE would attempt to restore the files in exactly the same drives/directories as on your local DB machine.

Answer 3

You have a permission problem on a database file. To resolve this you need to give full permission on .MDF and .LDF Files to the account used by SQL Server.

Second thing you can attach the database using wizard rather using the Script.

In Object Explorer --> Right Click On DATABASE --> Click Attach and then select your .MDF file.