Re: Point in time recovery

Smith,

Yes, that will work.

RLF

"Smith" <naissani@xxxxxxxxxx> wrote in message
news:AD1D7BD7-118B-424E-A256-F06B4374B275@xxxxxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:576CD573-BFFD-4D78-AD16-A64C116267EB@xxxxxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:6CEEC45C-62DA-42C9-9512-4C1681D29A9D@xxxxxxxxxxxxxxxx
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: Audacity and Gentoo
    ... > What's the UPS, btw? ... Still, a difference system would save a lot of space, unless database ... It would also be useful in case of data recovery as you'd at least be ... > Backup and Point-In-Time Recovery'). ...
    (uk.comp.os.linux)
  • Re: How can I back up a log-shipped database?
    ... When you bring a db out of standby mode, you get the recovery ... log backup onto this, as the log records in that log backup are totally out-of sync with the database you have ... Or MS would need to change SQL Server so it allow us to do a backup of a database in STANDBY mode. ...
    (microsoft.public.sqlserver.server)
  • Re: Shared databases and improving reliability
    ... For me that's one of the major tasks of a database! ... The mere job of data conversion is rather cumbersome for FMP itself ... After recovery the 700 000 records where available ... The backup solution ...
    (comp.databases.filemaker)
  • Re: Oracle: how to demonstrate successful restore?
    ... > We could always backup the database in tape, but we might NEVER get it ... That's called a black hole backup. ... tape could be bad after leaving there for a while. ... Are you sure that the tape is ripe enough for a recovery ...
    (comp.databases.oracle.server)
  • Re: Point in time recovery
    ... RESTORE DATABASE AdventureWorks ... just do them with norecovery like you did the full backup. ... RESTORE LOG AdventureWorks FROM AdventureWorksBackups with NORECOVERY ... create table TEST1 ...
    (microsoft.public.sqlserver.clients)
Loading