Database Reference
In-Depth Information
Listing 30-7.
Point in Time restore: Analyzing the system trace
declare
@TraceFilePath nvarchar(2000)
select @TraceFilePath = convert(nvarchar(2000),value)
from ::fn_trace_getinfo(0)
where traceid = 1 and property = 2
select
StartTime
,EventClass
,case EventSubClass
when 0 then 'DROP'
when 1 then 'COMMIT'
when 2 then 'ROLLBACK'
end as SubClass
,ObjectID
,ObjectName
,TransactionID
from ::fn_trace_gettable(@TraceFilePath, default)
where EventClass = 47 and DatabaseName = 'MyDB'
order by StartTime desc
As you can see in Figure
30-2
, there are two rows in the output. One of them corresponds to the time when the
object was dropped. The other one relates to the time when the transaction was committed.
Figure 30-2.
Output from the default system trace
You can use the time from the output to specify the
STOPAT
parameter of the
RESTORE
command, as shown in
Listing 30-8.
Listing 30-8.
Point in Time restore: Using the STOPAT parameter
RESTORE DATABASE [MyDBCopy]
FROM DISK = N'C:\Backups\MyDB.bak'
WITH FILE = 1,
MOVE N'MyDB' TO N'c:\db\MyDBCopy.mdf',
MOVE N'MyDB_log' TO N'c:\db\MyDBCopy.ldf',
NORECOVERY, STATS = 5;
RESTORE LOG [MyDBCopy]
FROM DISK = N'C:\Backups\MyDB.trn'
WITH FILE = 1, NORECOVERY, STATS = 5,
STOPAT = N'2013-12-07T12:07:02.633'
;
RESTORE DATABASE [MyDBCopy] WITH RECOVERY
