Point in time recovery

Can you tell me the step? how I can achieve this.

Thanks
"Tibor Karaszi" <tibor_please.no.email_karaszi RemoveThis @hotmail.nomail.com> wrote in
message news:5ACAC0ED-970C-4BAD-BAD4-AFB7C675A81F@microsoft.com...
> Restore will not let you get an inconsistent views of a database. But that
> I mean that it won't allow for a restore where some objects are from one
> point in time and other objects are from another point in time. You can
> restore up to 10:05 into a new database name and move the desired data
> (TEST2) from there into the production database.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
>
> "Smith" <naissani RemoveThis @hotmail.co> wrote in message
> news:3C7DF62C-9595-4750-A6B4-6D7F695B4D85@microsoft.com...
>> Hi Professionals,
>>
>> Following are my queries and I want to recover the table (Test2) with
>> data and recover all the test1 data. I would appreciate if you can let me
>> know the commands.
>>
>>
>> use eZSale
>> go
>> create table TEST1 (ID INT, NAME VARCHAR(50))
>> create table TEST2(ID INT, NAME VARCHAR(50))
>>
>> --Time:10:01
>> BACKUP DATABASE eZSale to disk = 'C:\TEMP\test_FullDb.BAK'
>>
>> --Time:10:02
>> INSERT INTO TEST1 SELECT 1, 'AFTER FULL BACKUP'
>> INSERT INTO TEST2 SELECT 1, 'AFTER FULL BACKUP'
>>
>> --Time:10:03
>> BACKUP LOG eZsale to disk = 'c:\Temp\test_Logbk.BAK'
>>
>> --Time: 10:04
>> INSERT INTO TEST1 SELECT 2, 'AFTER LOG BACKUP'
>> INSERT INTO TEST2 SELECT 2, 'AFTER LOG BACKUP'
>> INSERT INTO TEST2 SELECT 3, 'AFTER LOG BACKUP..SECOND ENTRY'
>>
>> --Time: 10:05
>> DROP TABLE TEST2
>>
>> --Time: 10:06
>> BACKUP DATABASE eZSale to disk = 'C:\TEMP\test_DiffDb.BAK' with
>> differential
>>
>> --Time: 10:07
>> INSERT INTO TEST1 SELECT 4, 'AFTER DIFFERNTIAL BACKUP'
>> INSERT INTO TEST1 SELECT 5, 'AFTER DIFFERNTIAL BACKUP..SECOND ENTRY'
>
 >> Stay informed about: Point in time recovery 

In order to do a point in time recovery, you'll need to use the STOPAT command.
Here's an example I pulled out of BOL:

RESTORE DATABASE AdventureWorks
FROM AdventureWorksBackups
WITH NORECOVERY;

RESTORE LOG AdventureWorks
FROM AdventureWorksBackups
WITH RECOVERY, STOPAT = 'Apr 15, 2020 12:00 AM';


If you have several logs before the last one that you run with recovery,
just do them with norecovery like you did the full backup. So if you had
previous logs to restore before you get to the one you want to stop at just
say:

RESTORE LOG AdventureWorks FROM AdventureWorksBackups with NORECOVERY

You can replace that device name with a physical path if you need to so you
would also do this if you had a physical path to the file:

RESTORE LOG AdventureWorks FROM disk='c:\mylog.trn' with NORECOVERY

It's worth it to mention that you lose the STOPAT functionality if you're
not in full recovery mode.

And as Tibor said, you can only recovery to one point in time. So if you
need 2 separate times, you'll have to do 2 restores and merge them manually
at the table level.


--
Read my book reviews at:
www.ITBookworm.com

Blog Author of:
Database Underground -- http://weblog.infoworld.com/dbunderground/
DBA Rant – http://dbarant.blogspot.com




"Smith" wrote:

> Can you tell me the step? how I can achieve this.
>
> Thanks
> "Tibor Karaszi" <tibor_please.no.email_karaszi DeleteThis @hotmail.nomail.com> wrote in
> message news:5ACAC0ED-970C-4BAD-BAD4-AFB7C675A81F@microsoft.com...
> > Restore will not let you get an inconsistent views of a database. But that
> > I mean that it won't allow for a restore where some objects are from one
> > point in time and other objects are from another point in time. You can
> > restore up to 10:05 into a new database name and move the desired data
> > (TEST2) from there into the production database.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://sqlblog.com/blogs/tibor_karaszi
> >
> >
> > "Smith" <naissani DeleteThis @hotmail.co> wrote in message
> > news:3C7DF62C-9595-4750-A6B4-6D7F695B4D85@microsoft.com...
> >> Hi Professionals,
> >>
> >> Following are my queries and I want to recover the table (Test2) with
> >> data and recover all the test1 data. I would appreciate if you can let me
> >> know the commands.
> >>
> >>
> >> use eZSale
> >> go
> >> create table TEST1 (ID INT, NAME VARCHAR(50))
> >> create table TEST2(ID INT, NAME VARCHAR(50))
> >>
> >> --Time:10:01
> >> BACKUP DATABASE eZSale to disk = 'C:\TEMP\test_FullDb.BAK'
> >>
> >> --Time:10:02
> >> INSERT INTO TEST1 SELECT 1, 'AFTER FULL BACKUP'
> >> INSERT INTO TEST2 SELECT 1, 'AFTER FULL BACKUP'
> >>
> >> --Time:10:03
> >> BACKUP LOG eZsale to disk = 'c:\Temp\test_Logbk.BAK'
> >>
> >> --Time: 10:04
> >> INSERT INTO TEST1 SELECT 2, 'AFTER LOG BACKUP'
> >> INSERT INTO TEST2 SELECT 2, 'AFTER LOG BACKUP'
> >> INSERT INTO TEST2 SELECT 3, 'AFTER LOG BACKUP..SECOND ENTRY'
> >>
> >> --Time: 10:05
> >> DROP TABLE TEST2
> >>
> >> --Time: 10:06
> >> BACKUP DATABASE eZSale to disk = 'C:\TEMP\test_DiffDb.BAK' with
> >> differential
> >>
> >> --Time: 10:07
> >> INSERT INTO TEST1 SELECT 4, 'AFTER DIFFERNTIAL BACKUP'
> >> INSERT INTO TEST1 SELECT 5, 'AFTER DIFFERNTIAL BACKUP..SECOND ENTRY'
> >
>
 >> Stay informed about: Point in time recovery 

