Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 149
  • Last Modified:

MS SQL Point-In-Time Recovery from offline backup

Hi,

If I have a snapshot backup or "offline backup"  of MS SQL 2008 R2 @ 11pm every night. We also plan having transaction log backup running every 2 hours - it is possible to achieve point-in-time recovery by restoring the offline backup, start in recovery mode and apply transaction log? Any special steps required?
0
Keng0499
Asked:
Keng0499
2 Solutions
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
I believe not.

Firstly, you can't roll forward from a snapshot, so you can't use the transaction logs automatically to go forward to point-in-time recovery.

Secondly, please don't rely on snapshots as being your only backup. Using only this method, if the database becomes corrupted, then so could your snapshot.

Have a look at this page for more information: http://msdn.microsoft.com/en-GB/library/ms189281.aspx
0
 
Keng0499Author Commented:
Thanks for response.

I was not referring to the Database_Snapshot - it is more of a backup via SQL writer which allows restore back the MDF and LDF file.
0
 
Scott PletcherSenior DBACommented:
You can't recover directly from those, as SQL does not allow "attach with norecovery" (maybe in SQL 2030 :-) ).

You could try attaching the files, backing that up, then restoring with norecovery so that you can apply the log file(s).  I don't know if that will work or not, but I can't think of anything else that would have a chance.  Be sure the db name is the same name as the original db, just in case.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now