Get the data after DB getting Crashed

Question

Get the data after DB getting Crashed

sqlusers

SSCrazy

Points: 2463
More actions
August 17, 2010 at 5:26 am

#225957

Hi,
I am new for DBA. I have some doubt in backup and restore. I have full backup job which will take full backup of my database every week. I have differential backup job which will take differential backup of my database every day. I have transactional job which will take transaction backup of my database every 30 minutes.
But my database get crashed at 5.45 AM. If i restore all the full, differential, transactional backup i will get the datas till 5.30 AM only. How can i get the 0.15 minutes datas?
---

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Depending on how the database crashed. If SQL is still running and the transaction log is still available you can do a tail-log backup and back up right to the last transaction.

If the crash was such that SQL cannot start or that the server itself can't start or the transaction log is lost, then that last 15 min data is lost and cannot be recovered.

This is why the interval between log backups should be the maximum allowable data loss for the database.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sqlusers SSCrazy Points: 2463 More actions · Answer 2

Great! Thanks Gail Shaw. You are saying tail-log backup which means that transaction log backup or else some other one?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Google is your friend

http://msdn.microsoft.com/en-us/library/ms179314.aspx

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ramji29 Hall of Fame Points: 3832 More actions · Answer 4

GilaMonster (8/17/2010)
Depending on how the database crashed. If SQL is still running and the transaction log is still available you can do a tail-log backup and back up right to the last transaction.
If the crash was such that SQL cannot start or that the server itself can't start or the transaction log is lost, then that last 15 min data is lost and cannot be recovered.
This is why the interval between log backups should be the maximum allowable data loss for the database.

See sometimes it happens that when you file are on different physical drives and one of the drive crashed which is holding data but you log drive is working fine then you can take tail-log backup..... sql services shud be running for this... if your complete server is crashed then..... as Gail said.... you'll loose 15 mins of the data.

sqlusers SSCrazy Points: 2463 More actions · Answer 5

Thanks Ramji29. In this case, if some thing is in a transaction that transaction data will be rolled back rt? I don't know exactly. Please explain.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

sqlusers (8/17/2010)
In this case, if some thing is in a transaction that transaction data will be rolled back rt? I don't know exactly.

In what case? If the DB crashes and you manage to take a tail-log backup? If the DB crashes and you can't take a tail-log backup (restoring to last normal log backup)?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sqlusers SSCrazy Points: 2463 More actions · Answer 7

sqlusers

SSCrazy

Points: 2463

August 17, 2010 at 7:33 am

#1207874

opps.. if i can able to take tail backup.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

As with restoring any backup, the DB will be transactionally consistent once restored. If a transaction did not commit by the time the backup finished, that transaction will not reflect in the restored database

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sqlusers SSCrazy Points: 2463 More actions · Answer 9

sqlusers

SSCrazy

Points: 2463

August 17, 2010 at 7:43 am

#1207878

Thanks Gail sharing all good info. 🙂

Ramji29 Hall of Fame Points: 3832 More actions · Answer 10

sqlusers (8/17/2010)
Thanks Ramji29. In this case, if some thing is in a transaction that transaction data will be rolled back rt? I don't know exactly. Please explain.

its more like whatever commited transactions are there in backup they will be roll forward and incomplete tran like what Gail said will be rollbacked and will reflect in the next log backup...

Hope this clears your doubt.

Rohit

sqlusers SSCrazy Points: 2463 More actions · Answer 11

its more like whatever commited transactions are there in backup they will be roll forward and incomplete tran like what Gail said will be rollbacked and will reflect in the next log backup...
Hope this clears your doubt.
Rohit

Yes.. Thanks Rohit.