Irina Tudose's blog

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.

Possible factors for a database to be marked SUSPECT:

• 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.

1. Simple Recovery model

The things are quite simple:

• Delete the suspect database
• Restore the most recent Full backup with RECOVERY option and that’s it.

2. Full Recovery model

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.

2.1. Prerequisites For A Database Failure:
 

-- 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');

2.2. Provoke Database Failure:
 

-- 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
 

2.3. Recover The Database:
 
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).

1. Drop Database:

 

Drop database recovery_test_1

2. Remove the original Log file because the Drop will not do it.

 

3. 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 FILELISTONLY FROM DISK = 'C:TESTSFull_recovery_test_1.bck'

4. Restore Full backup with NORECOVERY option:

 

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).

5. Restore 1′st and 2′nd  Log backups with NORECOVERY and sequential log with RECOVERY option:

 

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

Previous <<- Part 1