Thanks.. that means differential backup doesn't help me in this scanario?

Thanks

"Sean McCown" <SeanMcCown.DeleteThis@discussions.microsoft.com> wrote in message
news:CB701821-3B57-410A-B2BF-901A464CD455@microsoft.com...
> In order to do a point in time recovery, you'll need to use the STOPAT
> command.
> Here's an example I pulled out of BOL:
>
> RESTORE DATABASE AdventureWorks
> FROM AdventureWorksBackups
> WITH NORECOVERY;
>
> RESTORE LOG AdventureWorks
> FROM AdventureWorksBackups
> WITH RECOVERY, STOPAT = 'Apr 15, 2020 12:00 AM';
>
>
> If you have several logs before the last one that you run with recovery,
> just do them with norecovery like you did the full backup. So if you had
> previous logs to restore before you get to the one you want to stop at
> just
> say:
>
> RESTORE LOG AdventureWorks FROM AdventureWorksBackups with NORECOVERY
>
> You can replace that device name with a physical path if you need to so
> you
> would also do this if you had a physical path to the file:
>
> RESTORE LOG AdventureWorks FROM disk='c:\mylog.trn' with NORECOVERY
>
> It's worth it to mention that you lose the STOPAT functionality if you're
> not in full recovery mode.
>
> And as Tibor said, you can only recovery to one point in time. So if you
> need 2 separate times, you'll have to do 2 restores and merge them
> manually
> at the table level.
>
>
> --
> Read my book reviews at:
> www.ITBookworm.com
>
> Blog Author of:
> Database Underground -- http://weblog.infoworld.com/dbunderground/
> DBA Rant – http://dbarant.blogspot.com
>
>
>
>
> "Smith" wrote:
>
>> Can you tell me the step? how I can achieve this.
>>
>> Thanks
>> "Tibor Karaszi" <tibor_please.no.email_karaszi.DeleteThis@hotmail.nomail.com> wrote
>> in
>> message news:5ACAC0ED-970C-4BAD-BAD4-AFB7C675A81F@microsoft.com...
>> > Restore will not let you get an inconsistent views of a database. But
>> > that
>> > I mean that it won't allow for a restore where some objects are from
>> > one
>> > point in time and other objects are from another point in time. You can
>> > restore up to 10:05 into a new database name and move the desired data
>> > (TEST2) from there into the production database.
>> >
>> > --
>> > Tibor Karaszi, SQL Server MVP
>> > http://www.karaszi.com/sqlserver/default.asp
>> > http://sqlblog.com/blogs/tibor_karaszi
>> >
>> >
>> > "Smith" <naissani.DeleteThis@hotmail.co> wrote in message
>> > news:3C7DF62C-9595-4750-A6B4-6D7F695B4D85@microsoft.com...
>> >> Hi Professionals,
>> >>
>> >> Following are my queries and I want to recover the table (Test2) with
>> >> data and recover all the test1 data. I would appreciate if you can let
>> >> me
>> >> know the commands.
>> >>
>> >>
>> >> use eZSale
>> >> go
>> >> create table TEST1 (ID INT, NAME VARCHAR(50))
>> >> create table TEST2(ID INT, NAME VARCHAR(50))
>> >>
>> >> --Time:10:01
>> >> BACKUP DATABASE eZSale to disk = 'C:\TEMP\test_FullDb.BAK'
>> >>
>> >> --Time:10:02
>> >> INSERT INTO TEST1 SELECT 1, 'AFTER FULL BACKUP'
>> >> INSERT INTO TEST2 SELECT 1, 'AFTER FULL BACKUP'
>> >>
>> >> --Time:10:03
>> >> BACKUP LOG eZsale to disk = 'c:\Temp\test_Logbk.BAK'
>> >>
>> >> --Time: 10:04
>> >> INSERT INTO TEST1 SELECT 2, 'AFTER LOG BACKUP'
>> >> INSERT INTO TEST2 SELECT 2, 'AFTER LOG BACKUP'
>> >> INSERT INTO TEST2 SELECT 3, 'AFTER LOG BACKUP..SECOND ENTRY'
>> >>
>> >> --Time: 10:05
>> >> DROP TABLE TEST2
>> >>
>> >> --Time: 10:06
>> >> BACKUP DATABASE eZSale to disk = 'C:\TEMP\test_DiffDb.BAK' with
>> >> differential
>> >>
>> >> --Time: 10:07
>> >> INSERT INTO TEST1 SELECT 4, 'AFTER DIFFERNTIAL BACKUP'
>> >> INSERT INTO TEST1 SELECT 5, 'AFTER DIFFERNTIAL BACKUP..SECOND ENTRY'
>> >
>>
 >> Stay informed about: Point in time recovery 

You are correct. You lost STOPAT with diffs.

Don't forget to mark the issue as resolved if you got your answer.
thx.
--
Read my book reviews at:
www.ITBookworm.com

Blog Author of:
Database Underground -- http://weblog.infoworld.com/dbunderground/
DBA Rant – http://dbarant.blogspot.com




"Smith" wrote:

