Database Reference
In-Depth Information
Restore to a Point in Time
You can restore the database to a point in time using the
STOPAT
option. This option accepts a date/time value or a
variable as a parameter and restores the database to its state as of that time. Alternatively, you can use the
STOPATMARK
and
STOPBEFOREMARK
options, which allow you to restore the database, stopping at a particular LSN or named
transaction.
One common use-case for these options is the recovery of an accidentally dropped object. Let's look at the
example shown in Listing 30-5 and create the database with table
Invoices
, populate it with some data, and perform
a full database backup.
Listing 30-5.
Point in Time restore: Database creation
use master
go
create database MyDB
go
create table MyDB.dbo.Invoices
(
InvoiceId int not null
);
insert into MyDB.dbo.Invoices values(1),(2),(3)
go
BACKUP DATABASE [MyDB]
TO DISK = N'c:\backups\MyDB.bak'
WITH NOFORMAT, INIT,
NAME = N'MyDB-Full Database Backup', STATS = 5
Now let's assume that somebody accidentally dropped the
dbo.Invoices
table using the
DROP TABLE dbo.Invoices
command. If the database is active and other data has been modified over time, the best course of action would be to
restore another copy of the database from the backup to a point in time when the table was dropped and copy the data
from the newly restored to the original database.
As a first step in the recovery process, let's take a backup of the transaction log, as shown in Listing 30-6.
Obviously, in a real system, you should already have taken log backups regularly and this step would not be required.
Listing 30-6.
Point in Time restore: Backing up the log
BACKUP LOG MyDB
TO DISK = N'c:\backups\MyDB.trn'
WITH NOFORMAT, INIT,
NAME = N'MyDB-Transaction Log Backup', STATS = 5
The tricky part is finding the time when the table was dropped. One of the options that you have is analyzing
the system default trace, which captures such events. You can use the
fn_trace_gettable
system function,
as shown in Listing 30-7.