Recover the database

Question

Hi,

Backup senario is as below

Monday-Full backup @ 5:PM(backup database mirroring to disk='f:\backup\full.bkp')

Transaction backup is happening once in a hour like 6PM,7PM,8PM....etc

Tuesday-taken differential backup @ 2:PM.

Transactional backup is happening onc ein a hour

Wednesday: Taken differential backup @ 2:PM

Server is crashed @ 3:25.

How can I recover the database till this time.

Please provide me a querry for the same.

Thanks in advance

---

Shashikala

Answer 1

1.Apply last full backup ie one taken on Monday

2.Apply last differential backup ie one taken on Wednesday 2PM

3.Apply subsequent Transaction log backup ie one taken at 3PM

And you'l be able to get database up to stage as on Wed 3PM. You'll lose out last 25 minutes transactions as next transaction log backup has not happened yet (ie only @ 4PM next log backup happens)

---

Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

Answer 2

You may refer the below link to understand various options for you:

http://www.mssqltips.com/sqlservertip/3049/different-ways-to-restore-a-sql-server-database/

Answer 3

so, 25 mins data will be lost, no way to backup this logs and restore again?

---

Shashikala

Answer 4

so, 25 mins data will be lost, no way to backup this logs and restore again?

---

Shashikala

It looks like you did not read the link that I posted. Please read it carefully.

Look at session : Example 3 - restore to point in time P13

The session explains usage of STOP AT clause. You need to use the same.

Answer 5

so, 25 mins data will be lost, no way to backup this logs and restore again?

---

Shashikala

It looks like you did not read the link that I posted. Please read it carefully.

Look at session : Example 3 - restore to point in time P13

The session explains usage of STOP AT clause. You need to use the same.

Sorry I dont understand how person would be able to restore db as on 3:25 PM. The transaction log backups are running hourly so she can only restore till 3PM. the next transaction log happens only at 4PM and thats not happened yet as server crashed at 3:25. So I think latest possible restore point is log backup at 3 PM

---

Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

Answer 6

As Visakh mentioned, you can restore your database upto 3 PM and you'll lose 25 min of data.

Only alternative is tail-log backup, please refer below article how tail log backup works.

http://www.sqlskills.com/blogs/paul/disaster-recovery-101-backing-up-the-tail-of-the-log/

---

Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
Praveen Dsa | MCITP - Database Administrator 2008 | My Blog | My Page

Answer 7

so, 25 mins data will be lost, no way to backup this logs and restore again?

---

Shashikala

It looks like you did not read the link that I posted. Please read it carefully.

Look at session : Example 3 - restore to point in time P13

The session explains usage of STOP AT clause. You need to use the same.

Sorry I dont understand how person would be able to restore db as on 3:25 PM. The transaction log backups are running hourly so she can only restore till 3PM. the next transaction log happens only at 4PM and thats not happened yet as server crashed at 3:25. So I think latest possible restore point is log backup at 3 PM

---

Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

Visakh, you can take tail backup and restore.

http://www.sqlskills.com/blogs/paul/disaster-recovery-101-backing-up-the-tail-of-the-

log/

EDIT: I just saw Praveen's answer after I posted.