Recovery to a Point in Time

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Grant Fritchey » Recovery to a Point in Time

20 posts, Page 2 of 2

««1 2

Recovery to a Point in Time

Rate Topic

Display Mode

Topic Options

Author

Message

Grant Fritchey

Posted Thursday, June 16, 2005 8:12 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 4:50 AM
Points: 13,371, Visits: 25,143

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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #191288

Steve Schmidt

Posted Saturday, June 17, 2006 5:08 PM

Forum 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

Grant Fritchey

Posted Monday, June 19, 2006 6:14 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 4:50 AM
Points: 13,371, Visits: 25,143

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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #288474

Manie Verster

Posted Friday, November 21, 2008 5:11 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 7:01 AM
Points: 1,151, Visits: 878

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

Tongue

Manie Verster
Developer
Johannesburg
South Africa

Life is about choices.... I choose to be happy today

Post #606426

Grant Fritchey

Posted Friday, November 21, 2008 6:59 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 4:50 AM
Points: 13,371, Visits: 25,143

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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #606484

folkxyz01

Posted Tuesday, February 10, 2009 11:56 AM

Forum 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

Grant Fritchey

Posted Tuesday, February 10, 2009 12:34 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 4:50 AM
Points: 13,371, Visits: 25,143

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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #654115

folkxyz01

Posted Wednesday, February 11, 2009 8:55 AM

Forum 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

Grant Fritchey

Posted Wednesday, February 11, 2009 9:09 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 4:50 AM
Points: 13,371, Visits: 25,143

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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #654962

rmechaber

Posted Thursday, July 22, 2010 7:23 AM

SSChasing Mays

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 7:43 AM
Points: 656, Visits: 2,921

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 Smile

: 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 »

20 posts, Page 2 of 2

««1 2

Permissions