Several common operations for SQL Server transaction logs

As we know, the SQL Server transaction log is used primarily to record changes made to the database by all transactions, and if the system fails, it becomes the only source of the latest data. Log operations often have the following applications:

One, the transaction log file LDF lost

When we do not delete or LDF file loss, the database only left MDF files, at this time directly through the additional MDF can not restore the database, then how can we restore the database? We can divide the log files of SQL Server into two forms: one is a log with no active transaction, The other is a log of active transactions, we are based on two different cases of database recovery.

1. Log recovery without active transactions

When the file does not have an active log, we can easily use the MDF file can directly restore the database, the following methods:

1 database If there is no log, will be in doubt, we can first through Enterprise Manager in the corresponding database in the right click, and then under "All Tasks" select "Separate database" to separate the database;

2) The use of MDF file attached to the database to generate new log files, can be used in Enterprise Manager database right click to select "All Tasks" under the "additional database" to the database attached.

This allows you to restore the database directly, and if the log file of the database contains active transactions, you cannot recover the database by using this method, so you have to use the following method.

2. Log recovery with active transactions

When the log of a transaction has occurred, when it is lost, we use the following methods to achieve:

1 Create a new database with the same name, such as the original database named MyDB, then stop the SQL Server server, remove the database main data MDF file, and then restart the SQL Server server, create a new database MyDB with the same name, and then stop the SQL Server server. To overwrite the removed MDF file and then restart the SQL Server server, the system table is not allowed to be modified by default, we need to run the following statements, in Query Analyzer, select master Database and execute:

sp_configure ' allow updates ', 1

Reconfigure with Override

Then run the following statement to set the status property of the MyDB database in the sysdatabases table to ' 37268 ', setting the MYDB database to emergency mode.

Update sysdatabases set status=32768 where name= ' MYDB '

Then set the database MyDB to Single-user mode, restart the SQL Server servers, and set the database MyDB to Single-user mode

sp_dboption ' MYDB ', ' Single user ', ' true '

Then run the following statement to check the database mydb

DBCC CHECKDB (' MYDB ')