Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Recovery to a Point in Time Expand / Collapse
Author
Message
Posted Thursday, June 16, 2005 8:12 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:53 AM
Points: 13,890, Visits: 28,285
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.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #191288
Posted Saturday, June 17, 2006 5:08 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, April 18, 2010 6:05 PM
Points: 1, Visits: 3
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,NORECOVERY

NO_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
Post #288348
Posted Monday, June 19, 2006 6:14 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:53 AM
Points: 13,890, Visits: 28,285

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.



----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #288474
Posted Friday, November 21, 2008 5:11 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 25, 2014 5:21 AM
Points: 1,205, Visits: 923
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


Manie Verster
Developer
Johannesburg
South Africa

I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
Post #606426
Posted Friday, November 21, 2008 6:59 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:53 AM
Points: 13,890, Visits: 28,285
That was just a typo. Sorry about about the confusion. The script is right and the text is slightly off.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #606484
Posted Tuesday, February 10, 2009 11:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 17, 2009 10:20 AM
Points: 2, Visits: 6
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?
Post #654086
Posted Tuesday, February 10, 2009 12:34 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:53 AM
Points: 13,890, Visits: 28,285
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.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #654115
Posted Wednesday, February 11, 2009 8:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 17, 2009 10:20 AM
Points: 2, Visits: 6
Grant Fritchey (2/10/2009)
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.


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?
Post #654938
Posted Wednesday, February 11, 2009 9:09 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:53 AM
Points: 13,890, Visits: 28,285
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.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #654962
Posted Thursday, July 22, 2010 7:23 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 7:50 AM
Points: 705, Visits: 3,019
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:
http://www.sqlservercentral.com/scripts/Maintenance/62619/
.

HTH,
Rich
Post #957170
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse