By Alan Whited


2013-01-23 23:28:20 8 Comments

I'm using this blog on sqlskills.com as a guide to practice finding the relevant LSN in a transaction log backup for point in time recovery.

After backing up the transaction log, I attempt to read it with:

SELECT COUNT(*) FROM fn_dump_dblog (
NULL, NULL, 'DISK', 1, 'D:\TEMP\test_tlog2.bak',
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT);

I get the following error:

Msg 615, Level 21, State 1, Line 1 Could not find database ID 0, name '0'. The database may be offline. Wait a few minutes and try again.

The database id isn't 0, it isn't offline, I don't see any params I'm missing or changes to the function. I can query the database and I can view the tlog backup file from within SSMS backup using the contents option, shows correct media set 1.

Microsoft SQL Server 2008 R2 (SP2) - 10.50.4263.0 (X64) Aug 23 2012 15:56:56 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) (Hypervisor)

Am I missing something fundamental?

2 comments

@Karthick 2014-07-19 11:08:04

@Alan Please check once by disabling the audit.

That server has an AUDIT on a database. If the audit is enabled, you will get that error. If you disable the audit,query will run fine.

@Ivan Stankovic 2013-10-22 17:48:29

You can try the steps as explained here. On the other hand, there is a SQL Server auditing tool ApexSQL Log that can help you in finding the relevant LSN via GUI and without T-SQL use

Disclaimer: I work as a Product Support Engineer at ApexSQL

Related Questions

Sponsored Content

5 Answered Questions

3 Answered Questions

3 Answered Questions

[SOLVED] Read on MDF file failed with reason 15105

1 Answered Questions

1 Answered Questions

[SOLVED] Database Backup compression

1 Answered Questions

[SOLVED] Mirror db from production to test and acceptance nightly or weekly

1 Answered Questions

1 Answered Questions

1 Answered Questions

[SOLVED] Insert to SQL Azure through Linked Server very slow

Sponsored Content