I have accidentally ran a Transaction to export objects to the wrong database. In doing so I replaced a table that already existed with around 4000 records in it. Is there any way to do a roll back or recover from the .ldf file? I do not have a good back up of this database. I am in big trouble on Monday if I don't get this fixed. Please!!!!!!!! Someone have an answer. Thanks
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
I need serious help!!!!!!! I need to do a data restore without a back
- Thread starter wilnah
- Start date
tlbroadbent
MIS
You can perform point in time recovery if you have a backup. Restore the full backup and the transactions to a point in time just prior to the goof.
Another option is to use a product such as Lumigent's Log Explorer. It will allow you to back out transactions. You can download a trial copy which I believe is fully functional.
Note: When I first downloaded a trial version the recovery function was disabled so the current trial version may have some limitations. We have since purchased the product and found it to be a sound investment. Terry L. Broadbent - DBA
Computing Links:
faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.
Another option is to use a product such as Lumigent's Log Explorer. It will allow you to back out transactions. You can download a trial copy which I believe is fully functional.
Note: When I first downloaded a trial version the recovery function was disabled so the current trial version may have some limitations. We have since purchased the product and found it to be a sound investment. Terry L. Broadbent - DBA
Computing Links:
faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.
tlbroadbent
MIS
Sorry, I didn't catch the fact that you don't have a good backup. When was the last full backup? Do yo truncate the transaction log? The are some conditions from which you cannot recover. Unfortunaely, most of us have to learn the value of backups the hard way. It is a very expensive way to learn.
By the way, which version of SQL Server are you running? Terry L. Broadbent - DBA
Computing Links:
faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.
By the way, which version of SQL Server are you running? Terry L. Broadbent - DBA
Computing Links:
faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.
- Thread starter
- #4
- Thread starter
- #5
tlbroadbent
MIS
The software works with an active log or a transaction log backup. Terry L. Broadbent - DBA
Computing Links:
faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.
Computing Links:
faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.
- Thread starter
- #7
tlbroadbent
MIS
I cannot guarantee that Log Explorer will work for you. It can recover deleted records and even deleted tables under some circumstances. The active transaction log must be intact.
If the log has been truncated, you have no recovery position. If it has not been truncated you may be able to recover. I have no way of knowing for sure. Terry L. Broadbent - DBA
Computing Links:
faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.
If the log has been truncated, you have no recovery position. If it has not been truncated you may be able to recover. I have no way of knowing for sure. Terry L. Broadbent - DBA
Computing Links:
faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.
wilnah,
I feel for you. Most of us have made serious mistake at one time or another and it's never a fun experience.
I hope the program Terry suggested works, but if it doesn't you need a backup plan for how you are going to recreate those records. Hopefully they were entered from some kind of paper input and they can be typed back in. Or maybe the users have printed reports from this data and you can recreate from that. Or maybe this data came from another data source and you can reimport it.
You also need to consider the politics of the situation and take the steps which will keep the screaming and blaming down to a minimum.
Since this a weekend, I presume you are the only one who knows about this? If your boss doesn't know about this, I suggest you call him at home before the end of the weekend and tell him. Note that I'm not saying you have to tell him immediately (although if he is a reasonable sort, I probably would). You can take some time to try Terry's suggested program first. Of course, the sooner you bite the bullet and tell him the less time you have to spend dreading it. Make sure you also tell him the steps you are taking to fix the problem.
He's still gonna be mad, but not nearly as mad as if he found out about it from the manager of the people who use this database first thing Monday morning. At least if you tell him before Monday, he can plan how he is going to deal with the affected managers whose employees use or enter the data and if you go to him with a plan, he can go to them with a plan. And it forestalls some of the blaming if you have already taken it on. Most managers truly would rather have an employee who is working like mad to fix a problem than one who hides it and they get blindsided when someone else complains.
Anyway good luck
Judy
I feel for you. Most of us have made serious mistake at one time or another and it's never a fun experience.
I hope the program Terry suggested works, but if it doesn't you need a backup plan for how you are going to recreate those records. Hopefully they were entered from some kind of paper input and they can be typed back in. Or maybe the users have printed reports from this data and you can recreate from that. Or maybe this data came from another data source and you can reimport it.
You also need to consider the politics of the situation and take the steps which will keep the screaming and blaming down to a minimum.
Since this a weekend, I presume you are the only one who knows about this? If your boss doesn't know about this, I suggest you call him at home before the end of the weekend and tell him. Note that I'm not saying you have to tell him immediately (although if he is a reasonable sort, I probably would). You can take some time to try Terry's suggested program first. Of course, the sooner you bite the bullet and tell him the less time you have to spend dreading it. Make sure you also tell him the steps you are taking to fix the problem.
He's still gonna be mad, but not nearly as mad as if he found out about it from the manager of the people who use this database first thing Monday morning. At least if you tell him before Monday, he can plan how he is going to deal with the affected managers whose employees use or enter the data and if you go to him with a plan, he can go to them with a plan. And it forestalls some of the blaming if you have already taken it on. Most managers truly would rather have an employee who is working like mad to fix a problem than one who hides it and they get blindsided when someone else complains.
Anyway good luck
Judy
tlbroadbent
MIS
Wilnah,
I probably should have asked some of these questions earlier. Which version of SQL Server are you running? What kind of permissions do you have on the server? Are you a Sys Admin or can you login as a Sys Admin? Are you the owner of the database? Can you create datbases on the server?
Here are some more thoughts in the matter.
When you say you don't have a good backup, do you mean no backup file is available from any time that might contain at least some of the records that were deleted? It is possible to restore a backup to another database and copy the table from one database to another.
Is there a possibility that the MDF file was backed up during a server backup? If you could restore an MDF file with another name, you could attach it as another database and recover the table or part of the records. Terry L. Broadbent - DBA
Computing Links:
faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.
I probably should have asked some of these questions earlier. Which version of SQL Server are you running? What kind of permissions do you have on the server? Are you a Sys Admin or can you login as a Sys Admin? Are you the owner of the database? Can you create datbases on the server?
Here are some more thoughts in the matter.
When you say you don't have a good backup, do you mean no backup file is available from any time that might contain at least some of the records that were deleted? It is possible to restore a backup to another database and copy the table from one database to another.
Is there a possibility that the MDF file was backed up during a server backup? If you could restore an MDF file with another name, you could attach it as another database and recover the table or part of the records. Terry L. Broadbent - DBA
Computing Links:
faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.
jlgdeveloper
Programmer
Wilnah
Before doing anything else, make a backup of the current situation in case your attempts at recovery screw things up further. Make copies of the logs as well. Jonathan Galpin
Before doing anything else, make a backup of the current situation in case your attempts at recovery screw things up further. Make copies of the logs as well. Jonathan Galpin
- Thread starter
- #12
The backup copy shows the table as blank. It overwrote the existing media. Since there was no data in the table it overwrote the backup with data with the table that has no data. I hope that makes since. I am running sql server 7.0. Yes I am the sys admin on the server. Any ideas?
re:
> I have accidentally ran a Transaction to export objects to the wrong database. In doing so I replaced
> a table that already existed with around 4000 records in it.
I have a question about how this transaction that exported objects to the database was done. I haven't used SQL 7 extensively, but if you've used DTS and it created Drop/Create scripts to create the new table I have a hard time understanding that a log analyzer is going to be able to recreate your data...
Good luck!
Jeff
> I have accidentally ran a Transaction to export objects to the wrong database. In doing so I replaced
> a table that already existed with around 4000 records in it.
I have a question about how this transaction that exported objects to the database was done. I haven't used SQL 7 extensively, but if you've used DTS and it created Drop/Create scripts to create the new table I have a hard time understanding that a log analyzer is going to be able to recreate your data...
Good luck!
Jeff
tlbroadbent
MIS
If you don't have a back up or you overwrote the backup, you obviously can't recover from a backup. Does your site perform tape backups? Are there older backups available on tape that might hold part of the data?
Is the active transaction log intact? Is Truncate Log on Checkpoint set on? If it is set on, you'll not be able to recover using Log Explorer under any circumstance. If you've performed a full backup since the disaster, the transaction log may have been truncated.
Sometimes you just have to face the facts. We can all mess up and do something destructive. If the department has a good disaster recovery plan, backups would exist and at least partial recovery would be possible. At this point it appears your chances of recovery are slim.
Jeff,
Log Explorer can recover a deleted table under certain circumstances. As I explained, I don't know if it could recover from this particualr disaster. Terry L. Broadbent - DBA
Computing Links:
faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.
Is the active transaction log intact? Is Truncate Log on Checkpoint set on? If it is set on, you'll not be able to recover using Log Explorer under any circumstance. If you've performed a full backup since the disaster, the transaction log may have been truncated.
Sometimes you just have to face the facts. We can all mess up and do something destructive. If the department has a good disaster recovery plan, backups would exist and at least partial recovery would be possible. At this point it appears your chances of recovery are slim.
Jeff,
Log Explorer can recover a deleted table under certain circumstances. As I explained, I don't know if it could recover from this particualr disaster. Terry L. Broadbent - DBA
Computing Links:
faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.
- Status
Similar threads
- Locked
- Question
- Locked
- Question