| Author |
Topic |
|
nanolin8
Starting Member
17 Posts |
 Posted - 2011-01-11 : 21:34:26
|
| our database server was down on wednesday night. When we restored it on another server, the people who did restore only restored db to Tuesday db backup and forgot to restore Wednesday data using Tlog backups. The the db started to serve users on Thursday. Later, user found wednesday data is missing. We still have tuesday db backup, all Tlog backup on Wednesday and new db nightly backup and hourly Tlog backups since Thursday. How to get wednesday data back to working db?This is absolutely top priority work to recover the database, please help!!! Thanks in advance! |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-01-11 : 22:22:44
|
| Restore it again (to other server) with Tuesday's backup. Then apply TLog restores. Then insert missing data into production.Not going to be easy and will be time consuming. |
 |
|
|
nanolin8
Starting Member
17 Posts |
Posted - 2011-01-11 : 22:55:20
|
| That is we are trying to do. But how to extract missing data(they should include records both inserted and updated on wednesday, right?) below is we are doing for testing: build three dbs: base, test, targetcompare target w/ base to find updates1apply updates1 to testcompare test w/target to find updates2insert new to targetapply updates2 to targetwill above bring target to accurate?and do you have experience how to do db comparison? any good tool?Thanks ! |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-01-12 : 00:51:26
|
| Note that if you have identity columns, you may need to handle those manually.Red Gate has a tool for comparing and synchronizing that is pretty good.Idera has one too but I've never seen it in person.Certainly there are others. |
|
|
|
nanolin8
Starting Member
17 Posts |
Posted - 2011-01-12 : 08:51:47
|
| what is identity column? pk?Can RedGate tool handle identity column? |
|
|
|
nanolin8
Starting Member
17 Posts |
Posted - 2011-01-12 : 10:49:34
|
| before we perform isert/updates against target db, I think we need drop constraints ahead and add constraints back following DML. How about after insert/ after update triggers of the tables? disable them before DML? Will it cause mess?How about Apex SQL Log tool- that can read tlog backup and generate redo statement. Anybody has suggestions? Thanks! |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-01-12 : 11:39:59
|
| An identity column is a column that auto-increments when new records are inserted.In short what you need to do is compare the data in all tables after the restore, then insert the missing data.If you have foreign keys, you're going to need to do it in a specific order, or disable the keys.You most likely want to disable all triggers.Don't think Apex SQL Log can generate redo statements...could be wrong, been a couple of years since I've used that one.If your database is large, it may be worth your while to bring in a consultant who is expert in this type of thing. Please don't take this as an insult but it worries me that (a) this happened in the first place and (b) you don't know what an identity column is. This indicates that there may be many other things you may not know that could be critical in fixing this. |
|
|
|
nanolin8
Starting Member
17 Posts |
Posted - 2011-01-12 : 13:51:23
|
| Russell,You are right on the concerns. Why you only mentioned inserting missing data. We don't need worry about updated records?we disabled constraints and triggers. There are numerous FK and after DML triggers. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-01-12 : 14:04:29
|
| Yep, you need to find and fix the updated records as well. And any deletes.Depends on what the triggers do, but most likely safest to disable (or drop then recreate) all of them.Triggers are typically enforcing some sort of data integrity, or they run updates or inserts after the initial action. Your manual updates will cover these.Now if they are performing actions on some remote server or something, then you'll have to figure that part out too. |
|
|
|
nanolin8
Starting Member
17 Posts |
Posted - 2011-01-12 : 16:00:37
|
| fortunately, we don't have remote server associating with it.Can you explain more why need deal with identity column manually? |
|
|
|
nanolin8
Starting Member
17 Posts |
Posted - 2011-01-12 : 16:03:15
|
| we are using redgate tool. there is option im mapping:include identity columns, is that good enough to take care identity columns? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-01-12 : 16:25:18
|
| Suppose you have a table that regularly gets inserted into, with an identity column.At the time of your backup, the seed was at say 10,000.Next day 100 records get inserted, so seed is at 10,100.But, after the restore, seed is back to 10,000.Now you start inserting again.So between the 2 copies of the database (the one incompletely restored, and the one completely restored, but now without the most recent live transactions) you will have DIFFERENT records with the same identity value. Worse yet, if this identity value is a Primary Key AND referenced by Foreign Keys, you have a real mess. Now you have multiple tables with key values that might map to the WRONG record if you aren't careful about putting in the missing data. |
|
|
|
nanolin8
Starting Member
17 Posts |
Posted - 2011-01-12 : 17:22:25
|
| this is true that there are a lot of columns such as id are identity columns in this db and they are also PK or FK.So it will definately cause the mess, right? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-01-12 : 18:16:10
|
| You'll need to be careful. After comparing them, if you find duplicates (different records, same id) then you're going to have to devise a strategy for inserting them with new identities, then make sure to insert the referencing records with the correct (new) identity value.If the system is online (new records being written) while you do this, reseed the identities using DBCC CHECKIDENT(reseed) with a value large enough so the "hole" you create can fit all of the records you're going to insert. This way new records can continue to be inserted while you're explicitly inserting the "missing" ones without conflict. |
|
|
|
nanolin8
Starting Member
17 Posts |
Posted - 2011-01-12 : 20:48:36
|
| If on wednesday night, people found they missed to restore wed Tlog backups after they restored with tuesday backup with recovery option but still not yet to serve users, is it possible to restore wed tlog backups on the top of the restored db? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-01-13 : 08:25:03
|
| Only if you didn't perform another full backup.T-Log backups are only valid with the most recent full backup that preceded them. |
|
|
|
nanolin8
Starting Member
17 Posts |
Posted - 2011-01-13 : 10:32:17
|
| want get it more clear.suppose the db is restored with tuesday backup using "with recovery" option. It is back to online, but no user transaction yet. no further backup is done. Just at this moment, can we start a new restore process, without restoring the tuesday db backup, just restore using Tlog backup. Is that possible? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-01-13 : 10:34:05
|
| No. But yes if you restore Tuesday's backup with "norecovery" |
|
|
|
nanolin8
Starting Member
17 Posts |
Posted - 2011-01-13 : 16:04:57
|
| one more question:Suppose we want to restore a database A to another server to create database B, we backed up A and move the backup file to new server and restore there; do we need backup the tail of log of A and restore the log backup to B? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-01-13 : 16:52:26
|
| Generally, yes you would. In the case of your incomplete restore, if another full backup was taken, then it's too late to get the tail of the log. |
|
|
|
nanolin8
Starting Member
17 Posts |
Posted - 2011-01-13 : 20:20:42
|
| I'm not talking about the incomplete recovery we ran into, just general case to create a database B on another server using the backup of another database A. If we know no user activities during and after A's backup is taken,that is user connections has been disconnected before the backup, do we need backup A's tail of log after backing up A and restore log backup on B? |
|
|
|
Next Page
|
get data back after incomplete recovery