About complete back up
-
In Bilal Ahmed's Backup Scenarios for successful SQL Server Restores and Recovery (http://www.sqlservercentral.com/columnists/bahmed/backupscenariosforsuccessfulsqlserverrestoresandre.asp)
author described four scenario about user-created databases.
In each scenario,database would be completely backed up everyday.
Is it resonable that database is only completely backed up at first operation of backup,then database is diffrentially backed up at other time.
because the bigger the database is,the more the time will be spent on database backup.
are there scenario that are suitable for backup strategy what i described?
-
If I am understanding your question, the answer is no. If you create a single back-up and then perform differential (or some other change method), each day for a month and then you need to perform a restore, you will need to go back to your first days back-up and then restore all of the changes that took place each day that month. Also if there was a failure in any of the files then you would be creating back-ups that would not work. Creating back-ups schedules is a balance of how long it takes to make the back-up with, how long it takes you to restore (which is your down time) and how much data you can lose (that is the time between back-ups).
-
Thank you!
However,In diffrential backup,the latest backup contain every change since the last full backup.ie,if there are twice diffretial backup since a full backup,the second contain everything in the first.
hence,when I restore database,I only need to restore a full backup and the latest diffrential backup.
compared with full backup everyday,diffrential backup everyday will spend shorter time on backup.
Do you agree with me?
-
Yes but, because the differential backup contains portions of the database changed since the last full backup, wouldn't your differential backup become larger and larger as it tracks each change since the full backup?
Differential backups are good to use if you want to reduce your recovery time.
Take this scenario. You do a full backup every night at 12am. You also do transaction log dumps every 30mins. If the database fails at 1pm you need to restore,
1) full backup from 12am
2) 26 x transaction log backups (and don't mixup the order
)27 files in total.
However, if you also have a differential backup every 4 hours, then all you need to restore is,
1) full backup from 12am
2) differential backup from 12pm
3) 2 x transaction log backups.
4 files in total.
From Books Online,
A recommended process for implementing differential database backups is:
- Create regular database backups.
- Create a differential database backup periodically between database backups, such as every four hours or more for highly active systems.
- If using Full or Bulk-Logged Recovery, create transaction log backups more frequently than differential database backups, such as every 30 minutes.
--------------------
Colt 45 - the original point and click interface
-
About differential backups. I just want to give example from my experience. We had database which had differential backup (daily) bigger than full backup.
-
Thanks!
why diffrential backup is bigger than full backup?
-
This would be the case if there was a very large number of changes. Since it records all of the changes.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply