SQLServerCentral / Discuss Content Posted by Grant Fritchey / Article Discussions / Article Discussions by Author / Recovery to a Point in Time / Latest Posts

RE: Recovery to a Point in Time

rmechaber — Thu, 22 Jul 2010 07:23:55 GMT

Great article, thanks!I keep a folder on the server called "Disaster Management". It contains everything I need in an emergency to keep my head :-) : scripts to find current executing processes, to display server configuration settings, to kill all connections and establish DAC connection, to enumerate log files, and to perform the restores. This was a good discussion/presentation of killing connections and setting a DB to single user mode:[url=http://www.sqlservercentral.com/scripts/Maintenance/62619/]http://www.sqlservercentral.com/scripts/Maintenance/62619/[/url].HTH,Rich

RE: Recovery to a Point in Time

Grant Fritchey — Wed, 11 Feb 2009 09:09:07 GMT

No, not really. Without a log backup of some sort, there's nothing to restore from for point in time recoveries. You have to set up maintenance.

RE: Recovery to a Point in Time

folkxyz01 — Wed, 11 Feb 2009 08:55:23 GMT

[quote][b]Grant Fritchey (2/10/2009)[/b][hr]If you mean that the database goes away due to a media failure and you were unable to backup the log file, then you can only restore up to the last good log file backup, when ever that was.[/quote]That's exactly what I want to confirm.I know Oracle has the concept of incomplete restore and point-in-time recovery by cancel, by time, etc, by using archive log. Which in that case, even if you didn't setup any maintenance plan for any scheduled backups, you can still use Oracle internal automatic archive log files to restore the database to point-in-time.In SQL Server, it doesn't have the similar term?

RE: Recovery to a Point in Time

Grant Fritchey — Tue, 10 Feb 2009 12:34:47 GMT

If you mean that the database goes away due to a media failure and you were unable to backup the log file, then you can only restore up to the last good log file backup, when ever that was.

RE: Recovery to a Point in Time

folkxyz01 — Tue, 10 Feb 2009 11:56:48 GMT

What about a media failure, and backup the log file is impossible. Can't we recover the database to the time just before this happened, like Oracle can do by using it's archivelog files?

RE: Recovery to a Point in Time

Grant Fritchey — Fri, 21 Nov 2008 06:59:22 GMT

That was just a typo. Sorry about about the confusion. The script is right and the text is slightly off.

RE: Recovery to a Point in Time

Manie Verster — Fri, 21 Nov 2008 05:11:51 GMT

Grant, I have been motivated by your article and am trying this myself. I just have two things that I don't understand. You say that the last database backup was made at midnight and that the database crashed at 08:37 which gives you 35 backups up to 08:45 but obviously you do not want the bad logs so that is why you stop at 08:37. Why the is your @FileCount only 18? 'Scuse me if I'm stupid or maybe it's just a hypothetical figure?That's all I want to know.:D:D:D

RE: Recovery to a Point in Time

Grant Fritchey — Mon, 19 Jun 2006 06:14:00 GMT

Absolutely helpful. Those are some really important details that I didn't include in the article.

Funny thing about writing this kind of article, you keep thinking of other details that should be added to cover other kinds of crashes & recoveries, etc. It can make you nuts.

Thanks for filling in the gaps.

RE: Recovery to a Point in Time

Steve Schmidt — Sat, 17 Jun 2006 17:08:00 GMT

The best way to deal with a crashed database is to attempt to backup the log before starting the restore sequence:BACKUP LOG TO WITH NO_TRUNCATE,NORECOVERYNO_TRUNCATE is used to allow sql to try its best to get the tail of the log. This is necessary if the database will not start normally.NORECOVERY is used to take the database into a Restoring state. That prevents anyone from connecting and running more transactions.Even if you plan to restore to a point in time prior to the end of the previous log backup, it still might be a good idea to backup the remainder of the log:1. you might find out later that you really want to recover to a later point in time.2. you might want to recover to a later point to allow for some problem analysis.Hope that helps-Steve

RE: Recovery to a Point in Time

Grant Fritchey — Thu, 16 Jun 2005 08:12:00 GMT

We have an emergency response team that practices recoveries from various issues once a month. Almost all them involves restoring to a point in time. There's nothing worse than have several VP's standing in my cube while I try to figure something out. Better to have the scripts ready to go before they have a chance to congregate.

RE: Recovery to a Point in Time

Grant Fritchey — Thu, 16 Jun 2005 07:41:00 GMT

One thing not mentioned in the article, if possible, take one more log backup. If not possible, you may lose 9 minutes worth of data.

RE: Recovery to a Point in Time

Grant Fritchey — Thu, 16 Jun 2005 07:38:00 GMT

"Yes, there's the rub. How do you get the users out of the system and lock it down? We do not have a DBA here, so as senior programmer, I get to do most of these tasks. I haven't found a good explanation as to how to do this. Can someone enlighten me?"

I'd try it this way:

ALTER DATABASE X SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE

This is harsh and won't make you popular, but it does kick everyone that isn't a member of db_owner role out of the system PDQ.

However, if you've got a bunch of users in the db_owner role, in addition to have other issues, you'll need to try a different approach.

RE: Recovery to a Point in Time

Ian Yates — Thu, 16 Jun 2005 07:20:00 GMT

If you are able to, take a log backup of it after 8:40 (depending on the type of crash I suppose). Then you can do the restoration stopping at 8:39. If you cannot back it up after your 8:40 crash, then I suppose you are in lots more trouble Maybe try some repairs using DBCC then log backup and restore to 8:39?

Anyone have further ideas?

RE: Recovery to a Point in Time

Blake Bishop — Wed, 15 Jun 2005 15:47:00 GMT

There are several scripts on the web that kill SQL Server connections; here are a couple to try:

http://www.extremeexperts.com/SQL/Scripts/default.aspx (look for the Kill Connections script)

http://www.databasejournal.com/scripts/article.php/2110211

Blake

RE: Recovery to a Point in Time

Owen Mortensen — Wed, 15 Jun 2005 10:13:00 GMT

From the article:

"After you get the users out of the system and lock it down so that you’re not contending with them (if there are connections into a database, you can’t restore it),"

Yes, there's the rub. How do you get the users out of the system and lock it down? We do not have a DBA here, so as senior programmer, I get to do most of these tasks. I haven't found a good explanation as to how to do this. Can someone enlighten me?

TIA,

Owen

RE: Recovery to a Point in Time

vidya_pande — Wed, 15 Jun 2005 05:33:00 GMT

Suppose my database crashes at 8.40 am. I just have last log backup at taken at 8.30 am. Now i want to restore it upto 8.39 am. How would you do it ?

RE: Recovery to a Point in Time

Malcolm Leach — Wed, 15 Jun 2005 03:07:00 GMT

Excellent article as usual Grant!

I think the best part is the advice to practice the recovery steps when you're not under the cosh. In fact I would recommend that all production DBAs do this at least once a week because:

a) You are absolutely, 100% proving that your backups are working and can be used. Backups are meaningless until it can be proved that they can be recovered from in the event of a disaster. By doing a test recovery once a week you are ensuring that everything is still as it should be. I worked at a company where they had set up an excellent backup plan but, somehow, the incremental backup job got disabled and it wasn't noticed until, you guessed it, it was needed to get the company system back online! The DBA and the company went their separate ways.

b) For the reason that Grant stated - you will be familiar with the steps involved. Doing this once a week burns this knowledge into your brain. Practice, practice, practice.

If you're in a team then this shouldn't be too arduous and should be cycled round the team making sure that everyone knows how to do it without having to think about it.

Malc

RE: Recovery to a Point in Time

SQLPhil — Wed, 15 Jun 2005 02:45:00 GMT

As someone who's inherited the DBA role, I'm having to trawl through our databases and look at our backup plans. Articles like this serve to highlight some of the glaring errors some of my predecessors made which hopefully I can now make right (with a little help from my friends at SQL Server Central).

Very useful article!! Thanks

RE: Recovery to a Point in Time

Sunil_Singh — Wed, 15 Jun 2005 01:44:00 GMT

It is a very good document and very useful for all the DBA's. I would rate 5 star for this.

cheers

Sunil S.

Recovery to a Point in Time

Grant Fritchey — Sat, 04 Jun 2005 13:20:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/gFritchey/recoverytoapointintime.asp