Point in time restore from full backup | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Point in time restore from full backup

Folks,
Pls advice me if it is possible to perform point in time recovery from a sql 2005 full backup.I believe it is possible.In sql 2000 we can perform point in time only by restoring full backup with norecovery and applying tran logs by specifying WITH STOPAT clause and with recovery/standby……..pls correct me if i am wrong. Guyz,
Assume the following scenario!!!! I created a table @11:00am inserted sum 15 records @11:05 am….now i again insert some 10 records @11:10 am…then take a full backup @11:12 am.Now i tried to restore the full backup as a new db by using ‘WITH STOPAT’11:06 am clause……and i am unable to perform the same……..pls helpguys Regards
Deepak
SQL DBA
STOP AT option works with only TLOG backups not with FULL or Differential backup….
If you have the previous full backup and tlog backups then you can restore with point in time recovery…if not then you are out of luck… All you can do it you restore full backup and delete the data manaullay…
or
Run the tlog backup after full then use that tlog backup to do the point in time recovery..
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
hi mohammed,
pls refer the link!!!!!it says it is possible…..but i am unable to perform the same…. http://blogs.msdn.com/arvindsh/archive/2007/06/15/the-role-of-stopat-in-restore-database.aspx Regards
Deepak
SQL DBA
Yes, it is an addition to RESTORE DATABASE command..
But read the blog carefully…the following is the one sentence from the same blog… Without log backups, there is simply no way to achieve a true point-in-time restore
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
… also as referred reviewhttp://msdn2.microsoft.com/en-us/library/ms191468(SQL.90).aspx that is explained where and when it should be used. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
]]>