November 24, 2004 at 9:17 am
I accidentally dropped a column on one important table. Then come to realize that we had no backups (they were not being done anymore and no old backup exists). I however have full transactional backups from the last 20 months. Is there anyway to use the current database and the transaction log to do a backward restore.
What I have in mind is to make a copy of the current DB on another server and use the transaction log to restore in a point back in time (monday morning-ish).
Is it possible to do something like this... and more importantly how?
November 24, 2004 at 9:23 am
No, You can't.
Do you have any full backup during last 20 months?
If you don't have, Try Lumigent Log Explorer for SQL Server http://www.lumigent.com/products/le_sql.html
November 24, 2004 at 9:35 am
The only backup I found was well over 20 months old and I already used it to recover as much data as possible
I had considered trying their software but
1 - It can only connect to pubs/northwind and their test database
2 - The drop column recovery works by (quoted from their site)
I do not have a backup of my database and have inadvertently dropped (truncated) a table? What can Log Explorer do for me?
Log Explorer’s "Salvage Dropped/Truncated" function will also work even if you do not have a backup of your database. The number of rows recovered will depend on how much database activity has occurred since the table was dropped (truncated). The sooner you act, the more data you will recover. In short, Log Explorer searches the "free page" list maintained by SQL Server to determine what data has been marked for reclamation but not yet reused. At the end of the process, Log Explorer generates a recovery script file and provides a report of the total number of rows that were deleted and the number that are actually recoverable.
but since I already updated to whole table, not much free pages will be found.. added to that we have 5 employes working on that table all the time I have little hope of this software finding any un_overwritten data.
3 - Costs way too much money for our needs. That column is important but I don't think it's worth the 1000+ can $.
However if I were to find a more recent backup of the database, how could I use the log to restore to a point in time (to allow me to reship the data on the production database)?.
November 24, 2004 at 9:44 am
1. Restore the full database with norecovery option.
2. Restore all log backups since the full backup with norecovery option until before you deleted the column.
3. Restore database with recovery option.
See BOL for details.
November 26, 2004 at 12:33 pm
In some cases, it's a good idea to restore to a new database, leaving the original alone. If you can find the missing data or objects in the new database, it can be moved into the production database.
RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply