February 17, 2010 at 4:40 am
I have full backup of a database and the database got crashed.mdf file is also corrupted and I am only left with ldf file. How can I recover the database in order to have minimum data loss?
Thanks
Vivek
February 17, 2010 at 6:31 am
If the database was in full recovery and you have all the log backups since that full backup and SQL is still running and still has the DB attached (even though it won't be accessible) then you can do a tail-log backup to get the last of the transactions.
Was the DB in full recovery model?
Do you have all log backups since the full that you're using?
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
February 17, 2010 at 8:06 am
If this is in production and you have good backups do the restore from Full and T logs back ups.
EnjoY!
February 17, 2010 at 8:09 am
GT-897544 (2/17/2010)
If this is in production and you have good backups do the restore from Full and T logs back ups.EnjoY!
Dont forget to take a tail log backup before you do this.
February 17, 2010 at 8:13 am
I forgot to mention, that is first thing you should try to do when if there is something wrong with database, thanks Steve.
EnjoY!
February 17, 2010 at 8:17 am
Seems we are all in agreement with Gail then, correct?
February 17, 2010 at 8:22 am
My scenario is different from what have been mentioned in your posts. No other backups are available except for one full backup. mdf file is corrupted and ldf file is available only. How can we esnure minimum data loss in this case?
Thanks
Vivek
February 17, 2010 at 8:26 am
neeraj.kathuria (2/17/2010)
My scenario is different from what have been mentioned in your posts. No other backups are available except for one full backup. mdf file is corrupted and ldf file is available only. How can we esnure minimum data loss in this case?Thanks
Vivek
First, what is you database recovery model?
Second, when is the one full backup you have taken in relation to your mdf file becoming corrupt?
Third, should there have been t-log or differential backups taken between the full backup and the failure?
February 17, 2010 at 8:28 am
Did you try to run tail log back up?
February 17, 2010 at 8:34 am
neeraj.kathuria (2/17/2010)
My scenario is different from what have been mentioned in your posts. No other backups are available except for one full backup.
I'm going to assume (I know that's dangerous) that having just the full and no log backups means either this DB is in simple Recovery model or, if it's in Full there's regular log truncation jobs running. I'm assuming that because full recovery and no log backups means that the log fills the drive quickly.
If the DB was in simple recovery or you're running log truncations (BACKUP LOG ... WITH TRUNCATE ONLY) often, then you have no chance of getting any data back other than the full backup. Point in time recovery (which is what you're asking for) requires full recovery model and regular log backups.
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
February 17, 2010 at 8:36 am
My recovery model is full. This is a UAT database. I know if there were t-log backups, the database could have been recovered to its maximum. I am repeatedly saying there is no other backup then a full backup which was done on Sunday. My database got crashed on Wednesday. Only ldf file is available for use of the corrupted database. mdf file is also not available for use. Can we recover the database to its maximum? If yes how. Ofcourse we can restore the full backup available. But then can we make use of the available ldf file to recover the database to its maximum?
Thanks
Vivek
February 17, 2010 at 8:39 am
Vivek29 (2/17/2010)
Can we recover the database to its maximum? If yes how. Ofcourse we can restore the full backup available. But then can we make use of the available ldf file to recover the database to its maximum?
Maybe. Are you running log truncation jobs? (BACKUP LOG ... WITH TRUNCATE_ONLY)? How do you keep the log from filling the drive (seeing as it's full recovery, no log backups)?
Is SQL still running, is the DB still visible through SQL (I know, it won't be accessible)?
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
February 17, 2010 at 9:00 am
See this link for more information..
February 18, 2010 at 2:08 am
Its getting messy. Let simplify the point. Can we recover a database to its maximum with a full backup and valid ldf file available? Truncation logs were not performed on the log file of the crashed database.
February 18, 2010 at 2:18 am
If the recovery model is full and the recovery model has not been changed since that full backup, if no one has ever truncated the transaction log and if the DB is still visible in SQL Server, yes you can. (refer to the very first reply I made in this thread)
Look up Tail-log backups in SQL's Books Online.
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
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply