Restoring from latest .ldf AND old .bak backup

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Restoring from latest .ldf AND old .bak backup

Author

Topic

Alto005
Starting Member

3 Posts

Posted - 2008-10-19 : 04:15:31

STORY
There was a database called "Goldmine" on SQL 2000 server, SP4 over XP Pro. It had two files:
D:\MSSQL\Data\Goldmine.mdf
D:\MSSQL\Data\Goldmine_Log.ldf

Now there is only:
1) Backup of the entire database "Goldmine200809150200.bak" dated Sept 15, which is too old to restore for end-users.
2) A copy of the current log file "Goldmine_Log.ldf", dated Oct 19.

FACTS
1. Yesterday afternoon I discovered that weekly backups didn't happen for the last 4 weeks (last one is "Goldmine200809150200.bak" dated Sept 15). REASON - Log file grew to > 34GB which took all the HD space.
2. Log was set to be kept in "Full" mode, and was not shrunk.
3. The goal was to reduce the space by removing the old .ldf file and recreating the new one.
4. I followed advice by "Ken Marshall" on http://forums.frontrange.com/viewtopic.php?p=201225&highlight=&sid=41902b790386c4f8cffb505cc03456aa
and
a) Stopped SQL Server
b) Copied "Goldmine_Log.ldf" to a safe location.
c) Changed Recovery model from Full to Simple
3. Started SQL server. No log file was created according to Step 4 from instructions. When I went to Enterprise Manager to see if Enterprise Manager recreated log file, it showed "Goldmine (suspect)" - (no items)"
4. Then I did a huge mistake - I deleted database "Goldmine" in Enterprise Manager, to create a new one and attach mdf file, because once in the past, deletion of the server did not result in deletion of data files. I was mistaken, Enterprise Manager permanently deleted "Goldmine.mdf". I wish I knew it, - I would create a log file manually.

RECOVERY ATTEMPTS.
6. System restore didn't help, undelete tools (I tried 4 different ones like) could not locate "Goldmine.mdf" which was 6Gb in size. Undelete efforts didn't help at all. Looks like "Goldmine.mdf" is gone forewer.
7. I tried to create a new database calleld "Test" in the enterprise manager, restored "Goldmine200809150200.bak", then stopped the server, deleted Test_Log.ldf, renamed "Goldmine_Log.ldf" to "Test_Log.ldf" and ran a query "Backup Log Test to Disk = 'C:\MS_SQL_TEMP\Test_log.DAT\' With NO_TRUNCATE" but it resulted in an error "Server: Msg 3013, Level 16, State 1, Line 1 BACKUP LOG is terminating abnormally. Device activation error. The physical file name 'C:\MS_SQL_TEMP\Test_Log.LDF' may be incorrect."

GOAL
To recover Goldmine.mdf to as much as possible close state as of October 19th using only:

1) Backup of the entire database "Goldmine200809150200.bak" dated Sept 15, which is too old to restore for end-users.
2) A copy of the current log file "Goldmine_Log.ldf", dated Oct 19.

How can I get the DB back up again - with only using the old .bak and current .ldf?

Please help or I'm in big, big trouble...

David

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-10-19 : 04:49:45

Since the database has been dropped, probably not. Had you not deleted the mdf, it would have been possible to move the log file back and the DB would have come up fine. There was no need to recreate the ldf to recover space. You could have done a log backup then shrunk the log, or you could have done a log backup with truncate only and then shrunk the log. SQL won't rebuild the log if it finds that a DB doesn't have one. The only time it will rebuild the log is if you attach the mdf and specify rebuild log.

Try this, but no guarantees at all...

Create a database with the files
D:\MSSQL\Data\Goldmine.mdf
D:\MSSQL\Data\Goldmine_Log.ldf

Shut down SQL, replace the Goldmine_Log.ldf file with the one you have saved and delete the mdf. Bring SQL up. It will mark the DB suspect.
Run the following
Backup Log Goldmine to Disk = 'C:\MS_SQL_TEMP\Goldmine_TailLog.trn' With NO_TRUNCATE
If that works Restore the backup that you have WITH NORECOVERY and then try to restore the log backup. It will only work if there have been no log backups, no log truncations and no switch to simple recovery and back since before the full backup you have.

If it doesn't work, then Sept 15 is the latest you're going to be able to get the DB back to.

--
Gail Shaw
SQL Server MVP

Alto005
Starting Member

3 Posts

Posted - 2008-10-19 : 07:30:27

Gail, thanks for the quick response

I created a new database "Goldmine" with
C:\MS_SQL_TEMP\Goldmine_Log.mdf
C:\MS_SQL_TEMP\Goldmine_Log.ldf

and followed instructions on http://support.microsoft.com/default.aspx?scid=kb;en-us;253817
which is the same advice as on http://msdn.microsoft.com/en-gb/library/aa176757(SQL.80).aspx
and ran query
__________
Backup Log Goldmine to Disk = 'C:\Goldmine2.trn' With NO_TRUNCATE
__________
Received this error:
__________
Server: Msg 3447, Level 16, State 1, Line 1
Could not activate or scan all of the log files for database 'Goldmine'.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.
Device activation error. The physical file name 'C:\MS_SQL_TEMP\Goldmine_Log.ldf' may be incorrect.
__________

