Sometimes we need to deal with unfortunate and unplanned events like: hardware failure, human mistakes that lead to a SUSPECT database. Let’s face it, this is the moment when the management actually values the fact that he hired a DBA. Usually these unforgettable moments, either makes you a hero or…. you could search for another job. Let’s be heroes instead and make sure that you did all that you could to recover from some catastrophic events. There are more situations that I can think of, but I will describe some of them which can come on handy when it happens. Let’s think of it like a disaster test. It is always easier to treat disaster recoveries when you have tested before similar situations.
- Denial of access to a database resource by the operating system
- Anti-virus/3rd party software blocking access
- The hardware/network failure or leading to corruption of database file(s)
Any of these factors affect the database files: Data file becomes corrupted, Log file becomes corrupted or both are corrupted.
If the database can’t be recovered after failure it will be marked as Suspect and you can’t bring ONLINE to users until you fix the SUSPECT state.
You’ll get an Error when accessing the Suspect database:
Database 'recovery_test_1' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details. (Microsoft SQL Server, Error: 945
Next take a look in the SQL Server Log file and you will see the reason the database could not be opened.
When the Data File is corrupted or missing the error will look like this:
Message FCB::Open failed: Could not open file C:Program FilesMicrosoft SQL Server MSSQL10.TESTMSSQLDATArecovery_test_simple.mdf for file number 1. OS error: 2(failed to retrieve text for this error. Reason: 15105).
In this case you absolutely need a recent Full backup and a couple of Log backups for Full or Bulk-Logged Recovery models. The Data File contains the actual Data, all tables, Indexes, Procedures etc.
The things are quite simple:
- Delete the suspect database
- Restore the most recent Full backup with RECOVERY option and that’s it.
You would want to recover the Suspect database to the last point of failure including the last transactions that where not backed up in a log backup.
For this to work you need a Full backup and at least one valid Log backup
Here is how I will simulate the crash and recovery of a database: I will create a new database, take a Full backup, a log backup, make some changes, put the database Offline, simulate the crash by deleting the data file and then try to bring it Online.
-- Create database CREATE DATABASE [recovery_test_1] -- Create a Test table CREATE TABLE Test1 (a1 INT IDENTITY, b2 nvarCHAR (100)); GO -- Take a full backup BACKUP DATABASE recovery_test_1 TO DISK = 'C:TESTSFull_recovery_test_1.bck' WITH INIT; GO -- Take a Log backup BACKUP LOG recovery_test_1 TO DISK = 'C:TESTSTran_recovery_test_1.trn' WITH INIT; GO -- Make some transactions INSERT INTO Test1 VALUES ('record 1'); INSERT INTO Test1 VALUES ('record 2'); INSERT INTO Test1 VALUES ('record 3'); INSERT INTO Test1 VALUES ('record 4');
-- Simulate failure ALTER DATABASE recovery_test_1 SET OFFLINE -- Physically Rename or Delete DATA File of recovery_test_1 database -- Try to re-open the database ALTER DATABASE recovery_test_1 SET ONLINE; Error: Msg 5120, Level 16, State 101, Line 2 Unable to open the physical file "C:Program FilesMicrosoft SQL Server MSSQL10.TESTMSSQLDATArecovery_test_simple.mdf". ALTER DATABASE statement failed. -- Check database status SELECT DATABASEPROPERTYEX ('recovery_test_1', 'STATUS') AS 'Status'; Status -------- SUSPECT
First thing to do is to immediately try to backup the tail-of-log.
BACKUP LOG recovery_test_1 TO DISK = 'C:TESTSTran_recovery_test_1_2.trn' WITH INIT, NO_TRUNCATE; GO Results: Processed 1 pages for database 'recovery_test_1', file 'recovery_test_simple_log' on file 1. BACKUP LOG successfully processed 1 pages in 0.037 seconds (0.211 MB/sec).
OK, so now I have all that I need to make a successful recovery: the Full backup + 2 Log Backups (one made previously the crash and of the tail-of-log).
- Drop Database:
- Remove the original Log file because the Drop will not do it.
- I need to find out the logical names of the crashed database from the full backup. I can’t do a restore if I don’t know them.
- Restore Full backup with NORECOVERY option:
- Restore 1′st and 2′nd Log backups with NORECOVERY and sequential log with RECOVERY option:
Drop database recovery_test_1
RESTORE FILELISTONLY FROM DISK = 'C:TESTSFull_recovery_test_1.bck'
RESTORE DATABASE recovery_test_1 FROM DISK = N'C:TESTSFull_recovery_test_1.bck' WITH FILE = 1, MOVE N'recovery_test_simple' TO N'C:Program FilesMSSQLDATArecovery_test_simple.mdf', MOVE N'recovery_test_simple_log' TO N'C:Program FilesMSSQLDATArecovery_test_simple_log.ldf', NORECOVERY, NOUNLOAD, STATS = 10 GO Results: Processed 176 pages for database 'recovery_test_1', file 'recovery_test_simple' on file 1. Processed 1 pages for database 'recovery_test_1', file 'recovery_test_simple_log' on file 1. RESTORE DATABASE successfully processed 177 pages in 0.279 seconds (4.956 MB/sec).
RESTORE LOG recovery_test_1 FROM DISK = N'C:TESTSTran_recovery_test_1.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10 GO RESTORE LOG recovery_test_1 FROM DISK = N'C:TESTSTran_recovery_test_1_2.trn' WITH FILE = 1, RECOVERY, NOUNLOAD, STATS = 10 GO
Hooray… the database is recovered and a select on Test1 table reveals the last rows inserted before the crash:
a1 b2 -------------- 1 record 1 2 record 2 3 record 3 4 record 4