Re: 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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:CB701821-3B57-410A-B2BF-901A464CD455@xxxxxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxxxx> wrote
in
message news:5ACAC0ED-970C-4BAD-BAD4-AFB7C675A81F@xxxxxxxxxxxxxxxx
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@xxxxxxxxxx> wrote in message
news:3C7DF62C-9595-4750-A6B4-6D7F695B4D85@xxxxxxxxxxxxxxxx
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'



.

Relevant Pages

  • Re: Point in time recovery
    ... You can restore up to 10:05 into a new database name and move the desired data (TEST2) from there into the production database. ... Following are my queries and I want to recover the table with data and recover all the test1 data. ... 'AFTER FULL BACKUP' ...
    (microsoft.public.sqlserver.clients)
  • Re: Point in time recovery
    ... differential backup with norecovery and then log backup with recovery with ... will that give point in time recovery? ... Database Underground -- http://weblog.infoworld.com/dbunderground/ ... RESTORE LOG AdventureWorks ...
    (microsoft.public.sqlserver.clients)
  • Re: Point in time recovery
    ... You also have to be in full recovery mode. ... RESTORE DATABASE AdventureWorks ... just do them with norecovery like you did the full backup. ... RESTORE LOG AdventureWorks FROM AdventureWorksBackups with NORECOVERY ...
    (microsoft.public.sqlserver.clients)
  • RE: How do I restore from mdf and ldf files?
    ... Tasks -> Backup Database ... Tasks -> Restore Database ... Windows 2003 Server with Latest Service Pack ... Pre-requisites for Sharepoint Backup and Restore: ...
    (microsoft.public.sharepoint.portalserver)
  • Re: RMAN restore of full backup fails completely
    ... You are not using a catalog database. ... You might have autobackup controlfile on, ... Your autobackup has been made *prior* to the backup! ... plus archivelog', when i try a restore on the other server however, the ...
    (comp.databases.oracle.server)
Loading