Wednesday, June 5, 2013

SQL Server: Attach MDF without LDF

Yes, it happens. Sometimes, not very often I hope, you find yourself without a corrupt LDF file or for some odd reason you were handed an MDF file and you need to make this database file available  for use. Desperate times call for desperate measures right? I hope you never have to deal with this in a production environment, because being the good DBA that you are, you have implemented a sound Disaster Recovery plan in your SQL Server environment. Without further ado, here is how you'd attach an MDF without an LDF. 

1. Create a new database with the same name and same MDF and LDF files 

2. Stop sql server and rename the existing MDF to a new one and copy the original MDF to this location and delete the LDF files.

3. Start SQL Server

4. Now your database will be marked suspect 5. Update the sysdatabases to update to Emergency mode. This will not use LOG files in start up

Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO

6. Restart sql server. now the database will be in emergency mode

7. Now execute the undocumented DBCC to create a log file

DBCC REBUILD_LOG(dbname,'c:\dbname.ldf') -- Undocumented step to create a new log file.

(replace the dbname and log file name based on ur requirement)

8. Execute sp_resetstatus <dbname>

9. Restart SQL server and see the database is online.