Any ideas on what else can I try?

David

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-10-19 : 10:13:45

Is the log file at that location?
What files exist in the C:\MS_SQL_TEMP\ directory?

You may want to try a log reader. Look at the one RedGate has. It's free and it reads up to SQL 2000 DBs. I don't know if it'll work on a log file alone, or if it needs the DB to be usable. If you can't do the log backup though, it is your only chance

--
Gail Shaw
SQL Server MVP

Alto005
Starting Member

3 Posts

Posted - 2008-10-20 : 03:50:52

1. The dummy database I'm playing with is located in the same folder where the original database files were.
2. There are no other files in there.
3. I managed to make a log backup D:\Goldmine2_Good.trn, but
a) its only 780kb
b) "Processed 1 page in 1 sec"

Question #1. Can a backup of a 34GB .lgf be only 780Kb and process 1 page?

I'm currently trying to execute this query:
RESTORE DATABASE Goldmine
FROM DISK = 'C:\MS_SQL_TEMP\Goldmine_db_200809070200.BAK'
With Replace, NORECOVERY
Go

Restore LOG Goldmine
From DISK = 'D:\Goldmine2_Good.trn'
WITH RECOVERY
Go

Ended up with an error:

Processed 724864 pages for database 'Goldmine', file 'Goldmine_Data' on file 1.
Processed 4 pages for database 'Goldmine', file 'Goldmine_Log' on file 1.
RESTORE DATABASE successfully processed 724868 pages in 561.734 seconds (10.571 MB/sec).
Server: Msg 4305, Level 16, State 1, Line 2
The log in this backup set begins at LSN 122844000013869300001, which is too late to apply to the database. An earlier log backup that includes LSN 122767000000010300001 can be restored.
Server: Msg 3013, Level 16, State 1, Line 2
RESTORE LOG is terminating abnormally.

LostProcessed 724864 pages for database 'Goldmine', file 'Goldmine_Data' on file 1.
Processed 4 pages for database 'Goldmine', file 'Goldmine_Log' on file 1.
RESTORE DATABASE successfully processed 724868 pages in 561.734 seconds (10.571 MB/sec).
Server: Msg 4305, Level 16, State 1, Line 2
The log in this backup set begins at LSN 122844000013869300001, which is too late to apply to the database. An earlier log backup that includes LSN 122767000000010300001 can be restored.
Server: Msg 3013, Level 16, State 1, Line 2
RESTORE LOG is terminating abnormally.

Re: log readers - couldn't make it work yet. I tried Red-gate and ApexSQL log reader, however they only allowed connecting to an existing good database (when 'suspect' or 'emergency' - they don't connect).

Question #2. Is there a way to connect a Log Reader to a single .ldf file? Or at least to a newer .ldf and older .mdf with table structure unchanged, only records added/deleted?

David

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-10-20 : 04:30:11

quote:
Originally posted by Alto005
Question #1. Can a backup of a 34GB .lgf be only 780Kb and process 1 page?

Unlikely. It sounds like the log has been truncated at some point.

quote:
The log in this backup set begins at LSN 122844000013869300001, which is too late to apply to the database. An earlier log backup that includes LSN 122767000000010300001 can be restored.
Server: Msg 3013, Level 16, State 1, Line 2
RESTORE LOG is terminating abnormally.

Yup. The log's been truncated sometime between the full backup and the log backup. There's no way you'll get that to apply to the full that you have now.

quote:
Question #2. Is there a way to connect a Log Reader to a single .ldf file? Or at least to a newer .ldf and older .mdf with table structure unchanged, only records added/deleted?

Don't know, however as your saw with the log backup, the data you want isn't in the log any longer. There's nothing left for the log readers to recover.

You can't mix and match mdf and ldf files. SQL will know that they don't belong together.

I'm afraid at this point that your Sept 15 database is the best you're going to be able to do.

--
Gail Shaw
SQL Server MVP

homebrew
Posting Yak Master

114 Posts

Posted - 2008-10-21 : 16:55:15

Aren't there 3rd party tools to recover deleted files ?? (providing the disk space hasn't been overwritten)

Now, set up notifications on your backup jobs so that if they fail, you will get notified on day 1.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-10-21 : 17:47:15

It is highly unlikely that you can recover the deleted database files, because probably at least some of the disk allocation units have been reused by new files.

Work with the backup you have to recover the data that you can recover.

You should encourage your company to get the services of a DBA to prevent things like this from happening.

CODO ERGO SUM

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-10-22 : 02:54:09

quote:
Originally posted by homebrew

Aren't there 3rd party tools to recover deleted files ?? (providing the disk space hasn't been overwritten)

Already tried and failed.

quote:
System restore didn't help, undelete tools (I tried 4 different ones like) could not locate "Goldmine.mdf" which was 6Gb in size. Undelete efforts didn't help at all. Looks like "Goldmine.mdf" is gone forewer.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources