SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Recovery to a Point in Time


Recovery to a Point in Time

Author
Message
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (161K reputation)SSC Guru (161K reputation)SSC Guru (161K reputation)SSC Guru (161K reputation)SSC Guru (161K reputation)SSC Guru (161K reputation)SSC Guru (161K reputation)SSC Guru (161K reputation)

Group: General Forum Members
Points: 161683 Visits: 33199
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 and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Steve Schmidt
Steve Schmidt
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (161K reputation)SSC Guru (161K reputation)SSC Guru (161K reputation)SSC Guru (161K reputation)SSC Guru (161K reputation)SSC Guru (161K reputation)SSC Guru (161K reputation)SSC Guru (161K reputation)

Group: General Forum Members
Points: 161683 Visits: 33199

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 and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Manie Verster
Manie Verster
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3139 Visits: 1044
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.BigGrinBigGrinBigGrin

:-PManie 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)
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (161K reputation)SSC Guru (161K reputation)SSC Guru (161K reputation)SSC Guru (161K reputation)SSC Guru (161K reputation)SSC Guru (161K reputation)SSC Guru (161K reputation)SSC Guru (161K reputation)

Group: General Forum Members
Points: 161683 Visits: 33199
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 and SQL Server Execution Plans
Product Evangelist for Red Gate Software
folkxyz01
folkxyz01
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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?
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (161K reputation)SSC Guru (161K reputation)SSC Guru (161K reputation)SSC Guru (161K reputation)SSC Guru (161K reputation)SSC Guru (161K reputation)SSC Guru (161K reputation)SSC Guru (161K reputation)

Group: General Forum Members
Points: 161683 Visits: 33199
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 and SQL Server Execution Plans
Product Evangelist for Red Gate Software
folkxyz01
folkxyz01
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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?
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (161K reputation)SSC Guru (161K reputation)SSC Guru (161K reputation)SSC Guru (161K reputation)SSC Guru (161K reputation)SSC Guru (161K reputation)SSC Guru (161K reputation)SSC Guru (161K reputation)

Group: General Forum Members
Points: 161683 Visits: 33199
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 and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Rich Mechaber
Rich Mechaber
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4353 Visits: 3690
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search