SQL Server 2005 No log file attach database

Company website operation More than two years, log files Super large, in the reload system, in order to save time, there is no backup log files, and is no separation of the log file to delete (next time must remember to separate and then delete the log file). The result is that the database is not attached. An error occurred.

Workaround:

1. Create a new database of the same name.

2, stop the database service, overwrite the new database master file (tip: Preferably on the same disk, the new database master file is deleted or removed, and then the database to restore the main file to cut the past, so you can save time. ）

3, start the database service, the database becomes suspect or suspicious state. Then run in Query Analyzer:

Alter database has no log file name set emergency

Set to a state of emergency.

4. Re-run:

Alter database has no log file name set Single_user

Or:

sp_dboption ' no log file database name ', ' Single user ', ' true '

Set to single user mode.

5. Check and rebuild the log file to run:

DBCC CHECKDB (' database name without log file ', Repair_allow_data_loss)

This time is longer. Wait patiently! If you have an error message, run it again:

DBCC CHECKDB (' database name without log file ', Repair_rebuild)

For repair. If there are no errors, you can skip them.

6. Revert to multi-user mode

Alter database has no log file name set Multi_user

Or:

sp_dboption ' no log file database name ', ' Single user ', ' false '

Refresh the database and you'll see the repaired database.

I was so fixed, there are many repair methods on the Internet, I tried, did not succeed. This method is obtained through multiple combinations. May not be the database of each version is applicable, if you use later, no, hope to leave a message, so that we can discuss together and progress together!

#1楼 2010-05-21 19:19 | Thank Poofly for providing the method:

CREATE DATABASE DBname on (FILENAME = ' D:\SalesData\archdat1.mdf ') for Attach_rebuild_log; GO

#12楼 121.29.113.* 2010-08-11 09:11 | peking2[non-registered users] commentary:

This method is good and easy to operate. My personal Sql2005 MDF file size is 16G. The operation was successful under sql2008.

#30楼 116.228.132.* 2011-11-26 13:58 | $ Nirvana Reborn $

Oh, sorry! After the test, I reported such a mistake. Error message: Unable to open database ' db_name ' version 611. Please upgrade the database to the latest version.

(SQL Server 2005 is attached to SQL Server 2008)

$ Nirvana Reborn $14:34:20 provides a workaround: Put the MDF file generated in the report 2005 under the Sqlserver2008/mssql/data directory in the installation directory.

I come to: http://www.cnblogs.com/ATree/archive/2010/05/16/Sqlserver2005-mdf-ldf.html

SQL Server 2005 No log file attach database