Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
acw
Starting Member
3 Posts |
 Posted - 2005-01-05 : 14:37:31
|
| Here is my situation...I made a copy of what we'll call DB1 to the same SQL Server to do some testing. We'll call the copy DB2.After the copy, I deleted all the records in a products table of DB2 and continued testing - adding/deleting records.At some point I decide to delete all the records from the products table again, but mistakenly delete them from the original DB1 database. No action has been taken on DB1 since the deletion.No current backup exist of either database. The recovery model for DB1 is set to full and DB2 is set to simple.Is there a way I can recover the products info from DB1 to the state just before the deletion?Or can I recover the products info in DB2 from the point when it was first created?Thank you in advance.--- |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-01-05 : 14:46:05
|
| You can't recover from DB2 since you don't have a full backup of it that was used on creation and also since its recovery model is set to SIMPLE. You can only recover from DB1 if you have a full backup to start with and all of the tlog backups after it including the current one.You can recover this data via the tlog if you purchase a tool from Lumigent called Log Explorer.Is this a real world situation or a homework/test question?Tara |
 |
|
|
acw
Starting Member
3 Posts |
Posted - 2005-01-05 : 15:04:31
|
I wish it was homework - it is real world . The data missing represents almost 2 days of work and we are already behind on this project.If I understand you correctly, you are describing two possible solutions...1. Recover last database backup and recover using transaction logs including the current log. If I recover the current log, wouldn't that put me at the point I'm in, with deleted products? Or can I use the current log and specify not to include the last command?2. With Log Explorer I don't have to recover the database and I can have this tool recover using the current log to the point just before the deletion.Please elaborate.Thanks,FelixPS - The PM on this project is also name Tara.--- |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-01-05 : 15:08:55
|
| 1. Yes. When you restore the current tlog (you'll first have to back it up), you'll specify the time to stop restoring at. Please see RESTORE LOG for more information. This is known as point in time recovery. In order for this to work, you must have a full backup and all tlog backups since the full backup. If not a single tlog backup has been run since the full backup, then you just need to backup the current one and use that.2. The tool allows you to read the tlog. So you can get the information from it. I believe the tool is around 500 bucks. I've never used it though, but I know a few people here have and it saved them grief.Tara |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-01-05 : 23:43:13
|
| Probably no help to you now, but maybe "food for thought!":We run maintenance scripts on our servers which automatically back up newly created databases unless the database name is explicitly added to a list of "Do NOT backup this one".Kristen |
|
|
|
acw
Starting Member
3 Posts |
Posted - 2005-01-06 : 09:36:08
|
| Thank you both for your help and advice.It turns out that I did not have a usable backup of DB1 so after trying different ideas I finally decided it made more economic sense to have someone re-enter the information than continue to try to recover the missing data from just the log.I still feel that it is possible, but it is beyond me.Thanks again!--- |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-01-06 : 12:05:37
|
| Yes it is possible, but only with a third party tool like Lumigent's Log Explorer.Tara |
|
|
|
|
|
|
|
|
Deleted all records from DB table-can we rollback?