Post reply

Restore discussion

  • December 21, 2010 at 1:13 pm

    #242660

    Hi all,

    I have Doubt....

    I am taking Full backup every st of month, then taking a differential backup every day & transaction backup going on every 1 hour...In case my server is down on 23rd of day at 3:18 PM....

    Now how do i restore the data......

    Okkkk...

    1. 1st i will restore full backup with no_recovery,

    2. 2nd i will restore last diffrencial backup (mean 22nd day backup) with no_recovery

    3. I will reinstall all transaction backups with no_recovery

    4. tail_log backup reinstall with recovery....

    This process is right?????????????

    Please update me..........

    Thanks & regards

    Satish Saidapur

  • December 21, 2010 at 1:27 pm

    #1265257

    restore with full backup norecovery, restore differential from the 22nd or 23rd depending on when the differential occurs with no recovery, restore all the log backups following the differential backup up to 13:00 with recovery. You will lose 18 minutes of data in that scenario - that is assuming that when you say "server goes down" that it is not accessible.

    There is some really good examples in Books Online for this which would be worthwhile to read. Also, it would be really good to test this sometime on a test server. A backup is only good if you can use it for a restore. 🙂

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • December 21, 2010 at 1:30 pm

    #1265258

    saidapur_satish (12/21/2010)

    Hi all,

    I have Doubt....

    I am taking Full backup every st of month, then taking a differential backup every day & transaction backup going on every 1 hour...In case my server is down on 23rd of day at 3:18 PM....

    Now how do i restore the data......

    Okkkk...

    1. 1st i will restore full backup with no_recovery,

    2. 2nd i will restore last diffrencial backup (mean 22nd day backup) with no_recovery

    3. I will reinstall all transaction backups with no_recovery

    4. tail_log backup reinstall with recovery....

    You updated after I posted. Option 4 would only be possible if the database was online and you can execute a backup of the log prior to starting the restore. Otherwise your outline looks good.

    Please note again, that you should test restoring to become comfortable.

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • December 21, 2010 at 1:30 pm

    #1265260

    Hi,

    Fullbackup(on 1st day) + Last differencial backup + transaction Log of 23rd till 3:00 PM

    Thanks

    Parthi

    Thanks
    Parthi

  • December 21, 2010 at 1:33 pm

    #1265262

    Hi,

    Are you sure LAST DIFFRENCIAL backup only....

  • December 21, 2010 at 1:38 pm

    #1265265

    saidapur_satish (12/21/2010)

    Hi,

    Are you sure LAST DIFFRENCIAL backup only....

    Yes, differential backups are cumulative in that they hold all the changes since the last full. So, you do not have to restore all the differential backups.

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • December 21, 2010 at 1:44 pm

    #1265267

    David Benoit (12/21/2010)

    Yes, differential backups are cumulative in that they hold all the changes since the last full. So, you do not have to restore all the differential backups.

    Yes David is correct.

    A differential backup is based on the most recent, previous full backup. This is known as the base of the differential. A differential backup includes only the data that has changed since previous full backup.

    Thanks

    Parthi

    Thanks
    Parthi

  • December 21, 2010 at 1:46 pm

    #1265269

    Yes. A differential backup records all changes since the last FULL backup. That is why you only need the latest (or most current) differential backup file.

  • December 21, 2010 at 1:52 pm

    #1265270

    Hi All,

    After full backup all pages are emty after full backup we insert any thing it will store page no. 6 (SAM page).

    then 2nd day will take again diffrencial backup then it will copy the 1st day changes or not......

    Is this right?????

    Please explain me any one about pages....

  • December 21, 2010 at 2:08 pm

    #1265275

    saidapur_satish (12/21/2010)

    Hi All,

    After full backup all pages are emty after full backup we insert any thing it will store page no. 6 (SAM page).

    then 2nd day will take again diffrencial backup then it will copy the 1st day changes or not......

    Is this right?????

    Please explain me any one about pages....

    Full Backup on Day 0 --->Sunday

    Diff Backup on Day 1 --->Monday

    Diff Backup on Day 2 --->Tuesday

    Diff Backup on Day 3 --->Wednesday

    Diff Backup on Day 4 --->Thursday

    Diff Backup on Day 5 --->Friday

    Tran Backup on Day 6 --->Saturday(Till Fail)

    On Saturday If any fail comes

    Day 0+Day 5+Day 6(Till Last Tran Bakup in your case till 3 pm)

    Here what happens is Diff of Day1 to Day5 will have all the datas from Day0 of Full backup i.e Changes made after fullbackup day 1 to day 5 will have all the records from day 0.Every day Diff backup backups only difference of Full backup and not the diff backup.

    Hope you would have get some idea on bakup restore.

    Thanks

    Parthi

    Thanks
    Parthi

Viewing 10 posts - 1 through 9 (of 9 total)

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