backup and restore trouble.

Hello,
We want to do a restore to a point of time.
With recovery mode on full.
Can one do a restore to a point of time before the last full backup
with a transaction log backup made after the full backup ?
Sequence.
(full recovery mode, SQL-server 7. I think.).
a. Somewere in the past a full backup is made.
b. An error is made.
c. A full backup is made.
d. A transaction log backup is made.
We want to restore to a point in time just before
the error (b.) is made. Is this possible ?
(I have got the BOL (from 7) and inside from Kalen but
can not find the anwsers there).
Ben Brugman.> We want to do a restore to a point of time.
> With recovery mode on full.
> Sequence.
> (full recovery mode, SQL-server 7. I think.).
There is no FULL recovery mode in SQL 7. In SQL 7 and earlier versions,
the 'trunc. log on chkpt.' database option turned off is similar.
> a. Somewere in the past a full backup is made.
> b. An error is made.
> c. A full backup is made.
> d. A transaction log backup is made.
> We want to restore to a point in time just before
> the error (b.) is made. Is this possible ?
You can perform point-in-time recovery as follows:
1) Restore full database backup from step 'a' WITH NORECOVERY
2) Restore transaction log backup from step 'd' WITH STOPAT (time
before step 'b' error) and RECOVERY
Note that if you have any other log backups between 'a' and 'b', these
will need to be applied (WITH NORECOVERY) before step '2' above.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"ben brugman" <ben@.niethier.nl> wrote in message
news:bkhf32$mlv$1@.reader10.wxs.nl...
> Hello,
> We want to do a restore to a point of time.
> With recovery mode on full.
> Can one do a restore to a point of time before the last full backup
> with a transaction log backup made after the full backup ?
> Sequence.
> (full recovery mode, SQL-server 7. I think.).
> a. Somewere in the past a full backup is made.
> b. An error is made.
> c. A full backup is made.
> d. A transaction log backup is made.
> We want to restore to a point in time just before
> the error (b.) is made. Is this possible ?
> (I have got the BOL (from 7) and inside from Kalen but
> can not find the anwsers there).
> Ben Brugman.
>|||Greg,
I too have been having problems with the restore to a point in time
and your sample script was useful in trying to come to grips with
this. All this seems to do though , is to restore the datbase to the
state it was in when it was backed up to a_bak1.bak.
I have managed to write a script to add 4 records to the database ,
then do a full backup to a_bak1, then add a few more records then do
another full backup and a transaction log backup. I now want to
restore to a point where I have only entered the first two records,
but all that seems to happen is that I am restored to the point of the
first full backup.
I have attached my script (which is heavily based on the one you
originally posted).
What am I doing wrong ?...it's been driving me mad for days !!
All help gratefully received .
Kind Regards,
Nigel
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message news:<#sdQMP4fDHA.2464@.TK2MSFTNGP09.phx.gbl>...
> Hi Ben.
> Are you sure you're on SQL 7.0? The term "full recovery" only came along
> with SQL 2000...
> Anyway, this should be simple - restore the last good full backup without
> recovering, then apply the transaction log & stopat the appropriate time.
> Below is a demo script that should work on both 7.0 & 2000, whichever you're
> using. Follow it carefully & you should be able to see that this is
> possible. Note that the table t1 is populated with a row, which is then
> deleted between the two full backups. By performing a normal full database
> restore without recovery, then a log restore with recovery, you can get the
> point in time you're after.
> set nocount on
> go
> use master
> go
> create database a
> on (name=a_dat, filename='c:\a_dat.mdf', size=1mb, filegrowth=1mb)
> log on (name=a_log, filename='c:\a_log.ldf', size=1mb, filegrowth=1mb )
> go
> use a
> go
> create table t1 (c1 int)
> create table t2 (restoretime varchar(26))
> go
> /* insert a row into t1. we'll expect to see this row again after restore,
> despite it being deleted before full backup 2 */
> insert into t1 values (1)
> go
> use master
> go
> /* your full backup from whenever */
> backup database a to disk='c:\a_bak1.bak'
> go
> use a
> go
> /* insert a time into t2 that can be read as a stopat time accross batches
> */
> insert into t2 values (convert(varchar(26), getdate(), 9))
> go
> /* delay one minute */
> waitfor delay '00:01:00'
> go
> /* delete the row from t1. This represents the mistake we want to recover
> before.. */
> delete from t1
> go
> use master
> go
> /* your secondary, post mistake full backup */
> backup database a to disk='c:\a_bak2.bak'
> go
> /* your log backup */
> backup log a to disk='c:\a_bak3.bak'
> go
> /* we restore to point in time captured in t2. I'm only using t2 so we could
> record that point in time accross batches for the purposes of this example
> script */
> use a
> declare @.restoretime varchar(26)
> select @.restoretime = min(restoretime) from t2
> use master
> restore database a from disk='c:\a_bak1.bak' with norecovery
> restore log a from disk='c:\a_bak3.bak' with recovery, stopat = @.restoretime
> go
> use a
> go
> /* prove that the deleted row from t1 is restored */
> select * from t1
> go
> /* clean up */
> use master
> go
> drop database a
> go
> HTH
> Regards,
> Greg Linwood
> SQL Server MVP
> "ben brugman" <ben@.niethier.nl> wrote in message
> news:bkhf32$mlv$1@.reader10.wxs.nl...
> > Hello,
> >
> > We want to do a restore to a point of time.
> >
> > With recovery mode on full.
> > Can one do a restore to a point of time before the last full backup
> > with a transaction log backup made after the full backup ?
> >
> > Sequence.
> >
> > (full recovery mode, SQL-server 7. I think.).
> > a. Somewere in the past a full backup is made.
> > b. An error is made.
> > c. A full backup is made.
> > d. A transaction log backup is made.
> >
> > We want to restore to a point in time just before
> > the error (b.) is made. Is this possible ?
> > (I have got the BOL (from 7) and inside from Kalen but
> > can not find the anwsers there).
> >
> > Ben Brugman.
> >
> >|||Hi Nigel.
There's no attachment to your post. I'm not sure, but I think these might be
getting dropped by the news-servers at the moment, so please re-post with
your script in the body of your post & I'll look at the script & try to work
it out for you..
Don't email it to me as I have a strong filter on my email & you're not in
my address book, so you won't get through..
Regards,
Greg Linwood
SQL Server MVP
"NIgel Stallard" <Nigel_Stallard@.hotmail.com> wrote in message
news:26210f56.0310080734.53626df5@.posting.google.com...
> Greg,
> I too have been having problems with the restore to a point in time
> and your sample script was useful in trying to come to grips with
> this. All this seems to do though , is to restore the datbase to the
> state it was in when it was backed up to a_bak1.bak.
> I have managed to write a script to add 4 records to the database ,
> then do a full backup to a_bak1, then add a few more records then do
> another full backup and a transaction log backup. I now want to
> restore to a point where I have only entered the first two records,
> but all that seems to happen is that I am restored to the point of the
> first full backup.
> I have attached my script (which is heavily based on the one you
> originally posted).
> What am I doing wrong ?...it's been driving me mad for days !!
> All help gratefully received .
>
> Kind Regards,
> Nigel
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:<#sdQMP4fDHA.2464@.TK2MSFTNGP09.phx.gbl>...
> > Hi Ben.
> >
> > Are you sure you're on SQL 7.0? The term "full recovery" only came along
> > with SQL 2000...
> >
> > Anyway, this should be simple - restore the last good full backup
without
> > recovering, then apply the transaction log & stopat the appropriate
time.
> > Below is a demo script that should work on both 7.0 & 2000, whichever
you're
> > using. Follow it carefully & you should be able to see that this is
> > possible. Note that the table t1 is populated with a row, which is then
> > deleted between the two full backups. By performing a normal full
database
> > restore without recovery, then a log restore with recovery, you can get
the
> > point in time you're after.
> >
> > set nocount on
> > go
> > use master
> > go
> > create database a
> > on (name=a_dat, filename='c:\a_dat.mdf', size=1mb, filegrowth=1mb)
> > log on (name=a_log, filename='c:\a_log.ldf', size=1mb, filegrowth=1mb )
> > go
> > use a
> > go
> > create table t1 (c1 int)
> > create table t2 (restoretime varchar(26))
> > go
> > /* insert a row into t1. we'll expect to see this row again after
restore,
> > despite it being deleted before full backup 2 */
> > insert into t1 values (1)
> > go
> > use master
> > go
> > /* your full backup from whenever */
> > backup database a to disk='c:\a_bak1.bak'
> > go
> > use a
> > go
> > /* insert a time into t2 that can be read as a stopat time accross
batches
> > */
> > insert into t2 values (convert(varchar(26), getdate(), 9))
> > go
> > /* delay one minute */
> > waitfor delay '00:01:00'
> > go
> > /* delete the row from t1. This represents the mistake we want to
recover
> > before.. */
> > delete from t1
> > go
> > use master
> > go
> > /* your secondary, post mistake full backup */
> > backup database a to disk='c:\a_bak2.bak'
> > go
> > /* your log backup */
> > backup log a to disk='c:\a_bak3.bak'
> > go
> > /* we restore to point in time captured in t2. I'm only using t2 so we
could
> > record that point in time accross batches for the purposes of this
example
> > script */
> > use a
> > declare @.restoretime varchar(26)
> > select @.restoretime = min(restoretime) from t2
> > use master
> > restore database a from disk='c:\a_bak1.bak' with norecovery
> > restore log a from disk='c:\a_bak3.bak' with recovery, stopat =@.restoretime
> > go
> > use a
> > go
> > /* prove that the deleted row from t1 is restored */
> > select * from t1
> > go
> > /* clean up */
> > use master
> > go
> > drop database a
> > go
> >
> > HTH
> >
> > Regards,
> > Greg Linwood
> > SQL Server MVP
> >
> > "ben brugman" <ben@.niethier.nl> wrote in message
> > news:bkhf32$mlv$1@.reader10.wxs.nl...
> > > Hello,
> > >
> > > We want to do a restore to a point of time.
> > >
> > > With recovery mode on full.
> > > Can one do a restore to a point of time before the last full backup
> > > with a transaction log backup made after the full backup ?
> > >
> > > Sequence.
> > >
> > > (full recovery mode, SQL-server 7. I think.).
> > > a. Somewere in the past a full backup is made.
> > > b. An error is made.
> > > c. A full backup is made.
> > > d. A transaction log backup is made.
> > >
> > > We want to restore to a point in time just before
> > > the error (b.) is made. Is this possible ?
> > > (I have got the BOL (from 7) and inside from Kalen but
> > > can not find the anwsers there).
> > >
> > > Ben Brugman.
> > >
> > >|||Hi Greg,
The script is as follows..I know its a terrible script, I'm having to
learn as I go with this..
---
set nocount on
go
use master
create database a
on (name=a_dat, filename='c:\a_dat.mdf', size=1Mb, filegrowth=1mb)
log on (name=a_log, filename='c:\a_log.ldf', size=1mb, filegrowth=1mb)
go
use a
go
create table t1 (c1 int, restoretime varchar(26))
create table t2 (restoretime varchar (26))
go
backup database a to disk='c:\a_bak0.bak'
go
insert into t1 values(1 , convert(varchar(26), getdate() , 9))
insert into t2 values(convert(varchar(26), getdate() , 9))
waitfor delay '00:00:05'
go
insert into t1 values(2 , convert(varchar(26), getdate() , 9))
insert into t2 values(convert(varchar(26), getdate() , 9))
waitfor delay '00:00:05'
go
insert into t1 values(3 , convert(varchar(26), getdate() , 9))
insert into t2 values(convert(varchar(26), getdate() , 9))
waitfor delay '00:00:05'
go
insert into t1 values(4 , convert(varchar(26), getdate() , 9))
insert into t2 values(convert(varchar(26), getdate() , 9))
waitfor delay '00:00:05'
go
use master
go
backup database a to disk='c:\a_bak1.bak'
go
use a
go
insert into t1 values(5 , convert(varchar(26), getdate() , 9))
insert into t2 values(convert(varchar(26), getdate() , 9))
go
waitfor delay '00:00:05'
insert into t1 values(6 , convert(varchar(26), getdate() , 9))
insert into t2 values(convert(varchar(26), getdate() , 9))
go
waitfor delay '00:00:05'
go
use master
go
backup database a to disk='c:\a_bak2.bak'
go
backup log a to disk='c:\a_bak3.bak'
go
use a
go
----
Basically I'm trying to prove to myself that I can decide to restore
back to any particular transaction, which I assume is what restore to
point in time is all about.
I think I understood your original example script, but as far as i
could see there was no need to use the transaction log as the database
would be put back to the desired point in time simply by restore the
first full backup.
Basically I'm trying to prove to myself that I can decide to restore
back to any particular transaction, which I assume is what restore to
point in time is all about.
What I am attempting to do is try to restore to the point just after
the 2nd or 3rd transaction.
Thanks for your interest in my problem, most appreciated..and thanks
for the quick response.
Nigel
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message news:<OvviP5ejDHA.2000@.TK2MSFTNGP12.phx.gbl>...
> Hi Nigel.
> There's no attachment to your post. I'm not sure, but I think these might be
> getting dropped by the news-servers at the moment, so please re-post with
> your script in the body of your post & I'll look at the script & try to work
> it out for you..
> Don't email it to me as I have a strong filter on my email & you're not in
> my address book, so you won't get through..
> Regards,
> Greg Linwood
> SQL Server MVP
> "NIgel Stallard" <Nigel_Stallard@.hotmail.com> wrote in message
> news:26210f56.0310080734.53626df5@.posting.google.com...
> > Greg,
> >
> > I too have been having problems with the restore to a point in time
> > and your sample script was useful in trying to come to grips with
> > this. All this seems to do though , is to restore the datbase to the
> > state it was in when it was backed up to a_bak1.bak.
> >
> > I have managed to write a script to add 4 records to the database ,
> > then do a full backup to a_bak1, then add a few more records then do
> > another full backup and a transaction log backup. I now want to
> > restore to a point where I have only entered the first two records,
> > but all that seems to happen is that I am restored to the point of the
> > first full backup.
> > I have attached my script (which is heavily based on the one you
> > originally posted).
> >
> > What am I doing wrong ?...it's been driving me mad for days !!
> >
> > All help gratefully received .
> >
> >
> > Kind Regards,
> >
> > Nigel
> >
> > "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:<#sdQMP4fDHA.2464@.TK2MSFTNGP09.phx.gbl>...
> > > Hi Ben.
> > >
> > > Are you sure you're on SQL 7.0? The term "full recovery" only came along
> > > with SQL 2000...
> > >
> > > Anyway, this should be simple - restore the last good full backup
> without
> > > recovering, then apply the transaction log & stopat the appropriate
> time.
> > > Below is a demo script that should work on both 7.0 & 2000, whichever
> you're
> > > using. Follow it carefully & you should be able to see that this is
> > > possible. Note that the table t1 is populated with a row, which is then
> > > deleted between the two full backups. By performing a normal full
> database
> > > restore without recovery, then a log restore with recovery, you can get
> the
> > > point in time you're after.
> > >
> > > set nocount on
> > > go
> > > use master
> > > go
> > > create database a
> > > on (name=a_dat, filename='c:\a_dat.mdf', size=1mb, filegrowth=1mb)
> > > log on (name=a_log, filename='c:\a_log.ldf', size=1mb, filegrowth=1mb )
> > > go
> > > use a
> > > go
> > > create table t1 (c1 int)
> > > create table t2 (restoretime varchar(26))
> > > go
> > > /* insert a row into t1. we'll expect to see this row again after
> restore,
> > > despite it being deleted before full backup 2 */
> > > insert into t1 values (1)
> > > go
> > > use master
> > > go
> > > /* your full backup from whenever */
> > > backup database a to disk='c:\a_bak1.bak'
> > > go
> > > use a
> > > go
> > > /* insert a time into t2 that can be read as a stopat time accross
> batches
> > > */
> > > insert into t2 values (convert(varchar(26), getdate(), 9))
> > > go
> > > /* delay one minute */
> > > waitfor delay '00:01:00'
> > > go
> > > /* delete the row from t1. This represents the mistake we want to
> recover
> > > before.. */
> > > delete from t1
> > > go
> > > use master
> > > go
> > > /* your secondary, post mistake full backup */
> > > backup database a to disk='c:\a_bak2.bak'
> > > go
> > > /* your log backup */
> > > backup log a to disk='c:\a_bak3.bak'
> > > go
> > > /* we restore to point in time captured in t2. I'm only using t2 so we
> could
> > > record that point in time accross batches for the purposes of this
> example
> > > script */
> > > use a
> > > declare @.restoretime varchar(26)
> > > select @.restoretime = min(restoretime) from t2
> > > use master
> > > restore database a from disk='c:\a_bak1.bak' with norecovery
> > > restore log a from disk='c:\a_bak3.bak' with recovery, stopat => @.restoretime
> > > go
> > > use a
> > > go
> > > /* prove that the deleted row from t1 is restored */
> > > select * from t1
> > > go
> > > /* clean up */
> > > use master
> > > go
> > > drop database a
> > > go
> > >
> > > HTH
> > >
> > > Regards,
> > > Greg Linwood
> > > SQL Server MVP
> > >
> > > "ben brugman" <ben@.niethier.nl> wrote in message
> > > news:bkhf32$mlv$1@.reader10.wxs.nl...
> > > > Hello,
> > > >
> > > > We want to do a restore to a point of time.
> > > >
> > > > With recovery mode on full.
> > > > Can one do a restore to a point of time before the last full backup
> > > > with a transaction log backup made after the full backup ?
> > > >
> > > > Sequence.
> > > >
> > > > (full recovery mode, SQL-server 7. I think.).
> > > > a. Somewere in the past a full backup is made.
> > > > b. An error is made.
> > > > c. A full backup is made.
> > > > d. A transaction log backup is made.
> > > >
> > > > We want to restore to a point in time just before
> > > > the error (b.) is made. Is this possible ?
> > > > (I have got the BOL (from 7) and inside from Kalen but
> > > > can not find the anwsers there).
> > > >
> > > > Ben Brugman.
> > > >
> > > >|||Hi Greg,
Please ignore my last post. I have sorted out the problems with the
restore to a point in time. I have noticed however that when I try to
restore to a time which is later than the last entry in the
transaction log, the database is left in a loading state. I am using
SQL Server 2000 with SP3A installed, and according to MS knowledgebase
article 319697 this particular issue was fixed with SP3. It also
states that the problem was when using Enterprise Manager to carry out
the restore. I am using a script. Have you come across this problem
even when SP3 has been installed ?
Kind Regards,
Nigel
Nigel_Stallard@.hotmail.com (NIgel Stallard) wrote in message news:<26210f56.0310090259.664b6871@.posting.google.com>...
> Hi Greg,
> The script is as follows..I know its a terrible script, I'm having to
> learn as I go with this..
> ---
> set nocount on
> go
> use master
> create database a
> on (name=a_dat, filename='c:\a_dat.mdf', size=1Mb, filegrowth=1mb)
> log on (name=a_log, filename='c:\a_log.ldf', size=1mb, filegrowth=1mb)
> go
> use a
> go
> create table t1 (c1 int, restoretime varchar(26))
> create table t2 (restoretime varchar (26))
> go
> backup database a to disk='c:\a_bak0.bak'
> go
> insert into t1 values(1 , convert(varchar(26), getdate() , 9))
> insert into t2 values(convert(varchar(26), getdate() , 9))
> waitfor delay '00:00:05'
> go
> insert into t1 values(2 , convert(varchar(26), getdate() , 9))
> insert into t2 values(convert(varchar(26), getdate() , 9))
> waitfor delay '00:00:05'
> go
> insert into t1 values(3 , convert(varchar(26), getdate() , 9))
> insert into t2 values(convert(varchar(26), getdate() , 9))
> waitfor delay '00:00:05'
> go
> insert into t1 values(4 , convert(varchar(26), getdate() , 9))
> insert into t2 values(convert(varchar(26), getdate() , 9))
> waitfor delay '00:00:05'
> go
> use master
> go
> backup database a to disk='c:\a_bak1.bak'
> go
> use a
> go
> insert into t1 values(5 , convert(varchar(26), getdate() , 9))
> insert into t2 values(convert(varchar(26), getdate() , 9))
> go
> waitfor delay '00:00:05'
> insert into t1 values(6 , convert(varchar(26), getdate() , 9))
> insert into t2 values(convert(varchar(26), getdate() , 9))
> go
> waitfor delay '00:00:05'
> go
> use master
> go
> backup database a to disk='c:\a_bak2.bak'
> go
> backup log a to disk='c:\a_bak3.bak'
> go
> use a
> go
> ----
> Basically I'm trying to prove to myself that I can decide to restore
> back to any particular transaction, which I assume is what restore to
> point in time is all about.
> I think I understood your original example script, but as far as i
> could see there was no need to use the transaction log as the database
> would be put back to the desired point in time simply by restore the
> first full backup.
> Basically I'm trying to prove to myself that I can decide to restore
> back to any particular transaction, which I assume is what restore to
> point in time is all about.
> What I am attempting to do is try to restore to the point just after
> the 2nd or 3rd transaction.
> Thanks for your interest in my problem, most appreciated..and thanks
> for the quick response.
> Nigel
>
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message news:<OvviP5ejDHA.2000@.TK2MSFTNGP12.phx.gbl>...
> > Hi Nigel.
> >
> > There's no attachment to your post. I'm not sure, but I think these might be
> > getting dropped by the news-servers at the moment, so please re-post with
> > your script in the body of your post & I'll look at the script & try to work
> > it out for you..
> >
> > Don't email it to me as I have a strong filter on my email & you're not in
> > my address book, so you won't get through..
> >
> > Regards,
> > Greg Linwood
> > SQL Server MVP
> >
> > "NIgel Stallard" <Nigel_Stallard@.hotmail.com> wrote in message
> > news:26210f56.0310080734.53626df5@.posting.google.com...
> > > Greg,
> > >
> > > I too have been having problems with the restore to a point in time
> > > and your sample script was useful in trying to come to grips with
> > > this. All this seems to do though , is to restore the datbase to the
> > > state it was in when it was backed up to a_bak1.bak.
> > >
> > > I have managed to write a script to add 4 records to the database ,
> > > then do a full backup to a_bak1, then add a few more records then do
> > > another full backup and a transaction log backup. I now want to
> > > restore to a point where I have only entered the first two records,
> > > but all that seems to happen is that I am restored to the point of the
> > > first full backup.
> > > I have attached my script (which is heavily based on the one you
> > > originally posted).
> > >
> > > What am I doing wrong ?...it's been driving me mad for days !!
> > >
> > > All help gratefully received .
> > >
> > >
> > > Kind Regards,
> > >
> > > Nigel
> > >
> > > "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:<#sdQMP4fDHA.2464@.TK2MSFTNGP09.phx.gbl>...
> > > > Hi Ben.
> > > >
> > > > Are you sure you're on SQL 7.0? The term "full recovery" only came along
> > > > with SQL 2000...
> > > >
> > > > Anyway, this should be simple - restore the last good full backup
> without
> > > > recovering, then apply the transaction log & stopat the appropriate
> time.
> > > > Below is a demo script that should work on both 7.0 & 2000, whichever
> you're
> > > > using. Follow it carefully & you should be able to see that this is
> > > > possible. Note that the table t1 is populated with a row, which is then
> > > > deleted between the two full backups. By performing a normal full
> database
> > > > restore without recovery, then a log restore with recovery, you can get
> the
> > > > point in time you're after.
> > > >
> > > > set nocount on
> > > > go
> > > > use master
> > > > go
> > > > create database a
> > > > on (name=a_dat, filename='c:\a_dat.mdf', size=1mb, filegrowth=1mb)
> > > > log on (name=a_log, filename='c:\a_log.ldf', size=1mb, filegrowth=1mb )
> > > > go
> > > > use a
> > > > go
> > > > create table t1 (c1 int)
> > > > create table t2 (restoretime varchar(26))
> > > > go
> > > > /* insert a row into t1. we'll expect to see this row again after
> restore,
> > > > despite it being deleted before full backup 2 */
> > > > insert into t1 values (1)
> > > > go
> > > > use master
> > > > go
> > > > /* your full backup from whenever */
> > > > backup database a to disk='c:\a_bak1.bak'
> > > > go
> > > > use a
> > > > go
> > > > /* insert a time into t2 that can be read as a stopat time accross
> batches
> > > > */
> > > > insert into t2 values (convert(varchar(26), getdate(), 9))
> > > > go
> > > > /* delay one minute */
> > > > waitfor delay '00:01:00'
> > > > go
> > > > /* delete the row from t1. This represents the mistake we want to
> recover
> > > > before.. */
> > > > delete from t1
> > > > go
> > > > use master
> > > > go
> > > > /* your secondary, post mistake full backup */
> > > > backup database a to disk='c:\a_bak2.bak'
> > > > go
> > > > /* your log backup */
> > > > backup log a to disk='c:\a_bak3.bak'
> > > > go
> > > > /* we restore to point in time captured in t2. I'm only using t2 so we
> could
> > > > record that point in time accross batches for the purposes of this
> example
> > > > script */
> > > > use a
> > > > declare @.restoretime varchar(26)
> > > > select @.restoretime = min(restoretime) from t2
> > > > use master
> > > > restore database a from disk='c:\a_bak1.bak' with norecovery
> > > > restore log a from disk='c:\a_bak3.bak' with recovery, stopat => @.restoretime
> > > > go
> > > > use a
> > > > go
> > > > /* prove that the deleted row from t1 is restored */
> > > > select * from t1
> > > > go
> > > > /* clean up */
> > > > use master
> > > > go
> > > > drop database a
> > > > go
> > > >
> > > > HTH
> > > >
> > > > Regards,
> > > > Greg Linwood
> > > > SQL Server MVP
> > > >
> > > > "ben brugman" <ben@.niethier.nl> wrote in message
> > > > news:bkhf32$mlv$1@.reader10.wxs.nl...
> > > > > Hello,
> > > > >
> > > > > We want to do a restore to a point of time.
> > > > >
> > > > > With recovery mode on full.
> > > > > Can one do a restore to a point of time before the last full backup
> > > > > with a transaction log backup made after the full backup ?
> > > > >
> > > > > Sequence.
> > > > >
> > > > > (full recovery mode, SQL-server 7. I think.).
> > > > > a. Somewere in the past a full backup is made.
> > > > > b. An error is made.
> > > > > c. A full backup is made.
> > > > > d. A transaction log backup is made.
> > > > >
> > > > > We want to restore to a point in time just before
> > > > > the error (b.) is made. Is this possible ?
> > > > > (I have got the BOL (from 7) and inside from Kalen but
> > > > > can not find the anwsers there).
> > > > >
> > > > > Ben Brugman.
> > > > >
> > > > >|||I tried some restores to a point in time. What I experienced was
that I could not restore to before the first transaction log backup.
Could be that I did not use the correct procedure, but I tried
several way to restore to some points in time all times after
the first transaction log backup were possible none before.
My solution is do a transaction backup immediatly after you have
created a database. Then there is no problem.
Could be that your problem is similar ?
Can you confirm the above ?
Thanks for sharing your knowledge,
ben brugman
"NIgel Stallard" <Nigel_Stallard@.hotmail.com> wrote in message
news:26210f56.0310090757.2a60601a@.posting.google.com...
> Hi Greg,
> Please ignore my last post. I have sorted out the problems with the
> restore to a point in time. I have noticed however that when I try to
> restore to a time which is later than the last entry in the
> transaction log, the database is left in a loading state. I am using
> SQL Server 2000 with SP3A installed, and according to MS knowledgebase
> article 319697 this particular issue was fixed with SP3. It also
> states that the problem was when using Enterprise Manager to carry out
> the restore. I am using a script. Have you come across this problem
> even when SP3 has been installed ?
> Kind Regards,
> Nigel
> Nigel_Stallard@.hotmail.com (NIgel Stallard) wrote in message
news:<26210f56.0310090259.664b6871@.posting.google.com>...
> > Hi Greg,
> >
> > The script is as follows..I know its a terrible script, I'm having to
> > learn as I go with this..
> > ---
> > set nocount on
> > go
> > use master
> > create database a
> > on (name=a_dat, filename='c:\a_dat.mdf', size=1Mb, filegrowth=1mb)
> > log on (name=a_log, filename='c:\a_log.ldf', size=1mb, filegrowth=1mb)
> > go
> > use a
> > go
> > create table t1 (c1 int, restoretime varchar(26))
> > create table t2 (restoretime varchar (26))
> > go
> > backup database a to disk='c:\a_bak0.bak'
> > go
> > insert into t1 values(1 , convert(varchar(26), getdate() , 9))
> > insert into t2 values(convert(varchar(26), getdate() , 9))
> > waitfor delay '00:00:05'
> > go
> > insert into t1 values(2 , convert(varchar(26), getdate() , 9))
> > insert into t2 values(convert(varchar(26), getdate() , 9))
> > waitfor delay '00:00:05'
> > go
> > insert into t1 values(3 , convert(varchar(26), getdate() , 9))
> > insert into t2 values(convert(varchar(26), getdate() , 9))
> > waitfor delay '00:00:05'
> > go
> > insert into t1 values(4 , convert(varchar(26), getdate() , 9))
> > insert into t2 values(convert(varchar(26), getdate() , 9))
> > waitfor delay '00:00:05'
> > go
> > use master
> > go
> > backup database a to disk='c:\a_bak1.bak'
> > go
> > use a
> > go
> > insert into t1 values(5 , convert(varchar(26), getdate() , 9))
> > insert into t2 values(convert(varchar(26), getdate() , 9))
> > go
> > waitfor delay '00:00:05'
> > insert into t1 values(6 , convert(varchar(26), getdate() , 9))
> > insert into t2 values(convert(varchar(26), getdate() , 9))
> > go
> > waitfor delay '00:00:05'
> > go
> > use master
> > go
> > backup database a to disk='c:\a_bak2.bak'
> > go
> > backup log a to disk='c:\a_bak3.bak'
> > go
> > use a
> > go
> ----
> >
> > Basically I'm trying to prove to myself that I can decide to restore
> > back to any particular transaction, which I assume is what restore to
> > point in time is all about.
> >
> > I think I understood your original example script, but as far as i
> > could see there was no need to use the transaction log as the database
> > would be put back to the desired point in time simply by restore the
> > first full backup.
> >
> > Basically I'm trying to prove to myself that I can decide to restore
> > back to any particular transaction, which I assume is what restore to
> > point in time is all about.
> > What I am attempting to do is try to restore to the point just after
> > the 2nd or 3rd transaction.
> >
> > Thanks for your interest in my problem, most appreciated..and thanks
> > for the quick response.
> >
> > Nigel
> >
> >
> > "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:<OvviP5ejDHA.2000@.TK2MSFTNGP12.phx.gbl>...
> > > Hi Nigel.
> > >
> > > There's no attachment to your post. I'm not sure, but I think these
might be
> > > getting dropped by the news-servers at the moment, so please re-post
with
> > > your script in the body of your post & I'll look at the script & try
to work
> > > it out for you..
> > >
> > > Don't email it to me as I have a strong filter on my email & you're
not in
> > > my address book, so you won't get through..
> > >
> > > Regards,
> > > Greg Linwood
> > > SQL Server MVP
> > >
> > > "NIgel Stallard" <Nigel_Stallard@.hotmail.com> wrote in message
> > > news:26210f56.0310080734.53626df5@.posting.google.com...
> > > > Greg,
> > > >
> > > > I too have been having problems with the restore to a point in time
> > > > and your sample script was useful in trying to come to grips with
> > > > this. All this seems to do though , is to restore the datbase to the
> > > > state it was in when it was backed up to a_bak1.bak.
> > > >
> > > > I have managed to write a script to add 4 records to the database ,
> > > > then do a full backup to a_bak1, then add a few more records then do
> > > > another full backup and a transaction log backup. I now want to
> > > > restore to a point where I have only entered the first two records,
> > > > but all that seems to happen is that I am restored to the point of
the
> > > > first full backup.
> > > > I have attached my script (which is heavily based on the one you
> > > > originally posted).
> > > >
> > > > What am I doing wrong ?...it's been driving me mad for days !!
> > > >
> > > > All help gratefully received .
> > > >
> > > >
> > > > Kind Regards,
> > > >
> > > > Nigel
> > > >
> > > > "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> > news:<#sdQMP4fDHA.2464@.TK2MSFTNGP09.phx.gbl>...
> > > > > Hi Ben.
> > > > >
> > > > > Are you sure you're on SQL 7.0? The term "full recovery" only came
along
> > > > > with SQL 2000...
> > > > >
> > > > > Anyway, this should be simple - restore the last good full backup
> > without
> > > > > recovering, then apply the transaction log & stopat the
appropriate
> > time.
> > > > > Below is a demo script that should work on both 7.0 & 2000,
whichever
> > you're
> > > > > using. Follow it carefully & you should be able to see that this
is
> > > > > possible. Note that the table t1 is populated with a row, which is
then
> > > > > deleted between the two full backups. By performing a normal full
> > database
> > > > > restore without recovery, then a log restore with recovery, you
can get
> > the
> > > > > point in time you're after.
> > > > >
> > > > > set nocount on
> > > > > go
> > > > > use master
> > > > > go
> > > > > create database a
> > > > > on (name=a_dat, filename='c:\a_dat.mdf', size=1mb, filegrowth=1mb)
> > > > > log on (name=a_log, filename='c:\a_log.ldf', size=1mb,
filegrowth=1mb )
> > > > > go
> > > > > use a
> > > > > go
> > > > > create table t1 (c1 int)
> > > > > create table t2 (restoretime varchar(26))
> > > > > go
> > > > > /* insert a row into t1. we'll expect to see this row again after
> > restore,
> > > > > despite it being deleted before full backup 2 */
> > > > > insert into t1 values (1)
> > > > > go
> > > > > use master
> > > > > go
> > > > > /* your full backup from whenever */
> > > > > backup database a to disk='c:\a_bak1.bak'
> > > > > go
> > > > > use a
> > > > > go
> > > > > /* insert a time into t2 that can be read as a stopat time accross
> > batches
> > > > > */
> > > > > insert into t2 values (convert(varchar(26), getdate(), 9))
> > > > > go
> > > > > /* delay one minute */
> > > > > waitfor delay '00:01:00'
> > > > > go
> > > > > /* delete the row from t1. This represents the mistake we want to
> > recover
> > > > > before.. */
> > > > > delete from t1
> > > > > go
> > > > > use master
> > > > > go
> > > > > /* your secondary, post mistake full backup */
> > > > > backup database a to disk='c:\a_bak2.bak'
> > > > > go
> > > > > /* your log backup */
> > > > > backup log a to disk='c:\a_bak3.bak'
> > > > > go
> > > > > /* we restore to point in time captured in t2. I'm only using t2
so we
> > could
> > > > > record that point in time accross batches for the purposes of this
> > example
> > > > > script */
> > > > > use a
> > > > > declare @.restoretime varchar(26)
> > > > > select @.restoretime = min(restoretime) from t2
> > > > > use master
> > > > > restore database a from disk='c:\a_bak1.bak' with norecovery
> > > > > restore log a from disk='c:\a_bak3.bak' with recovery, stopat => > @.restoretime
> > > > > go
> > > > > use a
> > > > > go
> > > > > /* prove that the deleted row from t1 is restored */
> > > > > select * from t1
> > > > > go
> > > > > /* clean up */
> > > > > use master
> > > > > go
> > > > > drop database a
> > > > > go
> > > > >
> > > > > HTH
> > > > >
> > > > > Regards,
> > > > > Greg Linwood
> > > > > SQL Server MVP
> > > > >
> > > > > "ben brugman" <ben@.niethier.nl> wrote in message
> > > > > news:bkhf32$mlv$1@.reader10.wxs.nl...
> > > > > > Hello,
> > > > > >
> > > > > > We want to do a restore to a point of time.
> > > > > >
> > > > > > With recovery mode on full.
> > > > > > Can one do a restore to a point of time before the last full
backup
> > > > > > with a transaction log backup made after the full backup ?
> > > > > >
> > > > > > Sequence.
> > > > > >
> > > > > > (full recovery mode, SQL-server 7. I think.).
> > > > > > a. Somewere in the past a full backup is made.
> > > > > > b. An error is made.
> > > > > > c. A full backup is made.
> > > > > > d. A transaction log backup is made.
> > > > > >
> > > > > > We want to restore to a point in time just before
> > > > > > the error (b.) is made. Is this possible ?
> > > > > > (I have got the BOL (from 7) and inside from Kalen but
> > > > > > can not find the anwsers there).
> > > > > >
> > > > > > Ben Brugman.
> > > > > >
> > > > > >