?
Recovery step n ??????? Microsoft SQL Server when the database log file is lost
Key words:
Emergency Mode, DBCC CHECKDB, DTS
Reference list:
name
Author
serial number
Release date
"How to recover when the SQL Server database crashes? "
Yihong Mon
?
?
"SQL Server is absent from normal deletion log file (LDF) recovery method
_Rambo
?
?
Thing's cause
Yesterday, the system administrator told me that our disk space in which we have internal application database is under. I noticed that the database event log file XXX_DATA.LDF file has grown to 3GB, so I decrease the log file. After the failure of the contracting database, I made a maximum and fooling error since I have entered the industry: I accidentally deleted this log file! Later, I saw all the discussions and database recovery articles: "If you have to ensure that the database log file exists, it is critical, even Microsoft even has a KB article how to recover only by log files. I really don't know what I think at that time? !
This is broken! This database can't be connected, and the enterprise manager is written next to its "(quenus)". And the most important, this database never backed up. I found some of the database server for half a year ago, it can be used, but there are fewer records, tables, and stored procedures. I really hope this is just a nightmare!
No effect recovery steps
Additional database
_Rambo speesses that there is no activity log in the deleted log file, can be recovered:
1. Separate the quenched database, you can use sp_detach_db2, additional database, you can use sp_attach_single_file_db
However, unfortunately, after execution, SQL Server questioned that the data files and log files did not match, so they were unable to attach the database data file.
DTS data export
No, you can't read the XXX database, the DTS Wizard reports "Initialization context errors".
Emergency mode
Yihong Bang said that there is no log for recovery, you can do this:
1. Set the database to Emergency Mode
2, re-establish a log file
3, restart SQL Server
4, set the application database to single user mode
5, do DBCC Checkdb
6. If there is no big problem, you can change the status of the database. Remember to turn off the modification options for the system table.
?
I practiced, remove the data file applying the database, re-establish a database XXX of the same name, then stop the SQL service, and then override the original data file back. Thereafter, walk according to the steps of the Yihong Mon.
However, it is also unfortunate that other steps are very successful in addition to step 2. Unfortunately, after restarting SQL Server, this application database is still quilted!
However, let me be pleased, after doing this, it is able to select data, let me take a breath. However, when the component uses the database, the report says: "An error occurred: -2147467259, Begin Transaction cannot be run in the database 'xxx' because the database is in an evasive recovery mode."
?
Final successful recovery
Setting up the database for emergency mode
ü ???????? stop SQL Server service;
ü ???????? Remove the data file xxx_data.mdf of the application database; u ???????? Re-establish a database xxx;
ü ???????? stop SQL service;
ü ???????? Remove the original data file back;
ü ???????? Run the following statement to set the database to emergency mode;
??? Run "Use Master
Go
sp_configure 'allow updates', 1
Reconfigure with override
Go "
Results of the:
DBCC is executed. If DBCC outputs an error message, contact your system administrator.
The configuration option 'allow updates' has been changed from 0 to 1. Run the Reconfigure statement to install.
?
Then run "update sysdatabase set status = 32768 where name = 'xxx'"
Results of the:
(The number of rows affects is 1 line)
?
ü ???????? Restart SQL Server service;
ü ???????? Run the following statement, set the application database to Single User mode;
????? Run "sp_dboption 'XXX', 'Single User', 'True'"
Results of the:
????? The command has been successfully completed.
?
ü ???????? DBCC CHECKDB;
????? Run "DBCC Checkdb ('xxx')"
Results of the:
'Xxx' DBCC results.
'sysObjects' DBCC results.
Object 'sysObjects' has 273 rows, which are located in page 5.
'sysindexes' DBCC results.
Object 'sysIndexes' has 202 rows, which are located in page 7.
'syscolumn' 's DBCC results.
.........
?
ü ???????? Run the following statement to turn off the modification options of the system table;
????? Run "sp_resetstatus" xxx "
Go
sp_configure 'allow updates', 0
Reconfigure with override
Go "
Results of the:
Update the entry of the database 'xxx' in sysdatabases, mode = 0, status = 28 (status Suspect_bit = 0),
No rows in sysdatabases are updated because the mode and status have been properly reset. There is no error, no changes are made.
DBCC is executed. If DBCC outputs an error message, contact your system administrator.
The configuration option 'allow updates' has been changed from 1 to 0. Run the Reconfigure statement to install.
?
ü ???????? Re-establish another database xxx.lost;
DTS Export Wizard
ü ???????? Run the DTS Export Wizard;
ü ???????? Copy Source Select EmergencyMode's database XXX, import to XXX.LOST;
ü ???????? Select "Copy objects and data between SQL Server database", tried many times, it seems that it is not possible to copy all table structure, but there is no data, no view and store procedure, Moreover, the DTS wizard finally reports the replication failure; ü ???????? So finally select "From the source database replication table and view", but later discovered that only part of the table record can only be copied;
ü ???????? When you choose to "specify the data to be transmitted with a query", which table is missing, which is
ü ???????? View and stored procedures are added to the SQL statement.
?
?? This way, the XXX.LOST database can replace the original application database.
?
?
