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 (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)

Group: General Forum Members
Points: 55089 Visits: 32797
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/gFritchey/recoverytoapointintime.asp

----------------------------------------------------
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
Sunil_Singh
Sunil_Singh
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 1

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

cheers

Sunil S.


SQLPhil
SQLPhil
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1642 Visits: 740

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


Malcolm Leach
Malcolm Leach
SSC Veteran
SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)

Group: General Forum Members
Points: 277 Visits: 71

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



Malcolm
DB Ghost - Build, compare and synchronize from source control = Database Change Management for SQL Server
www.dbghost.com
vidya_pande
vidya_pande
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1267 Visits: 242
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 ?



Owen Mortensen
Owen Mortensen
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

Group: General Forum Members
Points: 64 Visits: 18

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


Blake Bishop
Blake Bishop
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 1

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)

or

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

Blake


Ian Yates
Ian Yates
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2492 Visits: 445

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?





Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)

Group: General Forum Members
Points: 55089 Visits: 32797

"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.



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

Group: General Forum Members
Points: 55089 Visits: 32797
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.

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