Re: Point in time recovery

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: Eseutil and restored database - Jet database errors
    ... database repair (hard recovery). ... It approximately spends 1 hour for 6~8 GB database. ... Please refer to the "Method 3: Restore a Mailbox from Backup to a Recovery ...
    (microsoft.public.exchange.admin)
  • Re: Crash recovery after shutdown immediate?
    ... database stated it wanted a recovery. ... it seems that a crash recovery occured. ... ARCH: Archiving is disabled ... After that, I did a backup, NOT a restore. ...
    (comp.databases.oracle.server)
  • Re: Backend Exchange migrate to New Hardware (Server)
    ... Exchange mailbox stores and Exchange public folder stores. ... When you use Backup to restore Exchange databases, ... Storage Engine to restore Exchange database files and their ...
    (microsoft.public.exchange.setup)
  • Re: HELP!!! restore database - left in loading state?? PLEASE H
    ... RESTORE DATABASE WITH RECOVERY ... Columnist, SQL Server Professional ...
    (microsoft.public.sqlserver.tools)
  • Re: change status for standby database
    ... " The RESTORE process will apply the UNDO... ... > Tibor Karaszi, SQL Server MVP ... >> internally hacking a system table that would make the database available.. ... >>> You can issue a RESTORE from Query Analyzer with only the RECOVERY option ...
    (microsoft.public.sqlserver.server)
Loading