Mixing
Restoring database with less data than the backup file
I'm not really experienced with DBA and SQL, so I need some recommendations from you.
I have a production database backup file, however it is very large, so when I'm executing restore, for example, making a copy of data in testing environment - it takes ages to restore.
I read this: Is a partial restore of data from backup possible?
It is similar to what I want to do, but not exactly equivalent.
To be clear, I need an answer to the following question: is there any possibility to restore database with fewer records than in the backup file?
For instance, if the backup file table contains 1 million records, I just want to restore only 1k records.
sql-server sql-server-2012 backup restore
edited Jan 11 at 19:53
RDFozz
9,88231530
asked Jan 11 at 13:01
J.J.RedicJ.J.Redic
132
add a comment |
I'm not really experienced with DBA and SQL, so I need some recommendations from you.
I have a production database backup file, however it is very large, so when I'm executing restore, for example, making a copy of data in testing environment - it takes ages to restore.
I read this: Is a partial restore of data from backup possible?
It is similar to what I want to do, but not exactly equivalent.
To be clear, I need an answer to the following question: is there any possibility to restore database with fewer records than in the backup file?
For instance, if the backup file table contains 1 million records, I just want to restore only 1k records.
sql-server sql-server-2012 backup restore
edited Jan 11 at 19:53
RDFozz
9,88231530
asked Jan 11 at 13:01
J.J.RedicJ.J.Redic
132
A couple of things to check to help the restore go faster. On the destination, is "Instant File Initialization" enabled (check the SQL Server errorlog for that phrase). Also, is the log file (.ldf) very large compared to the data file(s) (.mdf, .ndf). This is all about how fast SQL can write the pages. Tony's answer is a valid one :)
– Kevin3NF
Jan 11 at 13:54
add a comment |
I'm not really experienced with DBA and SQL, so I need some recommendations from you.
I have a production database backup file, however it is very large, so when I'm executing restore, for example, making a copy of data in testing environment - it takes ages to restore.
I read this: Is a partial restore of data from backup possible?
It is similar to what I want to do, but not exactly equivalent.
To be clear, I need an answer to the following question: is there any possibility to restore database with fewer records than in the backup file?
For instance, if the backup file table contains 1 million records, I just want to restore only 1k records.
sql-server sql-server-2012 backup restore
edited Jan 11 at 19:53
RDFozz
9,88231530
asked Jan 11 at 13:01
J.J.RedicJ.J.Redic
132
I'm not really experienced with DBA and SQL, so I need some recommendations from you.
I have a production database backup file, however it is very large, so when I'm executing restore, for example, making a copy of data in testing environment - it takes ages to restore.
I read this: Is a partial restore of data from backup possible?
It is similar to what I want to do, but not exactly equivalent.
To be clear, I need an answer to the following question: is there any possibility to restore database with fewer records than in the backup file?
For instance, if the backup file table contains 1 million records, I just want to restore only 1k records.
sql-server sql-server-2012 backup restore
sql-server sql-server-2012 backup restore
edited Jan 11 at 19:53
RDFozz
9,88231530
asked Jan 11 at 13:01
J.J.RedicJ.J.Redic
132
edited Jan 11 at 19:53
RDFozz
9,88231530
asked Jan 11 at 13:01
J.J.RedicJ.J.Redic
132
edited Jan 11 at 19:53
RDFozz
9,88231530
edited Jan 11 at 19:53
RDFozz
9,88231530
edited Jan 11 at 19:53
RDFozz
9,88231530
9,88231530
asked Jan 11 at 13:01
J.J.RedicJ.J.Redic
132
asked Jan 11 at 13:01
J.J.RedicJ.J.Redic
132
asked Jan 11 at 13:01
J.J.RedicJ.J.Redic
132
132
A couple of things to check to help the restore go faster. On the destination, is "Instant File Initialization" enabled (check the SQL Server errorlog for that phrase). Also, is the log file (.ldf) very large compared to the data file(s) (.mdf, .ndf). This is all about how fast SQL can write the pages. Tony's answer is a valid one :)
– Kevin3NF
Jan 11 at 13:54
add a comment |
A couple of things to check to help the restore go faster. On the destination, is "Instant File Initialization" enabled (check the SQL Server errorlog for that phrase). Also, is the log file (.ldf) very large compared to the data file(s) (.mdf, .ndf). This is all about how fast SQL can write the pages. Tony's answer is a valid one :)
– Kevin3NF
Jan 11 at 13:54
A couple of things to check to help the restore go faster. On the destination, is "Instant File Initialization" enabled (check the SQL Server errorlog for that phrase). Also, is the log file (.ldf) very large compared to the data file(s) (.mdf, .ndf). This is all about how fast SQL can write the pages. Tony's answer is a valid one :)
– Kevin3NF
Jan 11 at 13:54
A couple of things to check to help the restore go faster. On the destination, is "Instant File Initialization" enabled (check the SQL Server errorlog for that phrase). Also, is the log file (.ldf) very large compared to the data file(s) (.mdf, .ndf). This is all about how fast SQL can write the pages. Tony's answer is a valid one :)
– Kevin3NF
Jan 11 at 13:54
add a comment |
2 Answers
2
active
oldest
votes
No, it is not possible to only restore n number of rows from a given table. The restore process does not even know about the tables, let alone how many rows are in each table, as it works on the database page level.
To accomplish your goal of only getting n number of rows from production to non-production without doing a full restore you'll probably want to set up an ETL process.
answered Jan 11 at 13:19
Tony HinkleTony Hinkle
2,0131419
add a comment |
is there any possibility to restore database with fewer records then
it is in backup file
This is possible only when your db is organized in special mode: it should use partitioned tables and different partitions should reside in different filegroups.
In this case you can use Piecemeal Restores and restore only Primary and some other filegroup.
If for example, your tables are partitioned by date and every month resides on its own FG, you can restore only PRIMARY and FG201901 and you'll get only the data regarding 2019-01. Other files will not be created at all even if restored database will still aware of other filegroups that will remain offline, but your tables will be queryable unless you try to access data regarding prior periods of time.
This way you can achieve "only some rows from certain tables" after restore.
Alternatively you can use no partitioning but place your tables on different FG. This way you can restore only a subset of original tables.
answered Jan 11 at 14:35
sepupicsepupic
7,238818
This is correct, but OP should be note that this is a fairly advanced DBA operation, requiring substantial structural changes to the production database. So not a simple fix, by any means.
– BradC
Jan 11 at 15:21
I tried to answer a general question, how it's possible to restore less rows that the backup contains. It certainely should not be done in OP's case
– sepupic
Jan 11 at 15:27
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%2f226896%2frestoring-database-with-less-data-than-the-backup-file%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
No, it is not possible to only restore n number of rows from a given table. The restore process does not even know about the tables, let alone how many rows are in each table, as it works on the database page level.
To accomplish your goal of only getting n number of rows from production to non-production without doing a full restore you'll probably want to set up an ETL process.
answered Jan 11 at 13:19
Tony HinkleTony Hinkle
2,0131419
add a comment |
No, it is not possible to only restore n number of rows from a given table. The restore process does not even know about the tables, let alone how many rows are in each table, as it works on the database page level.
To accomplish your goal of only getting n number of rows from production to non-production without doing a full restore you'll probably want to set up an ETL process.
answered Jan 11 at 13:19
Tony HinkleTony Hinkle
2,0131419
add a comment |
No, it is not possible to only restore n number of rows from a given table. The restore process does not even know about the tables, let alone how many rows are in each table, as it works on the database page level.
To accomplish your goal of only getting n number of rows from production to non-production without doing a full restore you'll probably want to set up an ETL process.
answered Jan 11 at 13:19
Tony HinkleTony Hinkle
2,0131419
No, it is not possible to only restore n number of rows from a given table. The restore process does not even know about the tables, let alone how many rows are in each table, as it works on the database page level.
To accomplish your goal of only getting n number of rows from production to non-production without doing a full restore you'll probably want to set up an ETL process.
answered Jan 11 at 13:19
Tony HinkleTony Hinkle
2,0131419
answered Jan 11 at 13:19
Tony HinkleTony Hinkle
2,0131419
answered Jan 11 at 13:19
Tony HinkleTony Hinkle
2,0131419
answered Jan 11 at 13:19
Tony HinkleTony Hinkle
2,0131419
2,0131419
add a comment |
add a comment |
is there any possibility to restore database with fewer records then
it is in backup file
This is possible only when your db is organized in special mode: it should use partitioned tables and different partitions should reside in different filegroups.
In this case you can use Piecemeal Restores and restore only Primary and some other filegroup.
If for example, your tables are partitioned by date and every month resides on its own FG, you can restore only PRIMARY and FG201901 and you'll get only the data regarding 2019-01. Other files will not be created at all even if restored database will still aware of other filegroups that will remain offline, but your tables will be queryable unless you try to access data regarding prior periods of time.
This way you can achieve "only some rows from certain tables" after restore.
Alternatively you can use no partitioning but place your tables on different FG. This way you can restore only a subset of original tables.
answered Jan 11 at 14:35
sepupicsepupic
7,238818
This is correct, but OP should be note that this is a fairly advanced DBA operation, requiring substantial structural changes to the production database. So not a simple fix, by any means.
– BradC
Jan 11 at 15:21
I tried to answer a general question, how it's possible to restore less rows that the backup contains. It certainely should not be done in OP's case
– sepupic
Jan 11 at 15:27
add a comment |
is there any possibility to restore database with fewer records then
it is in backup file
This is possible only when your db is organized in special mode: it should use partitioned tables and different partitions should reside in different filegroups.
In this case you can use Piecemeal Restores and restore only Primary and some other filegroup.
If for example, your tables are partitioned by date and every month resides on its own FG, you can restore only PRIMARY and FG201901 and you'll get only the data regarding 2019-01. Other files will not be created at all even if restored database will still aware of other filegroups that will remain offline, but your tables will be queryable unless you try to access data regarding prior periods of time.
This way you can achieve "only some rows from certain tables" after restore.
Alternatively you can use no partitioning but place your tables on different FG. This way you can restore only a subset of original tables.
answered Jan 11 at 14:35
sepupicsepupic
7,238818
This is correct, but OP should be note that this is a fairly advanced DBA operation, requiring substantial structural changes to the production database. So not a simple fix, by any means.
– BradC
Jan 11 at 15:21
I tried to answer a general question, how it's possible to restore less rows that the backup contains. It certainely should not be done in OP's case
– sepupic
Jan 11 at 15:27
add a comment |
is there any possibility to restore database with fewer records then
it is in backup file
This is possible only when your db is organized in special mode: it should use partitioned tables and different partitions should reside in different filegroups.
In this case you can use Piecemeal Restores and restore only Primary and some other filegroup.
If for example, your tables are partitioned by date and every month resides on its own FG, you can restore only PRIMARY and FG201901 and you'll get only the data regarding 2019-01. Other files will not be created at all even if restored database will still aware of other filegroups that will remain offline, but your tables will be queryable unless you try to access data regarding prior periods of time.
This way you can achieve "only some rows from certain tables" after restore.
Alternatively you can use no partitioning but place your tables on different FG. This way you can restore only a subset of original tables.
answered Jan 11 at 14:35
sepupicsepupic
7,238818
is there any possibility to restore database with fewer records then
it is in backup file
This is possible only when your db is organized in special mode: it should use partitioned tables and different partitions should reside in different filegroups.
In this case you can use Piecemeal Restores and restore only Primary and some other filegroup.
If for example, your tables are partitioned by date and every month resides on its own FG, you can restore only PRIMARY and FG201901 and you'll get only the data regarding 2019-01. Other files will not be created at all even if restored database will still aware of other filegroups that will remain offline, but your tables will be queryable unless you try to access data regarding prior periods of time.
This way you can achieve "only some rows from certain tables" after restore.
Alternatively you can use no partitioning but place your tables on different FG. This way you can restore only a subset of original tables.
answered Jan 11 at 14:35
sepupicsepupic
7,238818
answered Jan 11 at 14:35
sepupicsepupic
7,238818
answered Jan 11 at 14:35
sepupicsepupic
7,238818
answered Jan 11 at 14:35
sepupicsepupic
7,238818
7,238818
This is correct, but OP should be note that this is a fairly advanced DBA operation, requiring substantial structural changes to the production database. So not a simple fix, by any means.
– BradC
Jan 11 at 15:21
I tried to answer a general question, how it's possible to restore less rows that the backup contains. It certainely should not be done in OP's case
– sepupic
Jan 11 at 15:27
add a comment |
This is correct, but OP should be note that this is a fairly advanced DBA operation, requiring substantial structural changes to the production database. So not a simple fix, by any means.
– BradC
Jan 11 at 15:21
I tried to answer a general question, how it's possible to restore less rows that the backup contains. It certainely should not be done in OP's case
– sepupic
Jan 11 at 15:27
This is correct, but OP should be note that this is a fairly advanced DBA operation, requiring substantial structural changes to the production database. So not a simple fix, by any means.
– BradC
Jan 11 at 15:21
This is correct, but OP should be note that this is a fairly advanced DBA operation, requiring substantial structural changes to the production database. So not a simple fix, by any means.
– BradC
Jan 11 at 15:21
I tried to answer a general question, how it's possible to restore less rows that the backup contains. It certainely should not be done in OP's case
– sepupic
Jan 11 at 15:27
I tried to answer a general question, how it's possible to restore less rows that the backup contains. It certainely should not be done in OP's case
– sepupic
Jan 11 at 15:27
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%2f226896%2frestoring-database-with-less-data-than-the-backup-file%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
A couple of things to check to help the restore go faster. On the destination, is "Instant File Initialization" enabled (check the SQL Server errorlog for that phrase). Also, is the log file (.ldf) very large compared to the data file(s) (.mdf, .ndf). This is all about how fast SQL can write the pages. Tony's answer is a valid one :)
– Kevin3NF
Jan 11 at 13:54