DB back up year wise and hold only last two calendar years data only
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
My database has 8 years of data and the code is taking some time to retrieve the data. I am planning to hold only last two years of data and the remaining data will be moved to temporary tables. For example employee table has last 8 years data so I want to create employee2013, emploee2014 and corresponding year data will moved to these tables.
Guide me how to do this and is this approach is correct or give me any better idea.
I am using SQL Server 2014 and have 300 tables with with hundreds of thousands of rows
sql
sql-server edited Nov 24 '18 at 10:02
James Z
11.2k71936
asked Nov 24 '18 at 6:53
Subbarao GaddamSubbarao Gaddam
1
add a comment |
My database has 8 years of data and the code is taking some time to retrieve the data. I am planning to hold only last two years of data and the remaining data will be moved to temporary tables. For example employee table has last 8 years data so I want to create employee2013, emploee2014 and corresponding year data will moved to these tables.
Guide me how to do this and is this approach is correct or give me any better idea.
I am using SQL Server 2014 and have 300 tables with with hundreds of thousands of rows
sql
sql-server edited Nov 24 '18 at 10:02
James Z
11.2k71936
asked Nov 24 '18 at 6:53
Subbarao GaddamSubbarao Gaddam
1
in brief : 1. create new table. 2. insert into new table. 3. delete from old table
– Squirrel
Nov 24 '18 at 8:03
Thanks any other way than this because It is too complex, have 300 tables with multiple relationship keys are there. Is there any easy way like generate script from the database.
– Subbarao Gaddam
Nov 24 '18 at 8:46
1
Using Indian words like "lac" will definitely cause confusion. Please don't do that.
– James Z
Nov 24 '18 at 10:02
add a comment |
My database has 8 years of data and the code is taking some time to retrieve the data. I am planning to hold only last two years of data and the remaining data will be moved to temporary tables. For example employee table has last 8 years data so I want to create employee2013, emploee2014 and corresponding year data will moved to these tables.
Guide me how to do this and is this approach is correct or give me any better idea.
I am using SQL Server 2014 and have 300 tables with with hundreds of thousands of rows
sql
sql-server edited Nov 24 '18 at 10:02
James Z
11.2k71936
asked Nov 24 '18 at 6:53
Subbarao GaddamSubbarao Gaddam
1
My database has 8 years of data and the code is taking some time to retrieve the data. I am planning to hold only last two years of data and the remaining data will be moved to temporary tables. For example employee table has last 8 years data so I want to create employee2013, emploee2014 and corresponding year data will moved to these tables.
Guide me how to do this and is this approach is correct or give me any better idea.
I am using SQL Server 2014 and have 300 tables with with hundreds of thousands of rows
sql
sql-server sql
sql-server edited Nov 24 '18 at 10:02
James Z
11.2k71936
asked Nov 24 '18 at 6:53
Subbarao GaddamSubbarao Gaddam
1
edited Nov 24 '18 at 10:02
James Z
11.2k71936
asked Nov 24 '18 at 6:53
Subbarao GaddamSubbarao Gaddam
1
edited Nov 24 '18 at 10:02
James Z
11.2k71936
edited Nov 24 '18 at 10:02
James Z
11.2k71936
edited Nov 24 '18 at 10:02
James Z
11.2k71936
11.2k71936
asked Nov 24 '18 at 6:53
Subbarao GaddamSubbarao Gaddam
1
asked Nov 24 '18 at 6:53
Subbarao GaddamSubbarao Gaddam
1
asked Nov 24 '18 at 6:53
Subbarao GaddamSubbarao Gaddam
1
1
in brief : 1. create new table. 2. insert into new table. 3. delete from old table
– Squirrel
Nov 24 '18 at 8:03
Thanks any other way than this because It is too complex, have 300 tables with multiple relationship keys are there. Is there any easy way like generate script from the database.
– Subbarao Gaddam
Nov 24 '18 at 8:46
1
Using Indian words like "lac" will definitely cause confusion. Please don't do that.
– James Z
Nov 24 '18 at 10:02
add a comment |
in brief : 1. create new table. 2. insert into new table. 3. delete from old table
– Squirrel
Nov 24 '18 at 8:03
Thanks any other way than this because It is too complex, have 300 tables with multiple relationship keys are there. Is there any easy way like generate script from the database.
– Subbarao Gaddam
Nov 24 '18 at 8:46
1
Using Indian words like "lac" will definitely cause confusion. Please don't do that.
– James Z
Nov 24 '18 at 10:02
in brief : 1. create new table. 2. insert into new table. 3. delete from old table
– Squirrel
Nov 24 '18 at 8:03
in brief : 1. create new table. 2. insert into new table. 3. delete from old table
– Squirrel
Nov 24 '18 at 8:03
Thanks any other way than this because It is too complex, have 300 tables with multiple relationship keys are there. Is there any easy way like generate script from the database.
– Subbarao Gaddam
Nov 24 '18 at 8:46
Thanks any other way than this because It is too complex, have 300 tables with multiple relationship keys are there. Is there any easy way like generate script from the database.
– Subbarao Gaddam
Nov 24 '18 at 8:46
1
1
Using Indian words like "lac" will definitely cause confusion. Please don't do that.
– James Z
Nov 24 '18 at 10:02
Using Indian words like "lac" will definitely cause confusion. Please don't do that.
– James Z
Nov 24 '18 at 10:02
add a comment |
1 Answer
1
active
oldest
votes
Huh? Why would you move older data to temporary tables. Those are deleted periodically.
In any case, it seems like you want to separate data out by years. This is called "partitioning". This is a broad topic, so you should start to learn about it from the documentation.
Once you have partitions, you can physically keep only the most recent two years by dropping older partitions or moving them to new tables. However, that might not be necessary. You can just create a view that accesses the two most recent years and have users access the views.
You don't specify why you want to do this. If the reason is just performance, then multiple tables is definitely not the right solution. In fact, for most purposes, a combination of partitions and indexes is probably the way to go.
answered Nov 24 '18 at 12:25
Gordon LinoffGordon Linoff
798k37320425
my reason is just performance.
– Subbarao Gaddam
Nov 24 '18 at 14:39
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
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%2fstackoverflow.com%2fquestions%2f53455893%2fdb-back-up-year-wise-and-hold-only-last-two-calendar-years-data-only%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
Huh? Why would you move older data to temporary tables. Those are deleted periodically.
In any case, it seems like you want to separate data out by years. This is called "partitioning". This is a broad topic, so you should start to learn about it from the documentation.
Once you have partitions, you can physically keep only the most recent two years by dropping older partitions or moving them to new tables. However, that might not be necessary. You can just create a view that accesses the two most recent years and have users access the views.
You don't specify why you want to do this. If the reason is just performance, then multiple tables is definitely not the right solution. In fact, for most purposes, a combination of partitions and indexes is probably the way to go.
answered Nov 24 '18 at 12:25
Gordon LinoffGordon Linoff
798k37320425
my reason is just performance.
– Subbarao Gaddam
Nov 24 '18 at 14:39
add a comment |
Huh? Why would you move older data to temporary tables. Those are deleted periodically.
In any case, it seems like you want to separate data out by years. This is called "partitioning". This is a broad topic, so you should start to learn about it from the documentation.
Once you have partitions, you can physically keep only the most recent two years by dropping older partitions or moving them to new tables. However, that might not be necessary. You can just create a view that accesses the two most recent years and have users access the views.
You don't specify why you want to do this. If the reason is just performance, then multiple tables is definitely not the right solution. In fact, for most purposes, a combination of partitions and indexes is probably the way to go.
answered Nov 24 '18 at 12:25
Gordon LinoffGordon Linoff
798k37320425
my reason is just performance.
– Subbarao Gaddam
Nov 24 '18 at 14:39
add a comment |
Huh? Why would you move older data to temporary tables. Those are deleted periodically.
In any case, it seems like you want to separate data out by years. This is called "partitioning". This is a broad topic, so you should start to learn about it from the documentation.
Once you have partitions, you can physically keep only the most recent two years by dropping older partitions or moving them to new tables. However, that might not be necessary. You can just create a view that accesses the two most recent years and have users access the views.
You don't specify why you want to do this. If the reason is just performance, then multiple tables is definitely not the right solution. In fact, for most purposes, a combination of partitions and indexes is probably the way to go.
answered Nov 24 '18 at 12:25
Gordon LinoffGordon Linoff
798k37320425
Huh? Why would you move older data to temporary tables. Those are deleted periodically.
In any case, it seems like you want to separate data out by years. This is called "partitioning". This is a broad topic, so you should start to learn about it from the documentation.
Once you have partitions, you can physically keep only the most recent two years by dropping older partitions or moving them to new tables. However, that might not be necessary. You can just create a view that accesses the two most recent years and have users access the views.
You don't specify why you want to do this. If the reason is just performance, then multiple tables is definitely not the right solution. In fact, for most purposes, a combination of partitions and indexes is probably the way to go.
answered Nov 24 '18 at 12:25
Gordon LinoffGordon Linoff
798k37320425
answered Nov 24 '18 at 12:25
Gordon LinoffGordon Linoff
798k37320425
answered Nov 24 '18 at 12:25
Gordon LinoffGordon Linoff
798k37320425
answered Nov 24 '18 at 12:25
Gordon LinoffGordon Linoff
798k37320425
798k37320425
my reason is just performance.
– Subbarao Gaddam
Nov 24 '18 at 14:39
add a comment |
my reason is just performance.
– Subbarao Gaddam
Nov 24 '18 at 14:39
my reason is just performance.
– Subbarao Gaddam
Nov 24 '18 at 14:39
my reason is just performance.
– Subbarao Gaddam
Nov 24 '18 at 14:39
add a comment |
Thanks for contributing an answer to Stack Overflow!
- 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%2fstackoverflow.com%2fquestions%2f53455893%2fdb-back-up-year-wise-and-hold-only-last-two-calendar-years-data-only%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
in brief : 1. create new table. 2. insert into new table. 3. delete from old table
– Squirrel
Nov 24 '18 at 8:03
Thanks any other way than this because It is too complex, have 300 tables with multiple relationship keys are there. Is there any easy way like generate script from the database.
– Subbarao Gaddam
Nov 24 '18 at 8:46
1
Using Indian words like "lac" will definitely cause confusion. Please don't do that.
– James Z
Nov 24 '18 at 10:02