> Thanks.. that means differential backup doesn't help me in this scanario?
>
> Thanks
>
> "Sean McCown" <SeanMcCown.RemoveThis@discussions.microsoft.com> wrote in message
> news:CB701821-3B57-410A-B2BF-901A464CD455@microsoft.com...
> > In order to do a point in time recovery, you'll need to use the STOPAT
> > command.
> > Here's an example I pulled out of BOL:
> >
> > RESTORE DATABASE AdventureWorks
> > FROM AdventureWorksBackups
> > WITH NORECOVERY;
> >
> > RESTORE LOG AdventureWorks
> > FROM AdventureWorksBackups
> > WITH RECOVERY, STOPAT = 'Apr 15, 2020 12:00 AM';
> >
> >
> > If you have several logs before the last one that you run with recovery,
> > just do them with norecovery like you did the full backup. So if you had
> > previous logs to restore before you get to the one you want to stop at
> > just
> > say:
> >
> > RESTORE LOG AdventureWorks FROM AdventureWorksBackups with NORECOVERY
> >
> > You can replace that device name with a physical path if you need to so
> > you
> > would also do this if you had a physical path to the file:
> >
> > RESTORE LOG AdventureWorks FROM disk='c:\mylog.trn' with NORECOVERY
> >
> > It's worth it to mention that you lose the STOPAT functionality if you're
> > not in full recovery mode.
> >
> > And as Tibor said, you can only recovery to one point in time. So if you
> > need 2 separate times, you'll have to do 2 restores and merge them
> > manually
> > at the table level.
> >
> >
> > --
> > Read my book reviews at:
> > www.ITBookworm.com
> >
> > Blog Author of:
> > Database Underground -- http://weblog.infoworld.com/dbunderground/
> > DBA Rant – http://dbarant.blogspot.com
> >
> >
> >
> >
> > "Smith" wrote:
> >
> >> Can you tell me the step? how I can achieve this.
> >>
> >> Thanks
> >> "Tibor Karaszi" <tibor_please.no.email_karaszi.RemoveThis@hotmail.nomail.com> wrote
> >> in
> >> message news:5ACAC0ED-970C-4BAD-BAD4-AFB7C675A81F@microsoft.com...
> >> > Restore will not let you get an inconsistent views of a database. But
> >> > that
> >> > I mean that it won't allow for a restore where some objects are from
> >> > one
> >> > point in time and other objects are from another point in time. You can
> >> > restore up to 10:05 into a new database name and move the desired data
> >> > (TEST2) from there into the production database.
> >> >
> >> > --
> >> > Tibor Karaszi, SQL Server MVP
> >> > http://www.karaszi.com/sqlserver/default.asp
> >> > http://sqlblog.com/blogs/tibor_karaszi
> >> >
> >> >
> >> > "Smith" <naissani.RemoveThis@hotmail.co> wrote in message
> >> > news:3C7DF62C-9595-4750-A6B4-6D7F695B4D85@microsoft.com...
> >> >> Hi Professionals,
> >> >>
> >> >> Following are my queries and I want to recover the table (Test2) with
> >> >> data and recover all the test1 data. I would appreciate if you can let
> >> >> me
> >> >> know the commands.
> >> >>
> >> >>
> >> >> use eZSale
> >> >> go
> >> >> create table TEST1 (ID INT, NAME VARCHAR(50))
> >> >> create table TEST2(ID INT, NAME VARCHAR(50))
> >> >>
> >> >> --Time:10:01
> >> >> BACKUP DATABASE eZSale to disk = 'C:\TEMP\test_FullDb.BAK'
> >> >>
> >> >> --Time:10:02
> >> >> INSERT INTO TEST1 SELECT 1, 'AFTER FULL BACKUP'
> >> >> INSERT INTO TEST2 SELECT 1, 'AFTER FULL BACKUP'
> >> >>
> >> >> --Time:10:03
> >> >> BACKUP LOG eZsale to disk = 'c:\Temp\test_Logbk.BAK'
> >> >>
> >> >> --Time: 10:04
> >> >> INSERT INTO TEST1 SELECT 2, 'AFTER LOG BACKUP'
> >> >> INSERT INTO TEST2 SELECT 2, 'AFTER LOG BACKUP'
> >> >> INSERT INTO TEST2 SELECT 3, 'AFTER LOG BACKUP..SECOND ENTRY'
> >> >>
> >> >> --Time: 10:05
> >> >> DROP TABLE TEST2
> >> >>
> >> >> --Time: 10:06
> >> >> BACKUP DATABASE eZSale to disk = 'C:\TEMP\test_DiffDb.BAK' with
> >> >> differential
> >> >>
> >> >> --Time: 10:07
> >> >> INSERT INTO TEST1 SELECT 4, 'AFTER DIFFERNTIAL BACKUP'
> >> >> INSERT INTO TEST1 SELECT 5, 'AFTER DIFFERNTIAL BACKUP..SECOND ENTRY'
> >> >
> >>
>
 >> Stay informed about: Point in time recovery 

That means I can't differential backup when I am trying to do point in time
recovery?

