Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
6  
Where did you learn that stopping the SQL Server service and then copying these mdf/ndf/ldf files would be a good idea? There is a great command called BACKUP DATABASE that is much more appropriate for this task. –  Aaron Bertrand Sep 6 '12 at 12:46

4 Answers 4

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.

share|improve this answer

(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.

share|improve this answer

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.

share|improve this answer

Your problem is with the permissions in your folder or files (mdf/ldf)!

share|improve this answer
    
-1 because I don't think this is correct, and because you have given such minimal explanation. Please consider improving this answer and I'll consider removing my downvote! –  Jack Douglas Sep 7 '12 at 9:56
3  
Operating system error 2: "2(The system cannot find the file specified.)" is not permission error. Permission error would result in error 5: (Access Denied). –  Remus Rusanu Sep 7 '12 at 12:54
1  
-1 -> As the comments above have shown, your answer is likely incorrect. You may want to either amend your answer, or consider deleting it yourself. –  Max Vernon Sep 7 '12 at 21:31

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.