Monday, June 6, 2011

How to recover database from MDF in SQL Server without an LDF

because there are times when you need to do something dodgy to save the day.

1. Detach database and move your mdf to save location.
2. Create new database 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 DATABASE yourdb SET EMERGENCY
7. ALTER DATABASE yourdb SET SINGLE_USER
8. DBCC CHECKDB (yourdb, REPAIR_ALLOW_DATA_LOSS)
9. ALTER DATABASE yourdb SET MULTI_USER
10. ALTER DATABASE yourdb SET ONLINE

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