Transactionlog lost

· Uncategorized

So what if you lose the disk that holds your transaction logs – and all database backup files. Of course, there is no DR plan available (if you put transaction log and backup files on the same disk, DR is clearly no priority).

There are 3 options here
Plan A: If your database was stopped in a clean way (eg detach), you can run following:
USE [master]
GO
CREATE DATABASE [TestDB] ON
(FILENAME = N'D:\MSSQL\SQL2008\Data\TestDB - Copy.mdf')
FOR ATTACH_REBUILD_LOG
GO

But what are the chances of this when you disk suddenly decides not to do anything anymore?
Anyway, you’ll soon find out when running previous code, you will get an error like this:

File activation failure. The physical file name "D:\MSSQL\SQL2008\Data\TestDB_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 1813, Level 16, State 2, Line 1
Could not open new database 'Test'. CREATE DATABASE is aborted.

So you have plan B: Restore from backups.
Oops, the backup files were on the same disks. So no plan B.

And so there is a plan C: Repair your DB with data loss. There will be data lost, but that is still better than not having any DB at all.

Step 1: As your DB will be in the Recovery Pending state, you’ll have to bring it into SINGLE_User mode first:
ALTER DATABASE TestDB SET EMERGENCY
go
ALTER DATABASE TestDB SET SINGLE_USER
GO

Step 2: run DBCC CHECKDB, which will automagically generate a transaction log that is missing.
DBCC CHECKDB(TestDB, REPAIR_ALLOW_DATA_LOSS)

Step 3: run CHECKDB again to check for inconsistency.
DBCC CHECKDB(TestDB) WITH NO_INFOMSGS

Step 4: put the DB back into MULTI_USER mode
ALTER DATABASE TestDB SET MULTI_USER

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: