Friday, March 23, 2012

Point-In-Time Restoration Problem

I'm learning how to restore a database to a point-in-time. I set up a
database called DR_TEST and a table called tblTest with a single
varchar column. I back up the database using the following command:
use master
go
BACKUP DATABASE DR_TEST
TO DISK = 'c:\backup\dr_test\dr.bak'
go
Then I run a TSQL script to continuously insert records into the
tblTest table. Using Enterprise Manager I have a scheduled transaction
log backup running every three minutes to c:\backup\dr_test\drLog.bak.
I drop the tblTest table and backup the transaction log using the
following command:
BACKUP LOG DR_TEST
TO DISK = 'c:\backup\dr_test\drLog.bak'
I then run the following commands to restore the database to it's state
at 2:25 PM:
use master
go
RESTORE DATABASE DR_TEST
FROM DISK = 'C:\BACKUP\DR_TEST\dr.bak'
WITH NORECOVERY
go
RESTORE LOG DR_TEST
FROM DISK = 'C:\BACKUP\DR_TEST\drLog.bak'
WITH RECOVERY,
STOPAT = N'11/16/2006 2:25 PM'
go
When I run the log restore I get the following message:
This log file contains records logged before the designated
point-in-time. The database is being left in load state so you can
apply another log file.
I started inserting the data at 2:22 PM and I dropped the table at 2:27
PM. Why would I get this message?
JerrySeems you did several log backups but only restored the very first one...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jerry" <jerryalan@.gmail.com> wrote in message
news:1163710146.977620.47300@.e3g2000cwe.googlegroups.com...
> I'm learning how to restore a database to a point-in-time. I set up a
> database called DR_TEST and a table called tblTest with a single
> varchar column. I back up the database using the following command:
> use master
> go
> BACKUP DATABASE DR_TEST
> TO DISK = 'c:\backup\dr_test\dr.bak'
> go
> Then I run a TSQL script to continuously insert records into the
> tblTest table. Using Enterprise Manager I have a scheduled transaction
> log backup running every three minutes to c:\backup\dr_test\drLog.bak.
> I drop the tblTest table and backup the transaction log using the
> following command:
> BACKUP LOG DR_TEST
> TO DISK = 'c:\backup\dr_test\drLog.bak'
> I then run the following commands to restore the database to it's state
> at 2:25 PM:
> use master
> go
> RESTORE DATABASE DR_TEST
> FROM DISK = 'C:\BACKUP\DR_TEST\dr.bak'
> WITH NORECOVERY
> go
> RESTORE LOG DR_TEST
> FROM DISK = 'C:\BACKUP\DR_TEST\drLog.bak'
> WITH RECOVERY,
> STOPAT = N'11/16/2006 2:25 PM'
> go
> When I run the log restore I get the following message:
> This log file contains records logged before the designated
> point-in-time. The database is being left in load state so you can
> apply another log file.
> I started inserting the data at 2:22 PM and I dropped the table at 2:27
> PM. Why would I get this message?
>
> --
> Jerry
>

No comments:

Post a Comment