Attach a database method without log files

Attach a database method without log files

       Today, the client executed SQL and reported an error. It was found that the client server database disk was completely used up, and the log file reached 500GB. Later, due to my wrong operation, the log file (.ldf) was deleted, and the old .mdf file was attached later. It means that there is no log file attached and it was unsuccessful. After some tossing, it was finally solved. Share it below!

Read the table of contents

back to the top

Steps

    1. Create a new database file with the same name

    2. Suspend SQLSetver service

    3. Overwrite the new database with the original mdf file and delete the ldf file of the new database

    4. Restart the SQLSetver service, the database you see at this time looks like this, and it cannot be opened

    5. Execute the following SQL statement

 1 --1. Set to emergency
 2 alter database database name set emergency
 3 --2. Set to single user mode
 4 alter database database name set single_user
 5 --3. Check and rebuild the log file
 6 dbcc checkdb('database name',REPAIR_ALLOW_DATA_LOSS)
 7 --4. If there is an error message in step 3, run step 4, if there is no error, skip
 8 dbcc checkdb('database name',REPAIR_REBUILD)
 9 --5. Revert to multi-user mode
10 alter database database name set multi_user

    6. At this point, the log file of the modified library will be regenerated, and the whole process is complete

  Or you can use manual attachment (refer to @code道程工 for this method)

back to the top

   Previous article: How to delete database log files

Reference: https://cloud.tencent.com/developer/article/1014587 How to attach a database without log files-Cloud + Community-Tencent Cloud