"Sean McCown" <SeanMcCown.RemoveThis@discussions.microsoft.com> wrote in message
news:6CEEC45C-62DA-42C9-9512-4C1681D29A9D@microsoft.com...
> You are correct. You lost STOPAT with diffs.
>
> Don't forget to mark the issue as resolved if you got your answer.
> thx.
> --
> Read my book reviews at:
> www.ITBookworm.com
>
> Blog Author of:
> Database Underground -- http://weblog.infoworld.com/dbunderground/
> DBA Rant – http://dbarant.blogspot.com
>
>
>
>
> "Smith" wrote:
>
>> Thanks.. that means differential backup doesn't help me in this scanario?
>>
>> Thanks
>>
>> "Sean McCown" <SeanMcCown.RemoveThis@discussions.microsoft.com> wrote in message
>> news:CB701821-3B57-410A-B2BF-901A464CD455@microsoft.com...
>> > In order to do a point in time recovery, you'll need to use the STOPAT
>> > command.
>> > Here's an example I pulled out of BOL:
>> >
>> > RESTORE DATABASE AdventureWorks
>> > FROM AdventureWorksBackups
>> > WITH NORECOVERY;
>> >
>> > RESTORE LOG AdventureWorks
>> > FROM AdventureWorksBackups
>> > WITH RECOVERY, STOPAT = 'Apr 15, 2020 12:00 AM';
>> >
>> >
>> > If you have several logs before the last one that you run with
>> > recovery,
>> > just do them with norecovery like you did the full backup. So if you
>> > had
>> > previous logs to restore before you get to the one you want to stop at
>> > just
>> > say:
>> >
>> > RESTORE LOG AdventureWorks FROM AdventureWorksBackups with NORECOVERY
>> >
>> > You can replace that device name with a physical path if you need to so
>> > you
>> > would also do this if you had a physical path to the file:
>> >
>> > RESTORE LOG AdventureWorks FROM disk='c:\mylog.trn' with NORECOVERY
>> >
>> > It's worth it to mention that you lose the STOPAT functionality if
>> > you're
>> > not in full recovery mode.
>> >
>> > And as Tibor said, you can only recovery to one point in time. So if
>> > you
>> > need 2 separate times, you'll have to do 2 restores and merge them
>> > manually
>> > at the table level.
>> >
>> >
>> > --
>> > Read my book reviews at:
>> > www.ITBookworm.com
>> >
>> > Blog Author of:
>> > Database Underground -- http://weblog.infoworld.com/dbunderground/
>> > DBA Rant – http://dbarant.blogspot.com
>> >
>> >
>> >
>> >
>> > "Smith" wrote:
>> >
>> >> Can you tell me the step? how I can achieve this.
>> >>
>> >> Thanks
>> >> "Tibor Karaszi" <tibor_please.no.email_karaszi.RemoveThis@hotmail.nomail.com>
>> >> wrote
>> >> in
>> >> message news:5ACAC0ED-970C-4BAD-BAD4-AFB7C675A81F@microsoft.com...
>> >> > Restore will not let you get an inconsistent views of a database.
>> >> > But
>> >> > that
>> >> > I mean that it won't allow for a restore where some objects are from
>> >> > one
>> >> > point in time and other objects are from another point in time. You
>> >> > can
>> >> > restore up to 10:05 into a new database name and move the desired
>> >> > data
>> >> > (TEST2) from there into the production database.
>> >> >
>> >> > --
>> >> > Tibor Karaszi, SQL Server MVP
>> >> > http://www.karaszi.com/sqlserver/default.asp
>> >> > http://sqlblog.com/blogs/tibor_karaszi
>> >> >
>> >> >
>> >> > "Smith" <naissani.RemoveThis@hotmail.co> wrote in message
>> >> > news:3C7DF62C-9595-4750-A6B4-6D7F695B4D85@microsoft.com...
>> >> >> Hi Professionals,
>> >> >>
>> >> >> Following are my queries and I want to recover the table (Test2)
>> >> >> with
>> >> >> data and recover all the test1 data. I would appreciate if you can
>> >> >> let
>> >> >> me
>> >> >> know the commands.
>> >> >>
>> >> >>
>> >> >> use eZSale
>> >> >> go
>> >> >> create table TEST1 (ID INT, NAME VARCHAR(50))
>> >> >> create table TEST2(ID INT, NAME VARCHAR(50))
>> >> >>
>> >> >> --Time:10:01
>> >> >> BACKUP DATABASE eZSale to disk = 'C:\TEMP\test_FullDb.BAK'
>> >> >>
>> >> >> --Time:10:02
>> >> >> INSERT INTO TEST1 SELECT 1, 'AFTER FULL BACKUP'
>> >> >> INSERT INTO TEST2 SELECT 1, 'AFTER FULL BACKUP'
>> >> >>
>> >> >> --Time:10:03
>> >> >> BACKUP LOG eZsale to disk = 'c:\Temp\test_Logbk.BAK'
>> >> >>
>> >> >> --Time: 10:04
>> >> >> INSERT INTO TEST1 SELECT 2, 'AFTER LOG BACKUP'
>> >> >> INSERT INTO TEST2 SELECT 2, 'AFTER LOG BACKUP'
>> >> >> INSERT INTO TEST2 SELECT 3, 'AFTER LOG BACKUP..SECOND ENTRY'
>> >> >>
>> >> >> --Time: 10:05
>> >> >> DROP TABLE TEST2
>> >> >>
>> >> >> --Time: 10:06
>> >> >> BACKUP DATABASE eZSale to disk = 'C:\TEMP\test_DiffDb.BAK' with
>> >> >> differential
>> >> >>
>> >> >> --Time: 10:07
>> >> >> INSERT INTO TEST1 SELECT 4, 'AFTER DIFFERNTIAL BACKUP'
>> >> >> INSERT INTO TEST1 SELECT 5, 'AFTER DIFFERNTIAL BACKUP..SECOND
>> >> >> ENTRY'
>> >> >
>> >>
>>
 >> Stay informed about: Point in time recovery 

That's exactly what that means.
So just to state it to be clear... you have to use log backups to have point
in time recovery. You also have to be in full recovery mode. Bulk-logged
won't give you point in time.

--
Read my book reviews at:
www.ITBookworm.com

Blog Author of:
Database Underground -- http://weblog.infoworld.com/dbunderground/
DBA Rant – http://dbarant.blogspot.com




"Smith" wrote:

