Database read-only solution

1. the logon identity has insufficient permissions. If it is set to "network service", this situation may occur. You can try to set it to "local system ".

Device activation error. The physical file name 'd:/test_log.ldf' may be incorrect.

 

What should we do? Don't worry. The following is an example of the restoration method.
A. We use the default method to create a database for recovery (such as test ). It can be created in SQL Server Enterprise Manager.
B. Stop the database server.
C. Delete the log file test_log.ldf of the database you just generated, and overwrite the generated data file est_data.mdf with the MDF file of the database you want to restore.
D. Start the database server. The database test status is "Suspect ". At this time, you cannot perform any operations on this database.
Set the database to allow direct operation of system tables. In SQL Server Enterprise Manager, select database server.
Server, right-click and select "properties". On the "Server Settings" Page, select "allow direct modification to system directory. Yes.
Use the following statement.
Use master
Go
Sp_configure 'Allow updates', 1
Go
Reconfigure with override
Go
Set test to Emergency Repair Mode
Update sysdatabases set status =-32768 where dbid = db_id ('test ')
In this case, you can see in SQL Server Enterprise Manager that the database is in "Read-Only/suspicious/offline/emergency mode ".
See the table in the database, but only the system table
G. Execute the true recovery operation below to recreate the database log file.
DBCC rebuild_log ('test', 'c:/program files/Microsoft SQL Server/MSSQL/data/test_log.ldf ')
If the following prompt is displayed during execution:

Server: Message 5030, level 16, status 1, Row 1
The database cannot be locked for this operation.
DBCC execution is complete. If DBCC outputs an error message, contact the system administrator.

This indicates that other programs are using this database. If you used SQL Server Enterprise Manager in step f just now
Open the system table of the test database, and exit SQL Server Enterprise Manager.

The prompt for correct execution should be similar:

Warning: the log of the database 'test' has been rebuilt. Transaction consistency is lost. DBCC checkdb should be run to verify physical consistency
. The database must be reset and redundant log files may need to be deleted.
DBCC execution is complete. If DBCC outputs an error message, contact the system administrator.

In SQL Server Enterprise Manager, the database status is "for DBO only ". You can
The User table in the database is accessed.
H. Verify Database Consistency (omitted)
DBCC checkdb ('test ')
??
The general execution result is as follows:

Checkdb finds 0 allocation errors and 0 consistency errors (in the database 'test ).
DBCC execution is complete. If DBCC outputs an error message, contact the system administrator.

Set the database to normal
Sp_dboption 'test', 'dbo use only', 'false'
If no error occurs, Congratulations. Now we can use the recovered database normally.
J. In the last step, we need to restore the "allow direct modification to system directory" set in Step E. Because the operating system table
It is a dangerous thing. Of course, we can recover the data in SQL Server Enterprise Manager or use
The following statement is completed:
Sp_configure 'Allow updates', 0
Go
Reconfigure with override
Go