Wednesday, March 21, 2012

Point in time recovery.

Can we do point in time recovery if I have a full backup... I don't have a
log backup.
Hi
No. The log is used to "replay" transactions to the point.
You need the full backup, plus all the logs after that, to at least the
point in time you want to restore to.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Rogers" <Rogers@.discussions.microsoft.com> wrote in message
news:83FAE7EA-BBF8-4937-BCBA-353B41531C24@.microsoft.com...
> Can we do point in time recovery if I have a full backup... I don't have a
> log backup.
|||Hi,
To add on to Mike, your database must be in FULL recovery model; if you need
to perform POIN IN TIME recovery.
If your database is FULL recovery model, you can do:-
1. Perform a transaction log backup of the original database (If you have
not performed one)
2. RESTORE DATABASE TEST1 FROM Backupfile(Give the correct backup file /
device name) WITH NORECOVERY
3. Restore the subsequent transaction log files in order of backup WITH
NORECOVERY option till the final transaction log file
4. In the the final transaction log restore mention WITH RECOVERY and
STOPAT='date and time'
Step 3 can be ignored if you have not performed any transaction log backups
after the full database backup. Incase if you took the transaction log
backup and if
you lost the files then we cant perform poin in time recovery.
Thanks
Hari
SQL Server MVP
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:OAbp0IKNFHA.1104@.TK2MSFTNGP10.phx.gbl...
> Hi
> No. The log is used to "replay" transactions to the point.
> You need the full backup, plus all the logs after that, to at least the
> point in time you want to restore to.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Rogers" <Rogers@.discussions.microsoft.com> wrote in message
> news:83FAE7EA-BBF8-4937-BCBA-353B41531C24@.microsoft.com...
>

No comments:

Post a Comment