[DonaldLyons86]

No, the question isn't stupid but the "admin" sure is.... BUT, if you can help, please do! The problem is how to recover a .mdf file into an operational db without an .ldf file and no useful backups on SQL Server 2005. See, I told you - STUPID!!

Now, I've seen plenty of suggestions for SQL 2K, but not 2005. Single file attach - no go. Detach wasn't proper. DBCC REBUILD_LOG, where most SQL 2K solutions end up - can't get there on 2005. At least not in any method I've tried. Access to those system tables not allowed (no "ad-hoc queries"). 3rd party recovery tools? Anything....?

(This isn't a big db, ~ 8GB, but it is rather important AND urgent).

Many thanks!

[Advertisements]

Best Answer

You will have data loss but it can be done.
1) Detach database and move your mdf to save location.
2) Create new databse of same name, same files, same file location and same file size.
3) Stop SQL server.
4) Swap mdf file of just created DB to your save one.
5) Start SQL. DB will go suspect.
6) ALTER DATABSE <your db> SET EMERGENCY
ALTER DATABASE <your db> SET SINGLE_USER
7) DBCC CHECKDB (<your db>, REPAIR_ALLOW_DATA_LOSS)
8) ALTER DATABASE <your db> SET MULTI_USER
ALTER DATABSE <your db> SET ONLINE

From programs you can try SQL Server Recovery Toolbox if you want. There is a demo version, so you can check it will help you or not. http://www.oemailrec...sql_repair.html

[OrnSveinsson]

Best Answer

You will have data loss but it can be done.
1) Detach database and move your mdf to save location.
2) Create new databse of same name, same files, same file location and same file size.
3) Stop SQL server.
4) Swap mdf file of just created DB to your save one.
5) Start SQL. DB will go suspect.
6) ALTER DATABSE <your db> SET EMERGENCY
ALTER DATABASE <your db> SET SINGLE_USER
7) DBCC CHECKDB (<your db>, REPAIR_ALLOW_DATA_LOSS)
8) ALTER DATABASE <your db> SET MULTI_USER
ALTER DATABSE <your db> SET ONLINE

From programs you can try SQL Server Recovery Toolbox if you want. There is a demo version, so you can check it will help you or not. http://www.oemailrec...sql_repair.html

[OrnSveinsson]

May be this article will give you more information.

http://www.techrepublic.com/forums/questions/it-seems-our-sql-database-is-corrupted/

[allen58441]

Hi,DonaldLyons86

 

When a MDF database file is corrupt, you can first use the SQL Server build-in command DBCC CheckDB and CheckTable to repair it, see:

http://technet.micro...y/ms176064.aspx

and

http://technet.micro...v=sql.110).aspx

to get more information about the commands. This is the recommended solution which you should try first.

If the corruption is severe and the above commands do not work, then you may try some third party tools to scan and recover the data from your corrupted MDF and NDF database files. Below is a list of SQL recovery tools:

https://www.datanume...ery/compare.htm

The list seems to rather comprehensive that including nearly all the popular tools in the market. And all the tools provide a free demo so you can try before you purchase.

Hope this will help. Good luck!

 

[DonaldLyons86]

Thank you guys for your feedback and help. Sorry, that long time did not respond. This is because the issue was solved and I forgot to tell.) Thank you!

[lucasdonovan1987]

The manual procedure for fixing SQL Database related errors has been mentioned in this article on Easy and Free way to do SQL Database Recovery

 

I hope this helps.