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.
