December 21, 2010 at 1:13 pm
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
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
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
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
Hi,
Are you sure LAST DIFFRENCIAL backup only....
December 21, 2010 at 1:38 pm
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
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
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
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
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