Database Reference
In-Depth Information
■
the Management studio Database restore ui supports point-in-time recovery. however, it does not allow you to
specify milliseconds in the
STOPAT
value.
Tip
While the default system trace is a very simple option, there is a downside. The time of the event in the trace is
not precise enough, and it could be a few milliseconds apart from the time that you need to specify as the
STOPAT
value. Therefore, there is no guarantee that you would restore the most recent table data at time of deletion. Moreover,
there is a chance that the
DROP OBJECT
event is overwritten or that the trace is disabled on the server.
One of the workarounds available for this is to use an undocumented system function,
fn_dump_dblog
, which
returns the content of the transaction log backup file. You need to find the LSN that belongs to the
DROP TABLE
statement and restore a copy of the database using the
STOPBEFOREMARK
option. Listing 30-9 shows the code that calls
the
fn_dump_dblog
function. Figure
30-3
shows the output of the query.
Listing 30-9.
Point in Time restore: Using the
fn_dump_dblog
function
select [Current LSN], [Begin Time], Operation
,[Transaction Name], [Description]
from fn_dump_dblog
(
default, default, default, default, 'C:\backups\mydb.trn',
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)
where [Transaction Name] = 'DROPOBJ'
Figure 30-3.
Fn_dump_dblog
output
Listing 30-10 shows a
RESTORE
statement that uses the LSN from the output. You should specify the
lsn:0x
prefix
in the
STOPBEFOREMARK
parameter. It tells SQL Server that you are using an LSN in hexadecimal format.
Listing 30-10.
Point in Time restore: Using the STOPBEFOREMARK parameter
RESTORE LOG [MyDBCopy]
FROM DISK = N'C:\Backups\MyDB.trn'
WITH FILE = 1, NORECOVERY, STATS = 5,
STOPBEFOREMARK = 'lsn:0x00000021:000000bb:0004'
Analyzing transaction log records is a tedious and time-consuming job. However, it provides the most accurate
results. Moreover, you can use such a technique when data is accidentally deleted with the
DELETE
statement. Such an
operation is not logged in the system default trace, and analyzing transaction log content is the only option available.