> That means I can't differential backup when I am trying to do point in time
> recovery?
>
> "Sean McCown" <SeanMcCown.DeleteThis@discussions.microsoft.com> wrote in message
> news:6CEEC45C-62DA-42C9-9512-4C1681D29A9D@microsoft.com...
> > You are correct. You lost STOPAT with diffs.
> >
> > Don't forget to mark the issue as resolved if you got your answer.
> > thx.
> > --
> > Read my book reviews at:
> > www.ITBookworm.com
> >
> > Blog Author of:
> > Database Underground -- http://weblog.infoworld.com/dbunderground/
> > DBA Rant – http://dbarant.blogspot.com
> >
> >
> >
> >
> > "Smith" wrote:
> >
> >> Thanks.. that means differential backup doesn't help me in this scanario?
> >>
> >> Thanks
> >>
> >> "Sean McCown" <SeanMcCown.DeleteThis@discussions.microsoft.com> wrote in message
> >> news:CB701821-3B57-410A-B2BF-901A464CD455@microsoft.com...
> >> > In order to do a point in time recovery, you'll need to use the STOPAT
> >> > command.
> >> > Here's an example I pulled out of BOL:
> >> >
> >> > RESTORE DATABASE AdventureWorks
> >> > FROM AdventureWorksBackups
> >> > WITH NORECOVERY;
> >> >
> >> > RESTORE LOG AdventureWorks
> >> > FROM AdventureWorksBackups
> >> > WITH RECOVERY, STOPAT = 'Apr 15, 2020 12:00 AM';
> >> >
> >> >
> >> > If you have several logs before the last one that you run with
> >> > recovery,
> >> > just do them with norecovery like you did the full backup. So if you
> >> > had
> >> > previous logs to restore before you get to the one you want to stop at
> >> > just
> >> > say:
> >> >
> >> > RESTORE LOG AdventureWorks FROM AdventureWorksBackups with NORECOVERY
> >> >
> >> > You can replace that device name with a physical path if you need to so
> >> > you
> >> > would also do this if you had a physical path to the file:
> >> >
> >> > RESTORE LOG AdventureWorks FROM disk='c:\mylog.trn' with NORECOVERY
> >> >
> >> > It's worth it to mention that you lose the STOPAT functionality if
> >> > you're
> >> > not in full recovery mode.
> >> >
> >> > And as Tibor said, you can only recovery to one point in time. So if
> >> > you
> >> > need 2 separate times, you'll have to do 2 restores and merge them
> >> > manually
> >> > at the table level.
> >> >
> >> >
> >> > --
> >> > Read my book reviews at:
> >> > www.ITBookworm.com
> >> >
> >> > Blog Author of:
> >> > Database Underground -- http://weblog.infoworld.com/dbunderground/
> >> > DBA Rant – http://dbarant.blogspot.com
> >> >
> >> >
> >> >
> >> >
> >> > "Smith" wrote:
> >> >
> >> >> Can you tell me the step? how I can achieve this.
> >> >>
> >> >> Thanks
> >> >> "Tibor Karaszi" <tibor_please.no.email_karaszi.DeleteThis@hotmail.nomail.com>
> >> >> wrote
> >> >> in
> >> >> message news:5ACAC0ED-970C-4BAD-BAD4-AFB7C675A81F@microsoft.com...
> >> >> > Restore will not let you get an inconsistent views of a database.
> >> >> > But
> >> >> > that
> >> >> > I mean that it won't allow for a restore where some objects are from
> >> >> > one
> >> >> > point in time and other objects are from another point in time. You
> >> >> > can
> >> >> > restore up to 10:05 into a new database name and move the desired
> >> >> > data
> >> >> > (TEST2) from there into the production database.
> >> >> >
> >> >> > --
> >> >> > Tibor Karaszi, SQL Server MVP
> >> >> > http://www.karaszi.com/sqlserver/default.asp
> >> >> > http://sqlblog.com/blogs/tibor_karaszi
> >> >> >
> >> >> >
> >> >> > "Smith" <naissani.DeleteThis@hotmail.co> wrote in message
> >> >> > news:3C7DF62C-9595-4750-A6B4-6D7F695B4D85@microsoft.com...
> >> >> >> Hi Professionals,
> >> >> >>
> >> >> >> Following are my queries and I want to recover the table (Test2)
> >> >> >> with
> >> >> >> data and recover all the test1 data. I would appreciate if you can
> >> >> >> let
> >> >> >> me
> >> >> >> know the commands.
> >> >> >>
> >> >> >>
> >> >> >> use eZSale
> >> >> >> go
> >> >> >> create table TEST1 (ID INT, NAME VARCHAR(50))
> >> >> >> create table TEST2(ID INT, NAME VARCHAR(50))
> >> >> >>
> >> >> >> --Time:10:01
> >> >> >> BACKUP DATABASE eZSale to disk = 'C:\TEMP\test_FullDb.BAK'
> >> >> >>
> >> >> >> --Time:10:02
> >> >> >> INSERT INTO TEST1 SELECT 1, 'AFTER FULL BACKUP'
> >> >> >> INSERT INTO TEST2 SELECT 1, 'AFTER FULL BACKUP'
> >> >> >>
> >> >> >> --Time:10:03
> >> >> >> BACKUP LOG eZsale to disk = 'c:\Temp\test_Logbk.BAK'
> >> >> >>
> >> >> >> --Time: 10:04
> >> >> >> INSERT INTO TEST1 SELECT 2, 'AFTER LOG BACKUP'
> >> >> >> INSERT INTO TEST2 SELECT 2, 'AFTER LOG BACKUP'
> >> >> >> INSERT INTO TEST2 SELECT 3, 'AFTER LOG BACKUP..SECOND ENTRY'
> >> >> >>
> >> >> >> --Time: 10:05
> >> >> >> DROP TABLE TEST2
> >> >> >>
> >> >> >> --Time: 10:06
> >> >> >> BACKUP DATABASE eZSale to disk = 'C:\TEMP\test_DiffDb.BAK' with
> >> >> >> differential
> >> >> >>
> >> >> >> --Time: 10:07
> >> >> >> INSERT INTO TEST1 SELECT 4, 'AFTER DIFFERNTIAL BACKUP'
> >> >> >> INSERT INTO TEST1 SELECT 5, 'AFTER DIFFERNTIAL BACKUP..SECOND
> >> >> >> ENTRY'
> >> >> >
> >> >>
> >>
>
 >> Stay informed about: Point in time recovery 

thanks but if we first restore full backup with norecovery and then
differential backup with norecovery and then log backup with recovery with
StopAt option, will that give point in time recovery?

"Sean McCown" <SeanMcCown RemoveThis @discussions.microsoft.com> wrote in message
news:576CD573-BFFD-4D78-AD16-A64C116267EB@microsoft.com...
> That's exactly what that means.
> So just to state it to be clear... you have to use log backups to have
> point
> in time recovery. You also have to be in full recovery mode. Bulk-logged
> won't give you point in time.
>
> --
> Read my book reviews at:
> www.ITBookworm.com
>
> Blog Author of:
> Database Underground -- http://weblog.infoworld.com/dbunderground/
> DBA Rant – http://dbarant.blogspot.com
>
>
>
>
> "Smith" wrote:
>
>> That means I can't differential backup when I am trying to do point in
>> time
>> recovery?
>>
>> "Sean McCown" <SeanMcCown RemoveThis @discussions.microsoft.com> wrote in message
>> news:6CEEC45C-62DA-42C9-9512-4C1681D29A9D@microsoft.com...
>> > You are correct. You lost STOPAT with diffs.
>> >
>> > Don't forget to mark the issue as resolved if you got your answer.
>> > thx.
>> > --
>> > Read my book reviews at:
>> > www.ITBookworm.com
>> >
>> > Blog Author of:
>> > Database Underground -- http://weblog.infoworld.com/dbunderground/
>> > DBA Rant – http://dbarant.blogspot.com
>> >
>> >
>> >
>> >
>> > "Smith" wrote:
>> >
>> >> Thanks.. that means differential backup doesn't help me in this
>> >> scanario?
>> >>
>> >> Thanks
>> >>
>> >> "Sean McCown" <SeanMcCown RemoveThis @discussions.microsoft.com> wrote in message
>> >> news:CB701821-3B57-410A-B2BF-901A464CD455@microsoft.com...
>> >> > In order to do a point in time recovery, you'll need to use the
>> >> > STOPAT
>> >> > command.
>> >> > Here's an example I pulled out of BOL:
>> >> >
>> >> > RESTORE DATABASE AdventureWorks
>> >> > FROM AdventureWorksBackups
>> >> > WITH NORECOVERY;
>> >> >
>> >> > RESTORE LOG AdventureWorks
>> >> > FROM AdventureWorksBackups
>> >> > WITH RECOVERY, STOPAT = 'Apr 15, 2020 12:00 AM';
>> >> >
>> >> >
>> >> > If you have several logs before the last one that you run with
>> >> > recovery,
>> >> > just do them with norecovery like you did the full backup. So if
>> >> > you
>> >> > had
>> >> > previous logs to restore before you get to the one you want to stop
>> >> > at
>> >> > just
>> >> > say:
>> >> >
>> >> > RESTORE LOG AdventureWorks FROM AdventureWorksBackups with
>> >> > NORECOVERY
>> >> >
>> >> > You can replace that device name with a physical path if you need to
>> >> > so
>> >> > you
>> >> > would also do this if you had a physical path to the file:
>> >> >
>> >> > RESTORE LOG AdventureWorks FROM disk='c:\mylog.trn' with NORECOVERY
>> >> >
>> >> > It's worth it to mention that you lose the STOPAT functionality if
>> >> > you're
>> >> > not in full recovery mode.
>> >> >
>> >> > And as Tibor said, you can only recovery to one point in time. So
>> >> > if
>> >> > you
>> >> > need 2 separate times, you'll have to do 2 restores and merge them
>> >> > manually
>> >> > at the table level.
>> >> >
>> >> >
>> >> > --
>> >> > Read my book reviews at:
>> >> > www.ITBookworm.com
>> >> >
>> >> > Blog Author of:
>> >> > Database Underground -- http://weblog.infoworld.com/dbunderground/
>> >> > DBA Rant – http://dbarant.blogspot.com
>> >> >
>> >> >
>> >> >
>> >> >
>> >> > "Smith" wrote:
>> >> >
>> >> >> Can you tell me the step? how I can achieve this.
>> >> >>
>> >> >> Thanks
>> >> >> "Tibor Karaszi" <tibor_please.no.email_karaszi RemoveThis @hotmail.nomail.com>
>> >> >> wrote
>> >> >> in
>> >> >> message news:5ACAC0ED-970C-4BAD-BAD4-AFB7C675A81F@microsoft.com...
>> >> >> > Restore will not let you get an inconsistent views of a database.
>> >> >> > But
>> >> >> > that
>> >> >> > I mean that it won't allow for a restore where some objects are
>> >> >> > from
>> >> >> > one
>> >> >> > point in time and other objects are from another point in time.
>> >> >> > You
>> >> >> > can
>> >> >> > restore up to 10:05 into a new database name and move the desired
>> >> >> > data
>> >> >> > (TEST2) from there into the production database.
>> >> >> >
>> >> >> > --
>> >> >> > Tibor Karaszi, SQL Server MVP
>> >> >> > http://www.karaszi.com/sqlserver/default.asp
>> >> >> > http://sqlblog.com/blogs/tibor_karaszi
>> >> >> >
>> >> >> >
>> >> >> > "Smith" <naissani RemoveThis @hotmail.co> wrote in message
>> >> >> > news:3C7DF62C-9595-4750-A6B4-6D7F695B4D85@microsoft.com...
>> >> >> >> Hi Professionals,
>> >> >> >>
>> >> >> >> Following are my queries and I want to recover the table (Test2)
>> >> >> >> with
>> >> >> >> data and recover all the test1 data. I would appreciate if you
>> >> >> >> can
>> >> >> >> let
>> >> >> >> me
>> >> >> >> know the commands.
>> >> >> >>
>> >> >> >>
>> >> >> >> use eZSale
>> >> >> >> go
>> >> >> >> create table TEST1 (ID INT, NAME VARCHAR(50))
>> >> >> >> create table TEST2(ID INT, NAME VARCHAR(50))
>> >> >> >>
>> >> >> >> --Time:10:01
>> >> >> >> BACKUP DATABASE eZSale to disk = 'C:\TEMP\test_FullDb.BAK'
>> >> >> >>
>> >> >> >> --Time:10:02
>> >> >> >> INSERT INTO TEST1 SELECT 1, 'AFTER FULL BACKUP'
>> >> >> >> INSERT INTO TEST2 SELECT 1, 'AFTER FULL BACKUP'
>> >> >> >>
>> >> >> >> --Time:10:03
>> >> >> >> BACKUP LOG eZsale to disk = 'c:\Temp\test_Logbk.BAK'
>> >> >> >>
>> >> >> >> --Time: 10:04
>> >> >> >> INSERT INTO TEST1 SELECT 2, 'AFTER LOG BACKUP'
>> >> >> >> INSERT INTO TEST2 SELECT 2, 'AFTER LOG BACKUP'
>> >> >> >> INSERT INTO TEST2 SELECT 3, 'AFTER LOG BACKUP..SECOND ENTRY'
>> >> >> >>
>> >> >> >> --Time: 10:05
>> >> >> >> DROP TABLE TEST2
>> >> >> >>
>> >> >> >> --Time: 10:06
>> >> >> >> BACKUP DATABASE eZSale to disk = 'C:\TEMP\test_DiffDb.BAK' with
>> >> >> >> differential
>> >> >> >>
>> >> >> >> --Time: 10:07
>> >> >> >> INSERT INTO TEST1 SELECT 4, 'AFTER DIFFERNTIAL BACKUP'
>> >> >> >> INSERT INTO TEST1 SELECT 5, 'AFTER DIFFERNTIAL BACKUP..SECOND
>> >> >> >> ENTRY'
>> >> >> >
>> >> >>
>> >>
>>
 >> Stay informed about: Point in time recovery 

Not having covered that specific scenario with STOPAT, I'm not sure. It
sounds like it should work. I'm thinking that you just can't use STOPAT when
restoring the actual diff... so your chances are high. I'd say y, it should
be possible.

Just kick off a quick test real quick... shouldn't be that hard, right?


--
Read my book reviews at:
www.ITBookworm.com

Blog Author of:
Database Underground -- http://weblog.infoworld.com/dbunderground/
DBA Rant – http://dbarant.blogspot.com




"Smith" wrote:

> thanks but if we first restore full backup with norecovery and then
> differential backup with norecovery and then log backup with recovery with
> StopAt option, will that give point in time recovery?
>
> "Sean McCown" <SeanMcCown RemoveThis @discussions.microsoft.com> wrote in message
> news:576CD573-BFFD-4D78-AD16-A64C116267EB@microsoft.com...
> > That's exactly what that means.
> > So just to state it to be clear... you have to use log backups to have
> > point
> > in time recovery. You also have to be in full recovery mode. Bulk-logged
> > won't give you point in time.
> >
> > --
> > Read my book reviews at:
> > www.ITBookworm.com
> >
> > Blog Author of:
> > Database Underground -- http://weblog.infoworld.com/dbunderground/
> > DBA Rant – http://dbarant.blogspot.com
> >
> >
> >
> >
> > "Smith" wrote:
> >
> >> That means I can't differential backup when I am trying to do point in
> >> time
> >> recovery?
> >>
> >> "Sean McCown" <SeanMcCown RemoveThis @discussions.microsoft.com> wrote in message
> >> news:6CEEC45C-62DA-42C9-9512-4C1681D29A9D@microsoft.com...
> >> > You are correct. You lost STOPAT with diffs.
> >> >
> >> > Don't forget to mark the issue as resolved if you got your answer.
> >> > thx.
> >> > --
> >> > Read my book reviews at:
> >> > www.ITBookworm.com
> >> >
> >> > Blog Author of:
> >> > Database Underground -- http://weblog.infoworld.com/dbunderground/
> >> > DBA Rant – http://dbarant.blogspot.com
> >> >
> >> >
> >> >
> >> >
> >> > "Smith" wrote:
> >> >
> >> >> Thanks.. that means differential backup doesn't help me in this
> >> >> scanario?
> >> >>
> >> >> Thanks
> >> >>
> >> >> "Sean McCown" <SeanMcCown RemoveThis @discussions.microsoft.com> wrote in message
> >> >> news:CB701821-3B57-410A-B2BF-901A464CD455@microsoft.com...
> >> >> > In order to do a point in time recovery, you'll need to use the
> >> >> > STOPAT
> >> >> > command.
> >> >> > Here's an example I pulled out of BOL:
> >> >> >
> >> >> > RESTORE DATABASE AdventureWorks
> >> >> > FROM AdventureWorksBackups
> >> >> > WITH NORECOVERY;
> >> >> >
> >> >> > RESTORE LOG AdventureWorks
> >> >> > FROM AdventureWorksBackups
> >> >> > WITH RECOVERY, STOPAT = 'Apr 15, 2020 12:00 AM';
> >> >> >
> >> >> >
> >> >> > If you have several logs before the last one that you run with
> >> >> > recovery,
> >> >> > just do them with norecovery like you did the full backup. So if
> >> >> > you
> >> >> > had
> >> >> > previous logs to restore before you get to the one you want to stop
> >> >> > at
> >> >> > just
> >> >> > say:
> >> >> >
> >> >> > RESTORE LOG AdventureWorks FROM AdventureWorksBackups with
> >> >> > NORECOVERY
> >> >> >
> >> >> > You can replace that device name with a physical path if you need to
> >> >> > so
> >> >> > you
> >> >> > would also do this if you had a physical path to the file:
> >> >> >
> >> >> > RESTORE LOG AdventureWorks FROM disk='c:\mylog.trn' with NORECOVERY
> >> >> >
> >> >> > It's worth it to mention that you lose the STOPAT functionality if
> >> >> > you're
> >> >> > not in full recovery mode.
> >> >> >
> >> >> > And as Tibor said, you can only recovery to one point in time. So
> >> >> > if
> >> >> > you
> >> >> > need 2 separate times, you'll have to do 2 restores and merge them
> >> >> > manually
> >> >> > at the table level.
> >> >> >
> >> >> >
> >> >> > --
> >> >> > Read my book reviews at:
> >> >> > www.ITBookworm.com
> >> >> >
> >> >> > Blog Author of:
> >> >> > Database Underground -- http://weblog.infoworld.com/dbunderground/
> >> >> > DBA Rant – http://dbarant.blogspot.com
> >> >> >
> >> >> >
> >> >> >
> >> >> >
> >> >> > "Smith" wrote:
> >> >> >
> >> >> >> Can you tell me the step? how I can achieve this.
> >> >> >>
> >> >> >> Thanks
> >> >> >> "Tibor Karaszi" <tibor_please.no.email_karaszi RemoveThis @hotmail.nomail.com>
> >> >> >> wrote
> >> >> >> in
> >> >> >> message news:5ACAC0ED-970C-4BAD-BAD4-AFB7C675A81F@microsoft.com...
> >> >> >> > Restore will not let you get an inconsistent views of a database.
> >> >> >> > But
> >> >> >> > that
> >> >> >> > I mean that it won't allow for a restore where some objects are
> >> >> >> > from
> >> >> >> > one
> >> >> >> > point in time and other objects are from another point in time.
> >> >> >> > You
> >> >> >> > can
> >> >> >> > restore up to 10:05 into a new database name and move the desired
> >> >> >> > data
> >> >> >> > (TEST2) from there into the production database.
> >> >> >> >
> >> >> >> > --
> >> >> >> > Tibor Karaszi, SQL Server MVP
> >> >> >> > http://www.karaszi.com/sqlserver/default.asp
> >> >> >> > http://sqlblog.com/blogs/tibor_karaszi
> >> >> >> >
> >> >> >> >
> >> >> >> > "Smith" <naissani RemoveThis @hotmail.co> wrote in message
> >> >> >> > news:3C7DF62C-9595-4750-A6B4-6D7F695B4D85@microsoft.com...
> >> >> >> >> Hi Professionals,
> >> >> >> >>
> >> >> >> >> Following are my queries and I want to recover the table (Test2)
> >> >> >> >> with
> >> >> >> >> data and recover all the test1 data. I would appreciate if you
> >> >> >> >> can
> >> >> >> >> let
> >> >> >> >> me
> >> >> >> >> know the commands.
> >> >> >> >>
> >> >> >> >>
> >> >> >> >> use eZSale
> >> >> >> >> go
> >> >> >> >> create table TEST1 (ID INT, NAME VARCHAR(50))
> >> >> >> >> create table TEST2(ID INT, NAME VARCHAR(50))
> >> >> >> >>
> >> >> >> >> --Time:10:01
> >> >> >> >> BACKUP DATABASE eZSale to disk = 'C:\TEMP\test_FullDb.BAK'
> >> >> >> >>
> >> >> >> >> --Time:10:02
> >> >> >> >> INSERT INTO TEST1 SELECT 1, 'AFTER FULL BACKUP'
> >> >> >> >> INSERT INTO TEST2 SELECT 1, 'AFTER FULL BACKUP'
> >> >> >> >>
> >> >> >> >> --Time:10:03
> >> >> >> >> BACKUP LOG eZsale to disk = 'c:\Temp\test_Logbk.BAK'
> >> >> >> >>
> >> >> >> >> --Time: 10:04
> >> >> >> >> INSERT INTO TEST1 SELECT 2, 'AFTER LOG BACKUP'
> >> >> >> >> INSERT INTO TEST2 SELECT 2, 'AFTER LOG BACKUP'
> >> >> >> >> INSERT INTO TEST2 SELECT 3, 'AFTER LOG BACKUP..SECOND ENTRY'
> >> >> >> >>
> >> >> >> >> --Time: 10:05
> >> >> >> >> DROP TABLE TEST2
> >> >> >> >>
> >> >> >> >> --Time: 10:06
> >> >> >> >> BACKUP DATABASE eZSale to disk = 'C:\TEMP\test_DiffDb.BAK' with
> >> >> >> >> differential
> >> >> >> >>
> >> >> >> >> --Time: 10:07
> >> >> >> >> INSERT INTO TEST1 SELECT 4, 'AFTER DIFFERNTIAL BACKUP'
> >> >> >> >> INSERT INTO TEST1 SELECT 5, 'AFTER DIFFERNTIAL BACKUP..SECOND
> >> >> >> >> ENTRY'
> >> >> >> >
> >> >> >>
> >> >>
> >>
>
 >> Stay informed about: Point in time recovery 

