July 30, 2010 at 9:11 am
Hi sir,
i took full Database backup on Monday morning,Next day on tuesday i took differential backup in the morning but on next day (wednsesday) afternoon my database got crashed,Now i want to recover database with all transactions done till wednesday afternoon.
How do i recover my database, Please send me proper solution
thanks in advance...
July 30, 2010 at 9:59 am
What recovery model is the database in? Do you have log backups?
Just to be clear, latest diff backup on tuesday morning?
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 30, 2010 at 10:07 am
Interview question ?
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
July 30, 2010 at 11:15 pm
Full recovery model.
no i dont have log backups
July 31, 2010 at 12:33 am
You have already mentioned that Database is in Full recovery model; however, you don't have a log backup. You need to understand that only transaction log backup provides point-in-time recovery, while the database is operating in Full Recovery model.
The first thing you want to do, is initiate a tail-log backup.
Then restore the Full backup from Monday, followed by the Differential backup from Tuesday. You must ensure that the should be in restored with NORECOVERY command, which will allow us to restore the tail-log backup.
Then restore the Tail-log backup with RECOVERY command which will allow the database to accept connections.
Some points to remember:
1. If you are unable to take the tail-log backup, you can restore the data till Tuesday morning.
2. Point in time recovery can be performed with the help of transaction log, while the database is operating in Full Recovery model
3. If you are not going to initiate log backup, better switch over to Simple Recovery Model.
Hope, this may answer your question.
Thanks & Regards,
Sudeepta.
http://twitter.com/skganguly
July 31, 2010 at 3:18 am
dhaval_dsa (7/30/2010)
Full recovery model.no i dont have log backups
Are there explicit log truncations happening (backup log ... with truncate only)? Check the backup jobs, it's likely where this would be if it's there. Or if there's an explicit log truncation job that runs.
Likely there are, because otherwise the log would have filled the disk completely long ago.
You didn't go into detail on how the database crashed. Is SQL Server still running? Is the database in question still visible as an attached database (listed in sys.databases). Both of these, and an unbroken log chain are necessary to have a chance of recovering to the point of failure since you have no 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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply