Is there a problem creating only differential backups every hour?
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}
I have three databases which are not big. Every differential backup is roughly 50MB, then we have a full backup at midnight.
Is there any problem doing this?
I could take the full backup then logs, then diffs like 4 or 5 times a day, but storage is not a problem with this instance. I can afford to lose 1 hour of data, that's no problem, that's why I'm creating all of the diffs (I'm using full recovery mode). Then I can restore only the full + diff , instead of full + log + log + log + log + log + log + log + diff.
sql-server sql-server-2008-r2 recovery
edited 5 mins ago
Paul White♦
54.2k14288461
asked yesterday
Racer SQLRacer SQL
3,12342566
add a comment |
I have three databases which are not big. Every differential backup is roughly 50MB, then we have a full backup at midnight.
Is there any problem doing this?
I could take the full backup then logs, then diffs like 4 or 5 times a day, but storage is not a problem with this instance. I can afford to lose 1 hour of data, that's no problem, that's why I'm creating all of the diffs (I'm using full recovery mode). Then I can restore only the full + diff , instead of full + log + log + log + log + log + log + log + diff.
sql-server sql-server-2008-r2 recovery
edited 5 mins ago
Paul White♦
54.2k14288461
asked yesterday
Racer SQLRacer SQL
3,12342566
add a comment |
I have three databases which are not big. Every differential backup is roughly 50MB, then we have a full backup at midnight.
Is there any problem doing this?
I could take the full backup then logs, then diffs like 4 or 5 times a day, but storage is not a problem with this instance. I can afford to lose 1 hour of data, that's no problem, that's why I'm creating all of the diffs (I'm using full recovery mode). Then I can restore only the full + diff , instead of full + log + log + log + log + log + log + log + diff.
sql-server sql-server-2008-r2 recovery
edited 5 mins ago
Paul White♦
54.2k14288461
asked yesterday
Racer SQLRacer SQL
3,12342566
I have three databases which are not big. Every differential backup is roughly 50MB, then we have a full backup at midnight.
Is there any problem doing this?
I could take the full backup then logs, then diffs like 4 or 5 times a day, but storage is not a problem with this instance. I can afford to lose 1 hour of data, that's no problem, that's why I'm creating all of the diffs (I'm using full recovery mode). Then I can restore only the full + diff , instead of full + log + log + log + log + log + log + log + diff.
sql-server sql-server-2008-r2 recovery
sql-server sql-server-2008-r2 recovery
edited 5 mins ago
Paul White♦
54.2k14288461
asked yesterday
Racer SQLRacer SQL
3,12342566
edited 5 mins ago
Paul White♦
54.2k14288461
asked yesterday
Racer SQLRacer SQL
3,12342566
edited 5 mins ago
Paul White♦
54.2k14288461
edited 5 mins ago
Paul White♦
54.2k14288461
edited 5 mins ago
Paul White♦
54.2k14288461
54.2k14288461
asked yesterday
Racer SQLRacer SQL
3,12342566
asked yesterday
Racer SQLRacer SQL
3,12342566
asked yesterday
Racer SQLRacer SQL
3,12342566
3,12342566
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Storage is not why you take log backups. You take log backups when the database is in full recovery model, and you need point-in-time recovery between full or incremental backups.
If your business can afford only 1 hour of lost data, then I'd typically setup nightly full backups for smaller databases, with log backups every 30 minutes during business hours (or even 24-hours per day).
If you have the database in simple recovery model, and each database is only a couple of hundred megabytes, you could simple take full backups every hour, or half hour.
Essentially, the decision comes down to answering these questions:
- What is my recovery point objective?
- What is my recovery time objective?
See Wikipedia for great details about what those two things actually mean.
If you're storing high-value, business-critical data in those databases, you should understand log backups, and the transaction log and recovery in general, to avoid being in a situation where you're unexpectedly missing data, or down for an extended period of time.
answered yesterday
Max VernonMax Vernon
52.5k13115232
2
You can certainly do that, if it fits your RPO/RTO objectives. I'd do a log backup after every diff backup just to ensure the log doesn't grow out of control.
– Max Vernon
yesterday
Also, have in mind, with differential backups you can't go between them, let's say you create full backup at 8 AM, differential at 12 PM and Tlog at 1 PM. If a user made a mistake at 11 AM, you can only go either 8 AM or 12 PM, nothing between. In other case you have full backup 8 AM , tlog every hour and differential 12 PM, the user made a mistake 11:30 AM you can easily recover the DB until 11:30 AM (assuming you have all your tlog chain at least until 12 PM) This is something you should also consider.
– dbamex
16 hours ago
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f234972%2fis-there-a-problem-creating-only-differential-backups-every-hour%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Storage is not why you take log backups. You take log backups when the database is in full recovery model, and you need point-in-time recovery between full or incremental backups.
If your business can afford only 1 hour of lost data, then I'd typically setup nightly full backups for smaller databases, with log backups every 30 minutes during business hours (or even 24-hours per day).
If you have the database in simple recovery model, and each database is only a couple of hundred megabytes, you could simple take full backups every hour, or half hour.
Essentially, the decision comes down to answering these questions:
- What is my recovery point objective?
- What is my recovery time objective?
See Wikipedia for great details about what those two things actually mean.
If you're storing high-value, business-critical data in those databases, you should understand log backups, and the transaction log and recovery in general, to avoid being in a situation where you're unexpectedly missing data, or down for an extended period of time.
answered yesterday
Max VernonMax Vernon
52.5k13115232
2
You can certainly do that, if it fits your RPO/RTO objectives. I'd do a log backup after every diff backup just to ensure the log doesn't grow out of control.
– Max Vernon
yesterday
Also, have in mind, with differential backups you can't go between them, let's say you create full backup at 8 AM, differential at 12 PM and Tlog at 1 PM. If a user made a mistake at 11 AM, you can only go either 8 AM or 12 PM, nothing between. In other case you have full backup 8 AM , tlog every hour and differential 12 PM, the user made a mistake 11:30 AM you can easily recover the DB until 11:30 AM (assuming you have all your tlog chain at least until 12 PM) This is something you should also consider.
– dbamex
16 hours ago
add a comment |
Storage is not why you take log backups. You take log backups when the database is in full recovery model, and you need point-in-time recovery between full or incremental backups.
If your business can afford only 1 hour of lost data, then I'd typically setup nightly full backups for smaller databases, with log backups every 30 minutes during business hours (or even 24-hours per day).
If you have the database in simple recovery model, and each database is only a couple of hundred megabytes, you could simple take full backups every hour, or half hour.
Essentially, the decision comes down to answering these questions:
- What is my recovery point objective?
- What is my recovery time objective?
See Wikipedia for great details about what those two things actually mean.
If you're storing high-value, business-critical data in those databases, you should understand log backups, and the transaction log and recovery in general, to avoid being in a situation where you're unexpectedly missing data, or down for an extended period of time.
answered yesterday
Max VernonMax Vernon
52.5k13115232
2
You can certainly do that, if it fits your RPO/RTO objectives. I'd do a log backup after every diff backup just to ensure the log doesn't grow out of control.
– Max Vernon
yesterday
Also, have in mind, with differential backups you can't go between them, let's say you create full backup at 8 AM, differential at 12 PM and Tlog at 1 PM. If a user made a mistake at 11 AM, you can only go either 8 AM or 12 PM, nothing between. In other case you have full backup 8 AM , tlog every hour and differential 12 PM, the user made a mistake 11:30 AM you can easily recover the DB until 11:30 AM (assuming you have all your tlog chain at least until 12 PM) This is something you should also consider.
– dbamex
16 hours ago
add a comment |
Storage is not why you take log backups. You take log backups when the database is in full recovery model, and you need point-in-time recovery between full or incremental backups.
If your business can afford only 1 hour of lost data, then I'd typically setup nightly full backups for smaller databases, with log backups every 30 minutes during business hours (or even 24-hours per day).
If you have the database in simple recovery model, and each database is only a couple of hundred megabytes, you could simple take full backups every hour, or half hour.
Essentially, the decision comes down to answering these questions:
- What is my recovery point objective?
- What is my recovery time objective?
See Wikipedia for great details about what those two things actually mean.
If you're storing high-value, business-critical data in those databases, you should understand log backups, and the transaction log and recovery in general, to avoid being in a situation where you're unexpectedly missing data, or down for an extended period of time.
answered yesterday
Max VernonMax Vernon
52.5k13115232
Storage is not why you take log backups. You take log backups when the database is in full recovery model, and you need point-in-time recovery between full or incremental backups.
If your business can afford only 1 hour of lost data, then I'd typically setup nightly full backups for smaller databases, with log backups every 30 minutes during business hours (or even 24-hours per day).
If you have the database in simple recovery model, and each database is only a couple of hundred megabytes, you could simple take full backups every hour, or half hour.
Essentially, the decision comes down to answering these questions:
- What is my recovery point objective?
- What is my recovery time objective?
See Wikipedia for great details about what those two things actually mean.
If you're storing high-value, business-critical data in those databases, you should understand log backups, and the transaction log and recovery in general, to avoid being in a situation where you're unexpectedly missing data, or down for an extended period of time.
answered yesterday
Max VernonMax Vernon
52.5k13115232
answered yesterday
Max VernonMax Vernon
52.5k13115232
answered yesterday
Max VernonMax Vernon
52.5k13115232
answered yesterday
Max VernonMax Vernon
52.5k13115232
52.5k13115232
2
You can certainly do that, if it fits your RPO/RTO objectives. I'd do a log backup after every diff backup just to ensure the log doesn't grow out of control.
– Max Vernon
yesterday
Also, have in mind, with differential backups you can't go between them, let's say you create full backup at 8 AM, differential at 12 PM and Tlog at 1 PM. If a user made a mistake at 11 AM, you can only go either 8 AM or 12 PM, nothing between. In other case you have full backup 8 AM , tlog every hour and differential 12 PM, the user made a mistake 11:30 AM you can easily recover the DB until 11:30 AM (assuming you have all your tlog chain at least until 12 PM) This is something you should also consider.
– dbamex
16 hours ago
add a comment |
2
You can certainly do that, if it fits your RPO/RTO objectives. I'd do a log backup after every diff backup just to ensure the log doesn't grow out of control.
– Max Vernon
yesterday
Also, have in mind, with differential backups you can't go between them, let's say you create full backup at 8 AM, differential at 12 PM and Tlog at 1 PM. If a user made a mistake at 11 AM, you can only go either 8 AM or 12 PM, nothing between. In other case you have full backup 8 AM , tlog every hour and differential 12 PM, the user made a mistake 11:30 AM you can easily recover the DB until 11:30 AM (assuming you have all your tlog chain at least until 12 PM) This is something you should also consider.
– dbamex
16 hours ago
2
2
You can certainly do that, if it fits your RPO/RTO objectives. I'd do a log backup after every diff backup just to ensure the log doesn't grow out of control.
– Max Vernon
yesterday
You can certainly do that, if it fits your RPO/RTO objectives. I'd do a log backup after every diff backup just to ensure the log doesn't grow out of control.
– Max Vernon
yesterday
Also, have in mind, with differential backups you can't go between them, let's say you create full backup at 8 AM, differential at 12 PM and Tlog at 1 PM. If a user made a mistake at 11 AM, you can only go either 8 AM or 12 PM, nothing between. In other case you have full backup 8 AM , tlog every hour and differential 12 PM, the user made a mistake 11:30 AM you can easily recover the DB until 11:30 AM (assuming you have all your tlog chain at least until 12 PM) This is something you should also consider.
– dbamex
16 hours ago
Also, have in mind, with differential backups you can't go between them, let's say you create full backup at 8 AM, differential at 12 PM and Tlog at 1 PM. If a user made a mistake at 11 AM, you can only go either 8 AM or 12 PM, nothing between. In other case you have full backup 8 AM , tlog every hour and differential 12 PM, the user made a mistake 11:30 AM you can easily recover the DB until 11:30 AM (assuming you have all your tlog chain at least until 12 PM) This is something you should also consider.
– dbamex
16 hours ago
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f234972%2fis-there-a-problem-creating-only-differential-backups-every-hour%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