Smith,

Yes, that will work.

RLF

"Smith" <naissani.TakeThisOut@hotmail.co> wrote in message
news:AD1D7BD7-118B-424E-A256-F06B4374B275@microsoft.com...
> thanks but if we first restore full backup with norecovery and then
> differential backup with norecovery and then log backup with recovery with
> StopAt option, will that give point in time recovery?
>
> "Sean McCown" <SeanMcCown.TakeThisOut@discussions.microsoft.com> wrote in message
> news:576CD573-BFFD-4D78-AD16-A64C116267EB@microsoft.com...
>> That's exactly what that means.
>> So just to state it to be clear... you have to use log backups to have
>> point
>> in time recovery. You also have to be in full recovery mode.
>> Bulk-logged
>> won't give you point in time.
>>
>> --
>> Read my book reviews at:
>> www.ITBookworm.com
>>
>> Blog Author of:
>> Database Underground -- http://weblog.infoworld.com/dbunderground/
>> DBA Rant - http://dbarant.blogspot.com
>>
>>
>>
>>
>> "Smith" wrote:
>>
>>> That means I can't differential backup when I am trying to do point in
>>> time
>>> recovery?
>>>
>>> "Sean McCown" <SeanMcCown.TakeThisOut@discussions.microsoft.com> wrote in message
>>> news:6CEEC45C-62DA-42C9-9512-4C1681D29A9D@microsoft.com...
>>> > You are correct. You lost STOPAT with diffs.
>>> >
>>> > Don't forget to mark the issue as resolved if you got your answer.
>>> > thx.
>>> > --
>>> > Read my book reviews at:
>>> > www.ITBookworm.com
>>> >
>>> > Blog Author of:
>>> > Database Underground -- http://weblog.infoworld.com/dbunderground/
>>> > DBA Rant - http://dbarant.blogspot.com
>>> >
>>> >
>>> >
>>> >
>>> > "Smith" wrote:
>>> >
>>> >> Thanks.. that means differential backup doesn't help me in this
>>> >> scanario?
>>> >>
>>> >> Thanks
>>> >>
>>> >> "Sean McCown" <SeanMcCown.TakeThisOut@discussions.microsoft.com> wrote in message
>>> >> news:CB701821-3B57-410A-B2BF-901A464CD455@microsoft.com...
>>> >> > In order to do a point in time recovery, you'll need to use the
>>> >> > STOPAT
>>> >> > command.
>>> >> > Here's an example I pulled out of BOL:
>>> >> >
>>> >> > RESTORE DATABASE AdventureWorks
>>> >> > FROM AdventureWorksBackups
>>> >> > WITH NORECOVERY;
>>> >> >
>>> >> > RESTORE LOG AdventureWorks
>>> >> > FROM AdventureWorksBackups
>>> >> > WITH RECOVERY, STOPAT = 'Apr 15, 2020 12:00 AM';
>>> >> >
>>> >> >
>>> >> > If you have several logs before the last one that you run with
>>> >> > recovery,
>>> >> > just do them with norecovery like you did the full backup. So if
>>> >> > you
>>> >> > had
>>> >> > previous logs to restore before you get to the one you want to stop
>>> >> > at
>>> >> > just
>>> >> > say:
>>> >> >
>>> >> > RESTORE LOG AdventureWorks FROM AdventureWorksBackups with
>>> >> > NORECOVERY
>>> >> >
>>> >> > You can replace that device name with a physical path if you need
>>> >> > to so
>>> >> > you
>>> >> > would also do this if you had a physical path to the file:
>>> >> >
>>> >> > RESTORE LOG AdventureWorks FROM disk='c:\mylog.trn' with NORECOVERY
>>> >> >
>>> >> > It's worth it to mention that you lose the STOPAT functionality if
>>> >> > you're
>>> >> > not in full recovery mode.
>>> >> >
>>> >> > And as Tibor said, you can only recovery to one point in time. So
>>> >> > if
>>> >> > you
>>> >> > need 2 separate times, you'll have to do 2 restores and merge them
>>> >> > manually
>>> >> > at the table level.
>>> >> >
>>> >> >
>>> >> > --
>>> >> > Read my book reviews at:
>>> >> > www.ITBookworm.com
>>> >> >
>>> >> > Blog Author of:
>>> >> > Database Underground -- http://weblog.infoworld.com/dbunderground/
>>> >> > DBA Rant - http://dbarant.blogspot.com
>>> >> >
>>> >> >
>>> >> >
>>> >> >
>>> >> > "Smith" wrote:
>>> >> >