3

Is it possible to create a database where the ldf is more current then the mdf? Or to attach the older (more mature) ldf to the younger mdf (less mature)?

I am working on Steve Stedman's Week 3 Database Corruption Challenge

Scenario

At 2:53pm a full backup was performed. After that full backup, users continued to do work and the database continued to grow.

At 2:54pm a transaction log backup was performed. Followed by more changes to the database.

At 3:01pm another transaction log backup was performed. Followed by more changes to the database.

At 3:12 another transaction log backup was performed. Followed by more changes to the database.

At 3:19 a catastrophic failure occurred causing the operating system running SQL Server to reboot. Let’s blame it on a power surge, or maybe it was just a DBA who accidentally bumped the power cord. I like the power surge idea, so that I don’t get blamed for kicking the power cord.

Initially I restored the full and each t-log one at time, so I could check for corruption and extract the "good" data. This left me with a database current as of the last t-log backup as my most current mdf and an ldf that was a bit older. Then I tried to figure out how to attach the ldf and get the mdf current.

I tried detaching the database and creating it with younger mdf and the older ldf

USE [master]
GO
CREATE DATABASE [CorruptionChallenge3_T3] ON 
( FILENAME = N'E:\SQLServer\Data01\CorruptionChallenge3_T3.mdf' ),
( FILENAME = N'F:\SQLLogs\CorruptionChallenge3_T3_log.LDF' )
 FOR ATTACH
GO
--That did not work, try this
CREATE DATABASE [CorruptionChallenge3_T3]
ON ( FILENAME = N'E:\SQLServer\Data01\CorruptionChallenge3_T3.mdf' )
LOG ON ( FILENAME = N'F:\SQLLogs\CorruptionChallenge3_T3_log.LDF')
GO
--That did not work either

Error

Msg 5173, Level 16, State 2, Line 90
One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files.  If this is an existing database, the file may be corrupted and should be restored from a backup.
Msg 1802, Level 16, State 7, Line 90
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

So then I tried to restore database with NORECOVERY at the end of the 3rd tlog

USE [master]
RESTORE DATABASE [CorruptionChallenge3_T3] FROM  DISK = N'G:\CorruptionChallenge\CorruptionChallenge3_Full.bak' WITH  FILE = 1,  
MOVE N'CorruptionChallenge3' TO N'E:\SQLServer\Data01\CorruptionChallenge3_T3.mdf',  
MOVE N'CorruptionChallenge3_log' TO N'F:\SQLLogs\CorruptionChallenge3_T3_log.LDF',  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [CorruptionChallenge3_T3] FROM  DISK = N'G:\CorruptionChallenge\TransLog_CorruptionChallenge30.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [CorruptionChallenge3_T3] FROM  DISK = N'G:\CorruptionChallenge\TransLog_CorruptionChallenge31.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [CorruptionChallenge3_T3] FROM  DISK = N'G:\CorruptionChallenge\TransLog_CorruptionChallenge32.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
GO

The tried detaching and the database and reattching with the older ldf, and gots lots of errors along the way (and no success)

--Detach (code above)
/*
Removed connections from CorruptionChallenge3_T3database.
Msg 947, Level 16, State 1, Line 68
Error while closing database 'CorruptionChallenge3_T3'. Check for previous additional errors and retry the operation.
*/
--Re_atach with ldf (code above)
/*
Msg 5173, Level 16, State 2, Line 90
One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files.  If this is an existing database, the file may be corrupted and should be restored from a backup.
Msg 1802, Level 16, State 7, Line 90
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
*/
--That did not work, try to put it back with it's own ldf
/*
Msg 1824, Level 16, State 1, Line 90
Cannot attach a database that was being restored.
*/

I searched some more and found the undocumented function called "fn_dblog" but that allows me to read the ldf only after I get it attached, so no help there.

So I peaked at the winning answer by Randolph West which lead me to the post by Paul S. Randal "Disaster recovery 101: backing up the tail of the log" with the note below. As the corrupted database is SQL 2005 and my sandbox is SQL 2016 I have not tried it yet.

Note: This procedure does not work if I try to perform the hack-attach to a more recent version of SQL Server. I tried hacking a 2005 log into a 2008 server and taking the tail-of-the-log backup – which worked fine, but the tail-of-the-log backup could not be used in conjunction with the first set of backups from the 2005 server. The reason for this is that the database version number in the tail-of-the-log backup is 655 (SQL Server 2008) and those for the 2005 backups are 611 (SQL Server 2005). The database doesn’t get upgraded when restoring on the 2008 server until the end of the restore sequence – so as far as the 2008 server is concerned, my 2008 tail-of-the-log backup can’t be used in the restore sequence of a still-2005-really database. Hope that makes sense!

The 'hack-attach' and 'tail-of-the-log backup' is interesting even if I don't get to work on this scenario, I will practice on some more current instances.

If I can't use the tail-of-the-log backup, what other option do I have to get the last transactions out of the older ldf and into my younger mdf?

Optimally the solution can be performed by DBA with SSMS, full server access and out of the box tools.

3

You should always produce a log backup before restoring a database. Call it a tail log backup if you wish. I call it "the last log backup before a restore".

If you can't start your SQL server, meaning the ldf file is isolated, you hack-attach that ldf file into a running SQL Server and do that tail log backup there. Nothing complicated, really, just remember to do this.

You can't in any way let SQL Server combine database files from different point in times (so to speak). It won't let you. It has the most recent LSN number in the header of each files and if those aren't the same, it is a big no-no.

Here's a blog post on the subject, I wrote a long time ago: http://sqlblog.karaszi.com/restore-database-to-the-point-of-disaster/ .

  • Thank you, it is interesting that your blog predates the Paul Randle blog in my question by a couple of months. – James Jenkins Jan 22 at 12:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.