Categories

A sample text widget

Etiam pulvinar consectetur dolor sed malesuada. Ut convallis euismod dolor nec pretium. Nunc ut tristique massa.

Nam sodales mi vitae dolor ullamcorper et vulputate enim accumsan. Morbi orci magna, tincidunt vitae molestie nec, molestie at mi. Nulla nulla lorem, suscipit in posuere in, interdum non magna.

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

1 comment to How to attach a SQL Server data file MDF without LDF

  • WordPress abc WordPress abc
    WordPress/abc; http://ssysadmin.com

    How to attach a SQL Server data file MDF without LDF…\r\n\r\nTo attach a MDF without LDF you can follow the steps below: 1. Create a new database with the same name and same database (i.e. mydatabase) MDF and LDF files (i.e. mydatabase_data.mdf and mydatabase_log.ldf). 2. Stop sql server and change the name of n…