LearnSQLWithBru -- SQL Server Forums
September 06, 2010, 04:02:34 am *
Welcome, Guest. Please login or register.

Login with username, password and session length
News:
 
   Home   Help Search Login Register  
Pages: [1]
  Print  
Author Topic: understand Full,Differential,TransactionLog Backups  (Read 20 times)
0 Members and 1 Guest are viewing this topic.
saqlainkhan
Sr. Member
****
Offline Offline

Posts: 186


« on: July 20, 2010, 09:04:34 am »

I am in a sticky position. I have a backup plan as following:
1)FULL BACKUP - WEEKLY -->FRIDAY
2)DIFFERENTIAL - EVERY 6 HRS
3)TRANSACTIONLOG - EVERY 15 MIN

Assuming my db crashes on Wednesday, what will be the procedure to restore my db just before it crahsed. As i see it my minimum loss would be a minute and max of 14.59 minutes.
Thanks.
Logged

Fear: The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Bru Medishetty
Administrator
Hero Member
*********
Offline Offline

Posts: 267


« Reply #1 on: July 20, 2010, 10:54:43 am »

First, I would not recommend a Differential backup every 6 hours, I would recommend it to be daily (24 hours).

So with that slight change, the recovery process would be something like this.

Since you have not mentioned at what time the crash occured (assuming if the crash occurs at 3:25 PM) or there was not a crash but a user made mistake (a Delete / Drop / Truncate). around 3:25.

Then the procedure would be something like this.

Restore the latest Full backup WITH NORECOVERY Option.

Restore the Most recent Differential backup (in this case it would be Tuesday night's 11 PM (again assuming you have it scheduled at 11 PM)  Diff backup) WITH NORECOVERY Option.

Restore all the Transaction Log backups which were taken right after the Recent most differential backup (from Tuesday night 11:15 ) till 3:15. All these backups need to be used WITH NORECOVERy Option and then finally (the last Transaction Log Backup) needs to be restored WITH RECOVERY Option and you need to mention STOP AT 15:25.

I hope this gives a good picture..
Logged

Regards,

Bru Medishetty

www.LearnSQLWithBru.com
saqlainkhan
Sr. Member
****
Offline Offline

Posts: 186


« Reply #2 on: July 20, 2010, 10:58:29 am »

Thanks... makes perfect sense with additional info regarding DifferentialBackup.
Thanks! Grin
Logged

Fear: The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Pages: [1]
  Print  
 
Jump to:  

Powered by MySQL Powered by PHP Powered by SMF 1.1.11 | SMF © 2006-2009, Simple Machines LLC Valid XHTML 1.0! Valid CSS!