Recovery of SQL Server 2005 database log loss

Online about SQL Server 2005 database log loss processing method basically is to modify the database system table, but I test n many times are unsuccessful, finally with the English keyword Google, found a feasible method, tortured me two days to solve the problem ...

Background note: A database named TestDB has lost log files and only data files Testdb.mdf

Please do not rush to start operation, if you are really in a hurry, please directly see my success test method (* content below)

1. Create a new database TestDB

2. Stop the SQL Server server

3. Delete the log file testdb_log.ldf of the testdb you just created, and replace the data file with the data file of the database to be recovered as mentioned above Testdb.mdf

4. Start the SQL Server server, reconnect in Management Studio, and discover that the TestDB database is still unavailable

5. Set the system table for the database to be modifiable

sp_configure ' allow updates ', 1

Reconfigure with override

6. Set test to Emergency Repair mode

Update sysdatabases set status=-32768 where dbid=db_id (' TestDB ')

Message 259, Level 1, state, line 1

Forbid specific updates to the system. ( that is, you are not allowed to update system tables )

The DAC is required to log in to SQL Server for modification, and the login method is shown in http://space.itpub.net/10314474/viewspace-696409

Setting SQL Server to single_user mode

D:\Documents and Settings\username>net Stop "SQL Server (MSSQLSERVER)"

D:\Documents and settings\username>net start "SQL Server (MSSQLSERVER)"/m

I thought I'd seen the dawn of victory here, and I knew the error

Message 4406, Level 1, state, line 1

Due to a derivative or constant linked fields bit, the update or insertion failure of the visual or function ' sys.sysdatabases ' .

This problem has plagued me for a long time, and I think this method of modifying the system table should not be feasible.

(******* above methods on the Internet widely circulated, but I did not test success, I do not know if anyone has successfully *******)

***********************

The following methods were successfully tested by myself three times:

Of course the purpose of the method is the same: rebuilding the log file

1. First look at the status of the database at this time

Select Name,state,state_desc from sys.databases where name = ' TestDB '

Name State State_desc

TestDB 3 recovery_pending

2. Setting the database to emergency mode

ALTER DATABASE TestDB SET EMERGENCY

Select Name,state,state_desc from sys.databases where name = ' TestDB '

Name State State_desc

TestDB 5 EMERGENCY

3. Set the database to single-user mode

ALTER DATABASE testdb SET single_user with ROLLBACK IMMEDIATE

4. Rebuilding the log file

DBCC CHECKDB (' TestDB ', Repair_allow_data_loss)

returned result information: file activation bug. The actual file name "D:\Program Files\Microsoft SQL Server\mssql.1\mssql\data\testdb_log.ldf" may not be correct.

Warning: The records of the repository ' TestDB ' have been rebuilt. Transaction consistency has been lost. The RESTORE link has been interrupted and the server has no previous memory content, so you need to know that. You should perform DBCC CHECKDB to verify the consistency of the entity. The repository has entered the DBO-only mode. Once you are ready to use the repository, you must re-set the library options and delete any extra records.

5. Check the database status

Select Name,state,state_desc from sys.databases where name = ' TestDB '

Name State State_desc

TEST 0 ONLINE

6. Modify the database from single-user mode to multi-user mode

ALTER DATABASE TestDB SET multi_user

The database is now ready for normal use

Original link: http://blog.itpub.net/10314474/viewspace-696555/

Recovery of SQL Server 2005 database log loss