Recreate db from mdf and ndf files, missing ldf

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Recreate db from mdf and ndf files, missing ldf

Author

Topic

boosts
Starting Member

9 Posts

Posted - 2012-05-21 : 18:16:42

The log file for a dev database was deleted and all is left are the mdf and ndf files. This is on a SQL server 2008 R2. Unfortunately there were no backups and some important sprocs were lost.

Is there a way to recreate the db from what is left?

My research so far has brought up methods such as:

1:http://stackoverflow.com/questions/773059/how-to-recover-database-from-mdf-in-sql-server-2005

2:http://technet.microsoft.com/en-us/library/ms174385.aspx

The first method failed but during the process of creating the new database I did not create the 10+ ndf files and that may have been the cause of the failure.

The second method fails as well.

Any help is much appreciated.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-05-21 : 18:25:40

You have to use CREATE DATABASE...FOR ATTACH syntax, like this:

CREATE DATABASE myDatabase
ON (FILENAME = 'D:\datafile.mdf'),
(FILENAME = 'D:\datafile1.ndf'),
(FILENAME = 'D:\datafile2.ndf'),
(FILENAME = 'D:\datafile3.ndf'),
(FILENAME = 'D:\datafile4.ndf'),
(FILENAME = 'D:\datafile5.ndf'),
(FILENAME = 'D:\datafile6.ndf'),
(FILENAME = 'D:\datafile7.ndf'),
(FILENAME = 'D:\datafile8.ndf'),
(FILENAME = 'D:\datafile9.ndf'),
(FILENAME = 'D:\datafile10.ndf')
FOR ATTACH;

Change the file names and database name as needed.

AND START MAKING REGULAR BACKUPS AND KEEP THEM SAFE

boosts
Starting Member

9 Posts

Posted - 2012-05-21 : 19:26:37

quote:
Originally posted by robvolk

You have to use CREATE DATABASE...FOR ATTACH syntax, like this:
CREATE DATABASE myDatabase
ON (FILENAME = 'D:\datafile.mdf'),
(FILENAME = 'D:\datafile1.ndf'),
(FILENAME = 'D:\datafile2.ndf'),
(FILENAME = 'D:\datafile3.ndf'),
(FILENAME = 'D:\datafile4.ndf'),
(FILENAME = 'D:\datafile5.ndf'),
(FILENAME = 'D:\datafile6.ndf'),
(FILENAME = 'D:\datafile7.ndf'),
(FILENAME = 'D:\datafile8.ndf'),
(FILENAME = 'D:\datafile9.ndf'),
(FILENAME = 'D:\datafile10.ndf')
FOR ATTACH;
Change the file names and database name as needed.

AND START MAKING REGULAR BACKUPS AND KEEP THEM SAFE

Thanks for your input. I did give it a try and I am getting the following error:
Unable to open the physical file "H:\MSSQL\Data\201112130005\my_ndf_file.ndf". Operating system error 5: "5(Access is denied.)".

Does it have to do with the permissions of the File?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-05-21 : 20:40:24

Yes, you'll either need to move the files to another disk, or modify the permissions on the drive/folder to permit read/write access for the SQL Server service account.

boosts
Starting Member

9 Posts

Posted - 2012-05-22 : 00:34:32

quote:
Originally posted by robvolk

Yes, you'll either need to move the files to another disk, or modify the permissions on the drive/folder to permit read/write access for the SQL Server service account.

After resolving the permission issue. I now get the following error:

One or more files do not match the primary file of the database. If you are attempting to attach a databas, retry teh operation with the correct files. If this is an existing database, the files may be corrupted and should be restored from a backup.

Any thoughts?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-05-22 : 07:01:54

Sounds pretty self-explanatory to me. Are you sure all the files you're trying to attach belong to that database, and that you're not missing any? If they're all accounted for, then they got corrupted, and you're SOL.

By the way, "some important sprocs were lost", I'm assuming by this statement that you're not using source control. If that's the case, start. This situation is exactly why you need it.

And if you know who deleted the log file, revoke their access to the server.

boosts
Starting Member

9 Posts

Posted - 2012-05-22 : 13:51:18

quote:
Originally posted by robvolk

Sounds pretty self-explanatory to me. Are you sure all the files you're trying to attach belong to that database, and that you're not missing any? If they're all accounted for, then they got corrupted, and you're SOL.

By the way, "some important sprocs were lost", I'm assuming by this statement that you're not using source control. If that's the case, start. This situation is exactly why you need it.

And if you know who deleted the log file, revoke their access to the server.

I was able to retrieve the important sprocs from another affected db by putting it in emergency mode.

I believe all those files are required for that mdf since attaching it lists those files as Not Found.

I got very close to recreating the database using the first solution I found but I received an error stating that not enough of the db was activated to recreate the database. The ldf file was recreated though.

At this point, I think I am going to recreate the db from a recent copy of production on another server. I wanted to avoid this because it's gonna take a few days to move it over.

Sachin.Nand

2937 Posts

Posted - 2012-05-22 : 14:14:38

quote:

Originally posted by robvolk

You have to use CREATE DATABASE...FOR ATTACH syntax, like this:

CREATE DATABASE myDatabase
ON (FILENAME = 'D:\datafile.mdf'),
(FILENAME = 'D:\datafile1.ndf'),
(FILENAME = 'D:\datafile2.ndf'),
(FILENAME = 'D:\datafile3.ndf'),
(FILENAME = 'D:\datafile4.ndf'),
(FILENAME = 'D:\datafile5.ndf'),
(FILENAME = 'D:\datafile6.ndf'),
(FILENAME = 'D:\datafile7.ndf'),
(FILENAME = 'D:\datafile8.ndf'),
(FILENAME = 'D:\datafile9.ndf'),
(FILENAME = 'D:\datafile10.ndf')
FOR ATTACH rebuild_log;

Small correction above in red..

After Monday and Tuesday even the calendar says W T F ....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources