asked
create a database with .mdf file
Hi team, yesterday there was a hard disk crash , and we managed to retrieve .mdf file, my question is how to make my database up now, we use SQL Server 2008 64 bit, there is no Backup
First of all, you may be stuck with a broken database. You **really** need to sort out backups of any system that means anything at all to you/your business! It may be possible to recover the database though. Take a **copy** of the mdf file and try to attach it to a test machine: CREATE DATABASE [YourBrokenDatabase] ON (NAME = [YourBrokenDatabase], FILENAME = N'c:\\blahblah.mdf') FOR ATTACH_REBUILD_LOG; This will attempt to attach the database and create an empty log file. I suggest you read [Paul Randal's blog][1], especially the [post on fixing a suspect database][2]. That gives a step-by-step approach to sorting out the problem you have. ***Then go and make sure your other databases are being backed up, and also that those backups are good (try restoring them to a test machine). Without backups that can be restored, you have no backups!*** [1]:
http://www.sqlskills.com/BLOGS/PAUL/ [2]:
http://www.sqlskills.com/blogs/paul/post/TechEd-Demo-Creating-detaching-re-attaching-and-fixing-a-suspect-database.aspx
Sing it brother! Backups, backups, backups, backups. Why, oh why, don't people set up backups? I just don't get it.
If there is only one mdf file then you have the option of using sp_attach_single_file_db
http://msdn.microsoft.com/en-us/library/ms174385.aspx. As @WilliamD suggests, work on a copy of the file you have rescued in case what you attempt is unsuccessful, you may need to revert to the rescued copy. Read the link in the other answer too, it will have very useful and relevant information for you.
I also think it will currently work just as fine, but I suspect it won't do anything different than CREATE DATABASE [..] FOR ATTACH_REBUILD_LOG.
sp_attach_single_file_db CREATE DATABASE [..] FOR ATTACH[_REBUILD_LOG] is replacing sp_attach_single_file_db From the BoL: This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. We recommend that you use CREATE DATABASE database_name FOR ATTACH instead.
There are third party recovery tools that might be able to help out if attaching the MDF file does not work. As others have mentioned above, why didn't you have backups. There is a good saying among many DBA's that backups are actually worthless. It is the restores that are priceless. I am sorry @ramesh that you may have learned this valuable lesson the hard way. Best of luck to you with recovering the data.
