| Author |
Topic |
|
lisa_baria03
Yak Posting Veteran
60 Posts |
 Posted - 2007-11-16 : 21:01:35
|
| I am trying to restore a database to a point in time preferably a minute early before a crash. Initially i have a table called orders where i inserted new rows but it has later been dropped. If i later want to recovered that drop table along with the inserted rows before the dropwould this be the correct procedure using the stopat?RESTORE DATABASE DB1 FROM 'c:\db1.bck' WITH RECOVERY, STOPAT = 'mmm dd, yyyy hh:mm AP'the following is what i have worked out so far. Would it make sense?1. I backed up the database.backup database db1 to disk = 'c:\db1.bck' with init2. I Added 2 rows to two tables Orders and OrderDetails table.insert into Orders(salesproductID,OrderQty,status) values(' 1','5','6')insert into orderdetails(salesproductID,OrderQty,ProductID) values('9','6','8')3. I backed up the logbackup log db1 to disk = 'c:\db1.bck' with init4. here I recorded time using the GETDATE() function.select GetDate()5. I added Add 3 more rows to the two tables like thisinsert into Orders(salesproductID,OrderQty,status) values(' 2','3','5')insert into Orders(salesproductID,OrderQty,status) values(' 4','2','9')insert into Orders(salesproductID,OrderQty,status) values(' 3','2','1')insert into orderdetails(salesproductID,OrderQty,ProductID) values('8','4','2')insert into orderdetails(salesproductID,OrderQty,ProductID) values('6','3','2')insert into orderdetails(salesproductID,OrderQty,ProductID) values('3','7','9')6. I Performed a differential database backup.backup database db1 to disk = 'c:\db1.bck' with differential, init7. I then recorded the second time: Time 2.select getdate()8. I Dropped the OrderDetails table.drop table orderdetails9. I then record the third time: Time 3.select getdate()10. I then Added 3 rows to the Orders table.insert into Orders(salesproductID,OrderQty,status) values('7','21','10')insert into Orders(salesproductID,OrderQty,status) values('14','12','19')insert into Orders(salesproductID,OrderQty,status) values(' 13','12','11')11. I Backuped the log and record the fourth Time 4.backup log db1 to disk = 'c:\db1.bck' with initselect getdate()Now when i tried to add rows to the OrderDetails table it does not exist but I like to get back the dropped table OrderDetails and not lose the three rows added to the table Orders. the only thing i would know is that the table orderdetails existed at time 2 and not at time 3 |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-11-17 : 00:33:54
|
| You can't get it by simple restoring. You can restore from full, diff and log backup to point of before dropping OrderDetails to another name. Then copy that table to db1. |
 |
|
|
lisa_baria03
Yak Posting Veteran
60 Posts |
Posted - 2007-11-17 : 10:05:22
|
| I just wanted to ask you the following questions that I've worked out. Would they be correct? |
|
|
|
Zoroaster
Aged Yak Warrior
702 Posts |
Posted - 2007-11-17 : 12:47:44
|
As rmiao stated, in order to get the orderdetails table back without losing the updates to orders you would need to restore your backups to another database up to the point before the orderdetails table was dropped, in this case step 6 that you outlined was the last backup you did before dropping the table. Then you would need to copy the orderdetails from that database to your original database.
Future guru in the making. |
|
|
|
lisa_baria03
Yak Posting Veteran
60 Posts |
Posted - 2007-11-17 : 13:54:35
|
| how would i perform this in sql?copy the orderdetails from that database to your original database. thanks |
|
|
|
Zoroaster
Aged Yak Warrior
702 Posts |
Posted - 2007-11-17 : 14:47:41
|
You can use the import/export wizard or you can right click and generate script to produce the table structure and then run that on the original. Then copy the data by linking the second database and then using Select * Into <DestinationTable> From <SourceTable>, the source table would need to be referenced using the fully qualified name.
Future guru in the making. |
|
|
|
lisa_baria03
Yak Posting Veteran
60 Posts |
Posted - 2007-11-17 : 15:15:22
|
thanks zoraster also for the steps leading to the copying tables partdo you think the queries i have from steps 1 - 10 is correct? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-11-17 : 21:03:26
|
| Don't need them at all for copying table. |
|
|
|
lisa_baria03
Yak Posting Veteran
60 Posts |
Posted - 2007-11-17 : 22:29:39
|
| but are the queries correct. thanks for your help. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-11-17 : 22:32:20
|
| If you didn't get error. |
|
|
|
lisa_baria03
Yak Posting Veteran
60 Posts |
Posted - 2007-11-17 : 22:36:40
|
| thanks rmiao.just a question about select getdate() would that actually record the time? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-11-18 : 00:12:01
|
| Yes, gives you current system date and time. |
|
|
|
lisa_baria03
Yak Posting Veteran
60 Posts |
Posted - 2007-11-19 : 19:32:49
|
| the way the database is backup in the 1st post. Is it corrrect?can you show me how to restore the backups to another database up to the point before the table was dropped, and then copying the table from that database to your original database using sql?thanks |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-11-19 : 23:25:05
|
| Find details in books online, or search related posts here. |
|
|
|
lisa_baria03
Yak Posting Veteran
60 Posts |
Posted - 2007-11-20 : 12:04:25
|
| full, diff and log backup to point of before dropping table to another name. Then copy that table to db1.for full, diff and log backups is using noinit or init the correct way?Can someone explain this more clearly. dropping table to another name. Then copy that table to db1.drop tbl orderand how does the copy table to db1 work? |
|
|
|
|
backups