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
>

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
>

Point-In-Time Restoration Issue

Hello,

I'm testing "Point In Time" restoration for my system using both Database & Log backup files. (Database backup once a day; Log backup every 4 hours)

When I use T-SQL to perfrom the restoration, I can specify one .BAK file with numerous .TRN files and restore to any 'point of time' with no issue.

However, if I use EM to perform the same restoration, I can only specify one .BAK file with a maximum of two .TRN files (although I can see all the .TRN files) in order to restore the database properly. If I specify more .TRN files, after restoration, my DB will be in 'LOADING' status and can't be used.

Does anyone encounter the same problem before and know what is going on?

Thank You.When restoring a database with Enterprise Manager, make sure the 'Leave Database Operational' is checked.|||you can also always switch the database from "Loading" to "operational" modes with simple tsql command

restore database <Dbname> with recovery

simas|||hi guys,

thanks for the replies. I'll look into it.

btw, I did found out some documentation that closely describe my problem, it's "Microsoft Knowledge Base Article - 319697, FIX: SQL Enterprise Manager Restore to Point in Time Does Not Stop at Requested Time and the Database is Left in a Loading State"

Seems like it's not something new...

Thank You.

Point-in-time recovery using Log file backups newer than full back

Hopefully I can ask this question w/o too much confusion. The example I am
about to give may not be practical, but the answer should help me understand
db and log backups a little better.
Scenario:
Full db backup performed 3 times in a day - morning, afternoon, and evening
(T1, T2, T3 respectively)
Log backup performed in morning (T1) and evening (T3) immediately after the
morning full db backups (no afternoon (T2) log backup)
Then server db drive fails after the evening db and log backups
The evening full backup is unrestorable (bad tape)
The evening log backup is good (stored on another device).
Could I restore the db to the point of failure using the afternoon full db
backup (T2) and the evening log backup (T3)?
db: T1--T2--T3
log: T1--T3
In other words, when restoring the evening log backup, against the afternoon
full db backup, would the restore process read through the evening log backup
to find the transactions begining at T2, or does the log being restored need
to be from a backup that occurs after the full backup was created?
My "guess" is that the restore would read through the T3 log and apply the
transactions that began after the T2 full db backup.If you ask whether you can "skip" a db backup, then the answer it yes. You cannot "skip" a log
backup, though. Perhaps easier with an examples. Say the time goes from top to bottom:
A Db
B Log
C Log
D Db
E Log
F Log
G Db
H Log
I Log
Here are a couple of examples of what you *can* do:
a, b, c, d, e, h, i
d, e, f, h, i
g, h, i
Here's a couple of examples of what you *cannot* do:
a, c, e, f, h, i
a, e, f, h, i
Short answer is that you need an unbroken chain of log backup. Db backups in between do not matter.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"GRakaska" <GRakaska@.discussions.microsoft.com> wrote in message
news:E20FF009-56C4-4B91-A31A-9720FB1404CD@.microsoft.com...
> Hopefully I can ask this question w/o too much confusion. The example I am
> about to give may not be practical, but the answer should help me understand
> db and log backups a little better.
> Scenario:
> Full db backup performed 3 times in a day - morning, afternoon, and evening
> (T1, T2, T3 respectively)
> Log backup performed in morning (T1) and evening (T3) immediately after the
> morning full db backups (no afternoon (T2) log backup)
> Then server db drive fails after the evening db and log backups
> The evening full backup is unrestorable (bad tape)
> The evening log backup is good (stored on another device).
> Could I restore the db to the point of failure using the afternoon full db
> backup (T2) and the evening log backup (T3)?
> db: T1--T2--T3
> log: T1--T3
> In other words, when restoring the evening log backup, against the afternoon
> full db backup, would the restore process read through the evening log backup
> to find the transactions begining at T2, or does the log being restored need
> to be from a backup that occurs after the full backup was created?
> My "guess" is that the restore would read through the T3 log and apply the
> transactions that began after the T2 full db backup.
>|||> A Db
> B Log
> C Log
> D Db
> E Log
> F Log
> G Db
> H Log
> I Log
> Here are a couple of examples of what you *can* do:
> a, b, c, d, e, h, i
> d, e, f, h, i
> g, h, i
I think an error has found it's way in the first row. You can't skip the F,
since that will break the log chain - and you also say that the chain can
not be broken :)
/Sjang|||> I think an error has found it's way in the first row.
Yes, thanks for catching that. It should have been:
a, b, c, e, f, h, i
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Henrik Davidsen" <none@.none.dk> wrote in message news:lBBDj.7440$9m.7234@.fe25.usenetserver.com...
>> A Db
>> B Log
>> C Log
>> D Db
>> E Log
>> F Log
>> G Db
>> H Log
>> I Log
>> Here are a couple of examples of what you *can* do:
>> a, b, c, d, e, h, i
>> d, e, f, h, i
>> g, h, i
>
> I think an error has found it's way in the first row. You can't skip the F,
> since that will break the log chain - and you also say that the chain can
> not be broken :)
> /Sjang
>
>

Point-in-time backup of database & filesystem

We're considering purchasing an application,
which stores some data in the filesystem, and
some data (meta data and links to files) in
MS-SQL.

We need to be able to create a backup wherein
the database and files are "in synch" (in other words,
in a consistent state). We need to maintain the
"referential integrity" between database and files.

What I'm thinking of is something like this:
1. stop the application server
2. set database to readonly (flush updates
from cache to disk)
3. backup database to disk files
4. back up application files and backup files.
(We could use "snapshot" capabilities of our disk
hardware, so that the backup could run against the
snapshot; that would minimize downtime.)
4. set database to read_write; restart app server

Recovery scenario would be:
1. Stop application server.
2. restore application files and database backup files
3. restore database from disk backup
4. restart application server

Is there a simpler way to get a "point in time" backup
of application files and the database?"L. Bertolini" <bertolini.1@.osu.edu> wrote in message news:<c25aq0$59b$1@.charm.magnus.acs.ohio-state.edu>...
> We're considering purchasing an application,
> which stores some data in the filesystem, and
> some data (meta data and links to files) in
> MS-SQL.
> We need to be able to create a backup wherein
> the database and files are "in synch" (in other words,
> in a consistent state). We need to maintain the
> "referential integrity" between database and files.
> What I'm thinking of is something like this:
> 1. stop the application server
> 2. set database to readonly (flush updates
> from cache to disk)
> 3. backup database to disk files
> 4. back up application files and backup files.
> (We could use "snapshot" capabilities of our disk
> hardware, so that the backup could run against the
> snapshot; that would minimize downtime.)
> 4. set database to read_write; restart app server
> Recovery scenario would be:
> 1. Stop application server.
> 2. restore application files and database backup files
> 3. restore database from disk backup
> 4. restart application server
> Is there a simpler way to get a "point in time" backup
> of application files and the database?

The first place to ask would be the vendor - find out what their
recommended backup strategy is, and how they say you should recover in
case of failure. If they can't give you a good answer, then you should
make that fact part of your decision.

The snapshot backup might be a good option if you already have the
hardware to support it - you probably wouldn't need to stop the server
at all in that case.

Simonsql