How to attach a SQL Server data file MDF without LDF

To attach a MDF without LDF you can follow the steps below:\r\n

\r\n

1. Create a new database with the same name and same database (i.e. mydatabase) MDF and LDF files 

\r\n

(i.e. mydatabase_data.mdf and mydatabase_log.ldf).\r\n

\r\n

2. Stop SQL server and change the name of new MDF (i.e. mydatabase.mdf to mydatabase-new.mdf) and 

\r\n

copy the original MDF to this location and delete the LDF file.\r\n

\r\n

3. Start SQL Server.\r\n

\r\n

4. Now your database will be marked suspect.\r\n

\r\n

5. Update the sysdatabases to update to Emergency mode. This will not use LOG files in start up:\r\n

\r\n

\r\n

\r\n

\r\n

\r\n

\r\n

\r\n

\r\n

\r\n

Sp_configure "allow updates", 1\r\n

\r\n

go\r\n

\r\n

Reconfigure with override\r\n

\r\n

GO\r\n

\r\n

Update sysdatabases set status = 32768 where name = "BadDbName"\r\n

\r\n

go\r\n

\r\n

Sp_configure "allow updates", 0\r\n

\r\n

go\r\n

\r\n

Reconfigure with override\r\n

\r\n

GO

\r\n

\r\n

\r\n

6. Restart SQL server. Now the database will be in emergency mode.\r\n

\r\n

7. Now execute the undocumented DBCC to create a log file:\r\n

\r\n

\r\n

\r\n

\r\n

\r\n

\r\n

\r\n

\r\n

\r\n

DBCC REBUILD_LOG (mydatabase,'c:\mydatabase.ldf').\r\n

\r\n

Note: (replace the mydatabase and log file name and path based on your requirement)\r\n

\r\n

\r\n

\r\n

8. Execute sp_resetstatus <mydatabase>.\r\n

\r\n

9. Restart SQL server and see the database is online.\r\n

\r\n

\r\n

One thought on “How to attach a SQL Server data file MDF without LDF”

  1. Pingback: sSysAdmin blog

Comments are closed.