SQL 2005/2008 Database Recovery Procedure – Log File Deleted (Part 3)

I will continue in this part, detailing the case of a database failure with SUSPECT status when the Log file is corrupted or deleted. As I mentioned in part 2,  you can easily find out why your database can’t be successfully recovered and is marked with SUSPECT status from the SQL Server Log file. The error will point to the exact problem – whether data file (.mdf) or log file (.ldf) is inaccessible. In part 2, I described the disaster recovery method if you lost your data file (.mdf). In that case you absolutely needed at least a Full backup to recover the database for all types of recovery models (SIMPLE, FULL, BULK-LOGGED).

Things are different if you lost only the Log file. If you have a Full backup and one/more Log backups than the recovery process for databases set in Simple/Full/Bulk-Logged recovery model is the same as in part 2 except that for Full/Bulk-Logged models you can’t backup the tail of the log it being corrupted or deleted.

If you don’t have a Full backup and your Log file can’t be opened but the Data file is intact, then – good news… you can bring the database online for users with minimal loss of data. The main idea is that you can only recover the database to a partially consistent state – having lost every transaction from the Log File which where not written to disk (after a Checkpoint) before the disaster.

So let’s have some fun recovering a SUSPECT database with a corrupted Log file.
 
1. Prerequisites For A Database Failure: 

-- Create database
CREATE DATABASE [recovery_test_2] 

-- Create Test tables
CREATE TABLE Products (Product nvarchar(100), Price INT);
GO

CREATE TABLE Invoices ([Id] INT IDENTITY, Product nvarchar (100), Value INT);
GO

-- Make some transactions

INSERT INTO Products VALUES ('T-shirt',200);
INSERT INTO Products VALUES ('Boots', 150);
INSERT INTO Products VALUES ('Skirt', 500);
INSERT INTO Products VALUES ('Belt', 300);

-- Make one uncommitted transaction written to Disk with forced Checkpoint. 

Begin transaction
Update Test2 set price= 1000 where product= 'Boots'
Checkpoint
Go

-- From another user session make one uncommitted transaction with
   no forced Checkpoint.

Begin transaction
INSERT INTO Invoices VALUES ('T-shirt', 200);
INSERT INTO Invoices VALUES ('Skirt', 500);
Go

 
I just updated the price of Boots and sold two products.
 
2. Provoke Database Failure: 

-- Simulate failure 

SHUTDOWN WITH NOWAIT;
GO 

-- Physically Rename or Delete LOG File of recovery_test_2 database  

-- Re-start SQL Server

-- Check database status

SELECT DATABASEPROPERTYEX ('recovery_test_2' , 'STATUS') AS 'Status';

Status
--------
SUSPECT

-- Try to re-open the database

ALTER DATABASE recovery_test_2 SET ONLINE

Error:

File activation failure. The physical file name "C:Program Files
Microsoft SQL ServerMSSQL10.TESTMSSQLDATArecovery_test_2_log.ldf"
may be incorrect. The log cannot be rebuilt because there were open
transactions/users when the database was shutdown, no checkpoint
occurred to the database, or the database was read-only.
This error could occur if the transaction log file was manually
deleted or lost due to a hardware or environment failure.

Msg 945, Level 14, State 2, Line 1
Database 'recovery_test_2' cannot be opened due to inaccessible files or
insufficient memory or disk space.  See the SQL Server errorlog for details.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
ALTER DATABASE statement failed.
 

 
3. Recover The Database:
 
As a GOLD rule is to never detach a SUSPECT database if you don’t have backups to recover from lately. In SQL 2008 there is a very wise restriction which prohibits to detach a SUSPECT database. In SQL 2005 and SQL 2000 however this is possible but strongly NOT RECOMMENDED . If you do this, even if your Log file is missing and is not actually corrupted – you will not be able to use the ATTACH_REBUILD_LOG option on CREATE DATABASE because SQL knows there where 2 active transactions and can’t create a new Log file.
You will have to fool SQL into believing that it is attached by creating a new dummy database (same database name, same file locations and sizes) –> bring database offline –> delete dummy’s database files –> replace them with the original Data Files (Log file is already missing) –> bring database online –> and obtain again the SUSPECT database’s status. 

Now comes the fixing part. 

The only way to access a SUSPECT database is to use EMERGENCY mode.

ALTER DATABASE recovery_test_2 SET EMERGENCY;
GO
ALTER DATABASE recovery_test_2 SET SINGLE_USER;
GO
DBCC CHECKDB (recovery_test_2, REPAIR_ALLOW_DATA_LOSS)
WITH NO_INFOMSGS, ALL_ERRORMSGS;
GO

Result:
File activation failure. The physical file name "C:Program Files
Microsoft SQL ServerMSSQL10.TESTMSSQLDATArecovery_test_2_log.ldf"
may be incorrect.
The log cannot be rebuilt because there were open transactions/users
when the database was shutdown, no checkpoint occurred to the database,
or the database was read-only. This error could occur if the transaction
log file was manually deleted or lost due to a hardware or environment failure.

Warning: The log for database 'recovery_test_2' has been rebuilt.
Transactional consistency has been lost. The RESTORE chain was broken,
and the server no longer has context on the previous log files, so you
will need to know what they were. You should run DBCC CHECKDB to validate
physical consistency. The database has been put in dbo-only mode. When
you are ready to make the database available for use, you will need to
reset database options and delete any extra log files.

SELECT DATABASEPROPERTYEX ('recovery_test_2' , 'STATUS') AS 'Status';

Status
--------
ONLINE

 
Now let’s take a look at Products and Invoices tables:
 

-- Set database with multi-user access 
ALTER DATABASE recovery_test_2 SET MULTI_USER;

select * from Products

Product	Price
--------------------
T-shirt	        200
1000	        150
Boots	        1000
Belt	        300

select * from Invoices

ID	Product	Value
----------------------------
(0 row(s) affected)

It can be noticed that ‘Boots’ product has the price changed to 1000, while the transaction was not committed before the crash but it was however written to disk. It means that SQL had no information stored in the newly created Log file about the transaction executed before the crash in order to decide whether to Rollback or to Roll-forward. So, transactions that where written to disk (due to self manageable SQL process known as Checkpoint) but not committed must be manually identified and rolled forward or rollbacked by the functional teams. It is a risk that must be considered while recovering from disaster with a lost Log file using the described method. Sometimes this is the only option you have.

On the other hand the insert before the crash into Invoices table that was not written to disk and not committed, can’t be found in the table after recovery. It was contained only in the corrupted Log file. The recovery process without backups has good parts and bad parts. This is the reason, why Full, Differential and Log backups are a mandatory step to do in the big chain of administrative tasks. 

Previous <<- Part 2

  1. Hi,

    I read your SQL Server database in Suspect mode blogs and it’s really good. You have mentioned the 3 types of possibilities for suspecting database but is any other possibilities to suspect the database.

  2. Hi yrushka
    it was so good :)

  3. Really it was a nice article which describes brilliantly the possibilities of SQL database in suspect mode. I also suffer this type of complicated situation in order to get recover SQL database i use MS SQL Server database recovery software which is a trustful and reliable solution for SQL database recover visit here to know more http://www.sqlrepairtool.org

  4. Great work, thank you very much. Using your procedures I was able to successfully rebuild the WSS_Content_log.LDF of my Sharepoint Service which was 24 GB before this operation and now is 500k. I had to do this because the hard drive was full. Now everythins works again and I have plenty of hard drive space left :grin:

  5. Hi Irina,

    I read two posts regarding db corrupt,(part1 & 2)..it’s really good content .helpfull

    thank u

Leave a Comment


NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>