Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Recovery


Recovery

Author
Message
GrahamO-735217
GrahamO-735217
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 41
Comments posted to this topic are about the item Recovery

Graham Okely B App Sc
Senior Database Administrator
magarity kerns
magarity kerns
SSC-Addicted
SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)

Group: General Forum Members
Points: 438 Visits: 397
Very good use of narrative style storytelling. Fun to read after dry just-the-facts-ma'am articles.

One lesson the author learned that IMO needs a stronger emphasis: before INSERT (or any other operation) on a live table, check the structure, FK's and PK's. Then the Identity column (or other constraints) won't suprise you.

It's also a good idea to use explicit transactions and don't commit them until it looks good when restoring individual rows in this manner to production.
SQLPhil
SQLPhil
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1516 Visits: 740
I've had to do something similar myself on our intranet database. One of the webteam ran a procedure that completely screwed up our forums, but because the database is also used to store personnel information I couldn't do a blanket restore of the DB. Restoring it spearately then allowed me to just recover those tables that had been corrupted (and a stern talking to the webteam to test their procedures before running them on the production system!)
ABCDEF-848380
ABCDEF-848380
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 42
I found this article for absolute beginners. Is this really Senior DBA?
SQLPhil
SQLPhil
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1516 Visits: 740
ABCDEF (9/29/2008)
I found this article for absolute beginners. Is this really Senior DBA?


I'd disagree. It doesn't sound like rocket science granted, but sometimes the most simple of solutions can be overlooked, particularly in the heat of the moment when you know you need to restore lost or corrupt data.

Keeping a level head, examing all the available options, and not making rash decisions are characteristics you would expect from a senior dba.
dougjjj
dougjjj
Mr or Mrs. 500
Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)

Group: General Forum Members
Points: 525 Visits: 644
This isn't something uncommon, I'd be surprised if you could find a DBA that hasn't had to restore bits of data people have deleted. Possible ways to speed up the process might be more interesting reading for an article.

A few ways we recover data quickly when we need to ...

We run log shipping on our servers so there is usually a 5-15 minute lag between copying data between servers, if one of the DBAs delete some data by accident (rare but has happened before) you can grab it off the log shipping server before the transaction is played there. In theory if someone else in the business deleted data you could do this too but they would have to call pretty quick.

Another way we have of quickly restoring data is that we run a 24 hour old version of the database, each night we backup to various locations and then restore that data to a seperate server which is sometimes used for testing or running certain reports. If something is removed in the last day we can usually recover it from here.

Last option is going back to backups and every DBAs best friend is Litespeed or one of the other compressed backup solutions. Once our databases got past 50gb we found the possible downtime wasn't acceptable to the business (it took about 6 hours to get things back up and running) and now even 100gb databases can be restored in about an hour once you factor in copying it from remote servers etc

Another option is delete triggers, our ERP system uses them for auditing which can be handy from time to time but we haven't tried it on any of the other systems.



Chuck Hottle
Chuck Hottle
Old Hand
Old Hand (376 reputation)Old Hand (376 reputation)Old Hand (376 reputation)Old Hand (376 reputation)Old Hand (376 reputation)Old Hand (376 reputation)Old Hand (376 reputation)Old Hand (376 reputation)

Group: General Forum Members
Points: 376 Visits: 1204
There are also products that allow for object-level recovery without restoring the database too. We use LiteSpeed and I've tested that functionality and it works fine.
William Vach
William Vach
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3215 Visits: 1732
ABCDEF (9/29/2008)
I found this article for absolute beginners. Is this really Senior DBA?


What's wrong with that? This forum should be open to SQL Server DBA's at every level. This is the best way for someone to learn.

I thought that it was a good step by step guide on how to accomplish this common task.
Chad Crawford
 Chad Crawford
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2630 Visits: 18703
You also need to watch foreign keys for other related tables. In this case it sounds like there were no interrelationships. But if data were deleted from a parent table, and then a child table, you would need to track and maintain the primary key on the parent during the "restore" insert so that you could correctly link up the child back to the parent's primary key. In that case, it would be easier to turn the IDENTITY_INSERT ON so that the records would flow smoothly back into the database from the backup copy, maintaining their key values. Another case where IDENTITY_INSERT might be a preferred solution is if the end user knew the primary key values and referenced them (like an account number). In that case, it would also be important to preserve the original key values.

Chad
Andy Ognenoff
Andy Ognenoff
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 11
Another solution is third party tools. I haven't tested this but we just got Red Gate's SQL Backup Pro (moved away from Litespeed) and SQL Data Compare Pro. SQL Data Compare Pro can use SQL Backup Pro backups as a source for doing comparisons and synchronizations with live databases - then it scripts the changes and you run it against the live database. I'm hoping that'll save me some time in the future when situations like this come up.
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