September 29, 2008 at 1:23 am
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.
September 29, 2008 at 4:27 am
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!)
September 29, 2008 at 5:15 am
I found this article for absolute beginners. Is this really Senior DBA?
September 29, 2008 at 5:25 am
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.
September 29, 2008 at 5:51 am
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.
September 29, 2008 at 6:28 am
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.
September 29, 2008 at 8:26 am
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.
September 29, 2008 at 2:45 pm
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
September 29, 2008 at 2:54 pm
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.
September 29, 2008 at 11:45 pm
Last time I tested the Litespeed object level restore it took about the same amount of time as a full restore so i haven't bother with it since, anyone used it recently and can comment about object restore times compared to full database restores on medium/larger databases (50gb-100gb+)?
Cheers
September 30, 2008 at 4:51 am
Good article. It was so nice that freshers will understand it very easily. Great effort. 🙂
September 30, 2008 at 10:53 am
Not to be well mean. But building a restore next to a prod DB and comparing data should be what we always do. I'm mean your just sewing together something that is missing. You can't very well roll back a DB to yesterday just because 16 rows where dumped. That would be well foolish.
I will say that I have had to do something similar to what your outlining about 5 times this year alone. One mistake by an analyst or developer can have you up all night. Should we lock them out of PROD, yes of course we do, but what I see in these events is someone drops code that deletes more then it should and once given the right well it can be misused. This can be very painful especially as I have one DB at 2 TB, and it's always an emergency to find a server admin to give me 2 TB of "temp" space as we are running out seems everyday.
the real problem though is people think you can just grab one table from a backup and don't understand you backup the whole data file not objects in DB's. They assume it's 5 minutes when it can take days to bring back and of course they have a deadline that they will now not meet. Ah the glamours life of a DBA.
As for Red gate they own this site so I won't suck up to much (free shwag? just ask for my address) but there backup program is awesome. You can restore just a table out of I think the professional one, but the cost has my management running for the hills. We tried the 14 day eval and it worked wonderfully. I hope they will drop the price a little in the future. I will also add that it isn't all that faster especially if it's a huge table spread across multiple datafiles, but from a not having to call a server admin for space standpoint I find it to be very useful.
Enjoy...
September 30, 2008 at 11:12 am
I had to do this with an Access database not long ago. A person was given a copy of the database to do a special study and deleted all of the records that they didn't need for their whatever. Unfortunately they were given a copy of the front-end that connected to the live database, so records went bye-bye.
Fortunately we have pretty good backups and recovery, though a bit of work, wasn't too bad. According to my notes, the user wiped 12,000 records from one table, 20,000 from another.
Such fun! I love what I do for a living!
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
October 2, 2008 at 7:08 pm
I liked this simple technique and can certainly be helpful in cases when the database being restored is relatively small (I live in a data warehousing world, so 16GB is really small to me). Thank you for the article!
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply