September 9, 2015 at 5:23 am
Hello Masters,
We got request from our client to have point in time restore.
Full Backup: Everyday at 2 AM.
Log backup Everyday 2 PM
Now client wants us the data upto 8 AM!!
I will restore full backup of 2 AM, but how can I restore data up to 8 AM? Is it possible to restore data as per time stamp ?
September 9, 2015 at 5:26 am
Restore the full backup. Restore the log backup WITH STOPAT '08:00'
Once you've done that, fix this backup strategy as it allows a full day's data loss. If a full day is acceptable, then just the full backup's good enough. If a full day isn't acceptable, then you need to change the log backup frequency to allow for the acceptable data loss.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 9, 2015 at 5:46 am
Thanks GilaMonster,
I will try to restore log file with given strategy.
September 9, 2015 at 5:47 am
Once you're done doing what Gail said, I suggest you set up a plan where you perform test restores of databases regularly.
The first thing it'll do is to confirm that your backups are viable and can actually be used to restore data. The other think it'll do is to give you practice at doing a restore. You never want your first time restoring a database to be when you actually need it. This is one of those basic things you should be familiar with soon after starting a DBA position.
September 9, 2015 at 6:05 am
You really need to take the log backups more frequently. Once an hour at least. That puts you in a situation where you could lose up to an hour of data. Check with the client just how much data they're prepared to live without.
Here's an article I wrote on how to do a point in time restore.[/url] Here's another by Kathi Kellenberger[/url]. Those should help. Here's a Simple-Talk article I wrote on backup basics[/url]. You really need to get on top of your backups.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 9, 2015 at 7:43 am
jitendra.padhiyar (9/9/2015)
Hello Masters,We got request from our client to have point in time restore.
Full Backup: Everyday at 2 AM.
Log backup Everyday 2 PM
Now client wants us the data upto 8 AM!!
I will restore full backup of 2 AM, but how can I restore data up to 8 AM? Is it possible to restore data as per time stamp ?
Just a recommendation to make you and the company you work for look better in the eyes of your clients...
Based on your question, you good folks don't actually know anything substantial about backups or restores. Do you folks have a Disaster Recovery Plan to protect your clients?
Since you're apparently "in the business" of taking care of client's data, you really need to learn how to do that a whole lot better. Just as an example and to emphasize what Grant suggested, you need to do log file backups more often if for no other reason other than to keep the log file smaller. Unlike the data files (mdf/ndf usually) which can benefit from instant file initialization, log files have to have VLFs built in them (think formatted in a way) before they are brought online. The bigger they are, the longer the system can take to do a restore. A whole lot can happen in one day especially if there are overnight batch runs and/or file loads. You also need to practice such restores on a regular basis to make sure that you can do it quickly and correctly not to mention it's a really good test to make sure that you can actually do a restore. As someone once said, it's not the backups that count... it's the restores that will save your butt.
That just scratches the surface of what I'm talking about. You need to make a recovery plan and then figure out how to do the backups to restore to that plan.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 9, 2015 at 9:46 am
Jeff Moden (9/9/2015)
You need to make a recovery plan and then figure out how to do the backups to restore to that plan.
I'd like to draw attention to what Jeff said here. I'm sure the phrase "make a recovery plan" wasn't a mistake. It shifts the focus of taking backups to not just be about taking backups. The reason for taking backups is so that you can recover data if needed. Thinking about having a "recovery plan" shifts the focus to what it's really about.
Then, practice and test regularly.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply