July 16, 2010 at 2:00 am
I am working as Sys Admin in a Private Bank. we have sql-2005 installed at all our sites
my assistant has screwed up one of the tables at a site by deleting all the rows in that table
-He deleted all the rows from a table named 'indexreg' from database name 'aknagar'
-the problem is they have never taken any sort of back up of the database since beginning
-when I examined the .ldf file it is bigger than the the .mdf file as they have not taken back up
-now I am trying to recover the DB using 'point-in-time' recovery but failing
-My assistant will have to lose his job if I dont recover the DB.
-can any expert please guide me on the exact procedure to be followed in my case to recover the db to a point-in-time or to LSN number.
-help is highly appreciated
the procedure I am trying is as under :-
1) Noting down the lsn number from dbcc log(aknagar)
2)deleting the .mdf file of the db and backingup only transaction log
3)droping the aknagar db and attaching only single file .mdf
4)backing up the full db
5) Now I have a full back up of the database and a transaction log back up
(I dont know whether this is correct)
6)Now I am dropping the database and restoring the full back up then restoring transaction log upto a lsn number using ''RESTORE LOG"
This is not working. Gods please help
July 16, 2010 at 2:23 am
What recovery model is the database in?
How often are you doing log backups?
the procedure I am trying is as under :-
1) Noting down the lsn number from dbcc log(aknagar)
2)deleting the .mdf file of the db and backingup only transaction log
3)droping the aknagar db and attaching only single file .mdf
4)backing up the full db
5) Now I have a full back up of the database and a transaction log back up
(I dont know whether this is correct)
6)Now I am dropping the database and restoring the full back up then restoring transaction log upto a lsn number using ''RESTORE LOG"
What the ???
Why drop the database? Why delete the mdf? Why try an attach without the log (can fail and leave you with NO database)?
All you need to do, assuming you have full and log backups is to take a log backup after the 'incident', restore the full backup as a new database with norecovery and then restore the log backups one by one, with norecovery until you get to the point you want to stop. Nothing more than that.
Of course, that assumes full recovery models and that you have log backups and that the log hasn't been truncated.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 16, 2010 at 2:27 am
anilambedkar (7/16/2010)
-the problem is they have never taken any sort of back up of the database since beginning
Just noticed this.
If there has never been a full backup of this database, then you are not going to be able to recover at all. You cannot undo changes by taking a backup after the changes happen.
If there has never been a backup, regardless of recovery model the DB is acting as though it's in simple, ie auto-truncating the log on checkpoint. As such, there's nothing to back up in the log.
You might (might) be able to get the changes back is you use a log reader quickly (before SQL overwrites the log records). The cheapest I know of is around $1000 (Apex SQLLog). Download the demo and give it a shot.
There is no free tool that does this, the demo will show you what it can recover, but you'll have to buy the full product to actually get anything back.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 16, 2010 at 2:47 am
If you are very lucky the last full backup may be hidden somewhere in the filesystem (if there ever was one).
Try running the the below query. Its a longshot but worth a try.
use msdb
go
select top 1 bs.database_name,bmf.physical_device_name,bs.backup_finish_date from backupmediafamily bmf
join backupset bs on bs.media_set_id=bmf.media_set_id
where bs.type='D' and bs.database_name='aknagar'
order by bs.backup_finish_date desc
July 16, 2010 at 3:11 am
then what is the use of point-in-time recovery
I can see all the lsn Numbers in the 'DBCC LOG(aknagar)'
where it is showing all Delete Actions performed.
I dont know how actually this point-in-time recovery works
July 16, 2010 at 3:12 am
If there genuinely has never been a backup since the time the database was created, the chances of recovering the data through the transaction log are fairly slim.
I would investigate things like whether the data has ever been exported anywhere else (e.g. to populate a test environment or another system). Also, what's the source of the data in this table? Does it come from another system where it could potentially be rebuilt from? Are there other tables within your schema that could be used to repopulate it? Are there hard/soft copies of the data in another format?
On the subject of firing the person responsible for the delete, I'd say the true incompetence lies in never having backed up a production database rather than making a single mistake (no matter how big the consequences of that mistake)
July 16, 2010 at 3:16 am
assuming that if there was a full backup of the db
what should I do to recover upto a LSN number
July 16, 2010 at 3:21 am
the data belongs to transactions of savings accounts
of a low level branch, (the low level branch is a non computerised office)
the records of that low level branch are being managed here
There is no way that we can reconstruct data from other tables, only way is to re-enter the data.
and it will take atleast a year to complete the dataentry.
July 16, 2010 at 3:40 am
Can Red Gate Log Explorer work..... ???
if I downgrade the database to SQL 2000 (compatability 80)
and try to recover from then ...........
Is it possible to to downgrade from SQL 2005 to SQL 2000 without actually committing the transaction log ??????????????
July 16, 2010 at 3:42 am
anilambedkar (7/16/2010)
I dont know how actually this point-in-time recovery works
And you're a production DBA?
Point-in-time recovery requires a full backup and all the transaction log backups taken since that full backup.
How about answering my questions.
What's the recovery model?
Have there ever been any backups of this DB? (query msdb like EvilPost showed you)
If the entries are in the log, consider buying that log recovery tool I mentioned. If you really and truly have no full backup, it's the only way you have even a chance of getting anything back.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 16, 2010 at 3:43 am
Well, it depends on your exact scenario. Assuming you have a full backup and the log file has never been backed up or truncated since, it's as Gail's has already said. You need to restore the full backup to another location without recovery and then back up the log of the production database and restore it to the newly created DB using a restore command with a STOPBEFOREMARK parameter to tell it the LSN to stop at.
You have to remember that a transaction log restore allows you to roll forward transactions up to a point in time, not back, so you must have a full backup and then a complete sequence of logs from the time that backup was created up to the point in time you want to restore to.
July 16, 2010 at 3:44 am
anilambedkar (7/16/2010)
Can Red Gate Log Explorer work..... ???if I downgrade the database to SQL 2000 (compatability 80)
No. It only works on SQL 2000 databases. A database attached to SQL 2005 is a SQL 2005 database, regardless of the compatibility level (which just affects how some T-SQL commands are interpreted)
There are no free log readers for SQL 2005+
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 16, 2010 at 3:47 am
HowardW (7/16/2010)
On the subject of firing the person responsible for the delete, I'd say the true incompetence lies in never having backed up a production database rather than making a single mistake (no matter how big the consequences of that mistake)
Agreed. Accidents happen. Ensuring that production databases are recoverable is one a the DBA's primary responsibilities.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 16, 2010 at 4:07 am
I am not a DBA ...
My company doesnt hire any DBAs, I am just an SA... that too for 25 + sites
I have four stupid assistants with me.., they are all freshers.... I cant blame my co cause it employed me
the recovery model of the DB is Simple
thanks for trying to help me out.
is there any way to downgrade and recover from LOG explorer.
there is no "back up of the DB" ever, I have checked that msdb query..... earlier itself ....
If the apexsql can do it then there should be some way we can do it.....
July 16, 2010 at 4:17 am
the recovery model of the DB is Simple
there is no "back up of the DB" ever
End of the game I'm afraid. Your log file doesn't store any information that could be used for recovery and you don't have a backup.
The only possible avenues would be to approach a data recovery specialist (or even Microsoft consultancy) to see if they can do anything at the page level (as deleting the data doesn't necessarily destroy the data pages, just deallocates them) but it's all a bit pie in the sky.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply