| Author |
Topic |
|
Big_tim
Starting Member
22 Posts |
 Posted - 2009-11-16 : 11:11:32
|
| Afternoon all.I don't really know if this is something that anyone can help with or not as it's going to get complicated!I now look after a database that contains a table of contacts with usual details (name address etc) and each of those contacts is associated with a lot of other tables that has other information relating to them, including unique keys etc which are vital to have the correct associations.The problem is that a very important contact has been deleted from the database after a user was improperly given access rights to the database (before I started!) but it wasn't noticed for a while. Now, when it was noticed we contacted the vendors who provide the software and they say 'All you can do is do a restore from your backup'. Of course the problem here is that we have been using the DB for months, so can't restore over it.Does any one have any advice/tip/soloutions on how to pull the contact and ALL of the associated records out of a restored back up to a Dev box and insert that data back into live? As far as I can see this is going to be the bain of my life for months to come!Help!Tim |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2009-11-16 : 11:16:59
|
Just restore the backup to a different database name on the same server, and get the data that you need, then drop the recovered database-- Check the Back upRESTORE FILELISTONLY FROM DISK = '\\<file path>\<file name>'GO-- If the DB Exists, Kick everyone outALTER DATABASE <db_name> SET SINGLE_USER WITH ROLLBACK IMMEDIATEGO-- RESTORE The database...this will overlay an existing one, OR Create a new one if not in existence-- Get the Logical file names from filelistonlyRESTORE DATABASE <db_name> FROM DISK = '\\<file path>\<file name>' WITH MOVE '<logical data file name>_data' TO '\\<file path>\<file name>.MDF' , MOVE '<logical log file name>_log' TO '\\<file path>\<file name>.LDF' , REPLACEGOALTER DATABASE <db_name> SET READ_WRITEALTER DATABASE <db_name> SET MULTI_USERGO Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
 |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-11-16 : 11:19:30
|
| Sounds like you already know what needs to be done. What's the question?Restore the backup and insert the missing data. May want to restore to same server to make the inserts easier without having to create linked server or SSIS pkg.By the way, proper Foreign Keys would have prevented this. And cascading keys are such a bad idea that I honestly can't think of a reason for the feature to exist. I don't allow them in my databases.i see Brett beat me to it lol |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-11-16 : 11:23:08
|
I would do:Have a restore with that "old" data (i.e. you already have done on your dev box).Have a restore with that "actual" data (with missing contact data) on your dev box.Script out the needed "insert actual_db..actual_table select * from old_db..old_table where ..."If this scripting is done with success you can run it in production (but have a backup before!).I think more we cannot tell because we don't know anything about your data.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-11-16 : 11:24:16
|
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
Big_tim
Starting Member
22 Posts |
Posted - 2009-11-16 : 11:29:40
|
| The problem is that there is a large number of associated records that belong to the contact, so although I can easily get a back up it's the 'and get the data that you need' part that is causing the problem. I don't know how to get ALL of that data and make sure it is accurate. Logically thinking about it I guess what I would need to do is find the backup from right before it was deleted and also right after it was deleted (that will be a big task in itself) and then compare the 2 databases, review the differences, remove any changes NOT related to the deletion and insert that. Unfortuantely I wouldn't know where to start with comparing 2 databases like that, and it still sounds like it is work that is liable to mistakes. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Big_tim
Starting Member
22 Posts |
Posted - 2009-11-16 : 12:00:11
|
| When I say 'correct' I mean the same as it was before it was deleted. If it all worked before I want it to all work afterwards!! |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-11-16 : 12:33:04
|
| No. What you need to do 1st is identify the tables that might be missing records that you're interested in.Once you have that list in hand, restore the backup from right before the delete occurred to a different database name on the same server.Then insert the missing records.How long ago was the delete? |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
Recover small amounts of data from a full restore