Daily Archives: November 18, 2010

SQL 2005/2008 Database Recovery procedure – Data File deleted (Part 2)

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:
  2.  

    Drop database recovery_test_1
  3. Remove the original Log file because the Drop will not do it.
  4.  

  5. 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.
  6.  

    RESTORE FILELISTONLY FROM DISK = 'C:TESTSFull_recovery_test_1.bck'
  7. Restore Full backup with NORECOVERY option:
  8.  

    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).
  9. Restore 1’st and 2’nd  Log backups with NORECOVERY and sequential log with RECOVERY option:
  10.  

    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

Next ->> Part 3

SQL 2005/2008 Database Recovery Procedure – Recovery Models (part 1)

When you create a new database and prepare the environment for a certain application to be deployed, you must take into consideration the database recovery model configuration. Each new database created will have the same Recovery model set to model system database. model database works as a template for each new created database. The default setting is “Full”.

The first thing to do is to decide whether you need Full, Simple or Bulk-Logged Recovery Model on your databases. I will not enter in many details about what each model means as there a lot of very good articles describing the pros and cons for each (links to them are posted at the bottom on this post).

I will mainly focus on recovery procedures in specific types of recovery models. They are different and there are some factors that need to be considered before you decide which model to set on your databases.

1. Full Recovery Model

In Full recovery model all transactions are logged and you have full control over your business – human or technical mistakes are not longer a disaster. So, you can recover the database to the last transactional backup log done – be it 30 minutes or 10 minutes ago – depending on your schedules.

In order to be able to do so: Full backups, Differential backups (if you want to reduce the recovery process to a smaller backup chain) and Log backups must be enabled according to specific schedules that best fit your transaction flows. The transaction log needs to be backed up regularly to prevent not only the loss of work but also to reduce the transaction log size.

The recovery process of a database can be done by:

  1. Recovering the last Full backup + each one of the Log backups performed since the last Full Backup.
  2. Recovering the the last Full backup + last Differential made after last Full + each one of the Log backups performed after the last Differential Backup.
  3. Alternatively if the database is damaged, corrupted and has entered in suspect state you can recover the database to the last transaction from the current transaction log (not-backed up). This can be done by backing up the tail-of-the-log if possible and try applying the 1’st or 2’nd of the above methods + the tail-of-the-log just backed – up. (see Database Recovery procedure – Data File deleted (Part 2)

Conclusion: Stick to the Full recovery option if your business is critical and transactions can’t be re-created or restored easily from other tools.

2. Simple Recovery Model

I will keep it simple. Choose this model if your databases are for DEV/Test purposes or…. are Production but not critical ones such as: databases with static data, with data that be re-created or recovered from other tools and mostly databases containing read-only data (data warehouse databases).

You must be sure that your system can leave with a loss of all transactions processed since the last Full or Differential was made. Although Log backups are not possible, I suggest you enable anyway more frequent differentials as these can be really useful for lost wide developments.

The recovery process of a database can be done by:

  1. Recovering the last Full backup + last differential since the last Full Backup.

3. Bulk-Logged Recovery Model

It is quite similar to Full recovery model, with the single difference that in Bulk-Logged recovery model NOT all transactions are fully logged and you CAN’T have full control over your business. Transactions that are minimally logged are: SELECT INTO, bulk-load operations, CREATE INDEX as well as text and image operations are not recoverable. You still need to do full, differential and transaction log backups.

If a disaster forces you to recover a database set in Bulk-Logged recovery model you will able to do so by:

The recovery process of a database can be done by:

  1. Recovering the last Full backup + last Differential made after last Full + each one of the Log backups performed since the last Full Backup that DO NOT contain any bulk- logged transaction. In other words, you will be able to recover completely the database to the last log backup made only if it’s not containing bulk activity since the last Full backup made. If there was, you will be able to recover the database using log backups only to the beginning of that log backup which has bulk activity.
  2. Contrary to Full recovery model there are exceptions when you can backup the tail-of-log in order to completely recover the database to the last transaction made.
  • If there has been a bulk-log operation performed in the BULK-LOGGED recovery model since the last log backup made – in that case a tail-of-the-log backup is not possible at all, and you’ll have lost all transaction log generated since the last log backup.
  • If you are lucky and there are no bulk-logged operations performed in the Bulk-Logged recovery model since the last log backup made – in that case a tail-of-the-log backup is possible, but if there are other bulk-logged operations in some of the previous log backups since the last Full – the tail-of-log can’t be used in a sequential restore.
  • You can backup the tail-of-log and use it only if there are no bulk-logged operations caught in any of the needed previous log backups and non in the current not-backed up transaction log.

Conclusion: Why you should use Bulk-Next Logged recovery model? If you have a big amount of bulk insert operations that are scheduled at specific hours (nightly) the bulk-logged recovery model may be the best choice. Do a Full backup after the Bulk insert and you will have a good backup log chain to recover from until the next bulk operation.

Any option you choose be sure you choose it wisely and plan your recovery options accordingly. In part 2 and part 3, I will simulate several recovery methods considering different scenarios.

Log File Usage – in Full and Simple Recovery Model

I heard many times people complaining that their database log file is growing huge even if the database is in Simple recovery model. It is absolutely normal because the SQL transaction mechanism (ACID) doesn’t change when recovery model is changed. In order to keep a transaction atomic transaction log will be used no matter which recovery model you use. Atomicity refers to the idea that a transaction needs to be processed completely or not at all. In other words, if a transaction fails at any point in the process, the entire transaction must be rolled back and the log is the place where all modifications are recorded.

At each DML statement (INSERT / DELETE / UPDATE) the transaction log will be used. How? At each data modification statement, SQL will read the data pages affected by the modification into buffer cache, updates with new values also in Buffer cache and records the modification into the Log file. This is why the Log is used and you can see big Log growth during a transaction.

In Simple recovery model – when a Checkpoint occurs (usually an SQL internally managed operation) – the dirty data pages (which were modified in buffer cache) are written (flushed) to disk. Also at checkpoint the inactive part (not used by any transaction) of the log is truncated making the Transaction log re-usable for other transactions. After the log was truncated, the Log file than can be shrinked in order to reduce the physical size if needed.

In Full recovery model, the log is truncated only after a log backup. The checkpoint does not truncate the inactive part of the log file. This is the main difference between Full and Simple recovery mode. A good start in understanding better all this can serve the following articles:

  1. MSDN article – Transaction Log Truncation
  2. Louis Nguyen article – SQL Server Database Engine Basics
  3. SQL Server Performance blog –Database Recovery Models in SQL Server


Next ->> Part 2