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 9 hours ago
RDFozz
9,82731430
asked 15 hours ago
J.J.RedicJ.J.Redic
132
New contributor
J.J.Redic is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
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 9 hours ago
RDFozz
9,82731430
asked 15 hours ago
J.J.RedicJ.J.Redic
132
New contributor
J.J.Redic is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
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
15 hours ago
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 9 hours ago
RDFozz
9,82731430
asked 15 hours ago
J.J.RedicJ.J.Redic
132
New contributor
J.J.Redic is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
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 9 hours ago
RDFozz
9,82731430
asked 15 hours ago
J.J.RedicJ.J.Redic
132
New contributor
J.J.Redic is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited 9 hours ago
RDFozz
9,82731430
asked 15 hours ago
J.J.RedicJ.J.Redic
132
New contributor
J.J.Redic is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited 9 hours ago
RDFozz
9,82731430
edited 9 hours ago
RDFozz
9,82731430
edited 9 hours ago
RDFozz
9,82731430
9,82731430
asked 15 hours ago
J.J.RedicJ.J.Redic
132
New contributor
J.J.Redic is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 15 hours ago
J.J.RedicJ.J.Redic
132
asked 15 hours ago
J.J.RedicJ.J.Redic
132
132
New contributor
J.J.Redic is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
J.J.Redic is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
J.J.Redic is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
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
15 hours ago
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
15 hours ago
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
15 hours ago
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
15 hours ago
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 15 hours ago
Tony HinkleTony Hinkle
1,7381418
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 14 hours ago
sepupicsepupic
6,786817
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
13 hours ago
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
13 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
});
}
});
J.J.Redic is a new contributor. Be nice, and check out our Code of Conduct.
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 15 hours ago
Tony HinkleTony Hinkle
1,7381418
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 15 hours ago
Tony HinkleTony Hinkle
1,7381418
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 15 hours ago
Tony HinkleTony Hinkle
1,7381418
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 15 hours ago
Tony HinkleTony Hinkle
1,7381418
answered 15 hours ago
Tony HinkleTony Hinkle
1,7381418
answered 15 hours ago
Tony HinkleTony Hinkle
1,7381418
answered 15 hours ago
Tony HinkleTony Hinkle
1,7381418
1,7381418
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 14 hours ago
sepupicsepupic
6,786817
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
13 hours ago
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
13 hours ago
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 14 hours ago
sepupicsepupic
6,786817
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
13 hours ago
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
13 hours ago
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 14 hours ago
sepupicsepupic
6,786817
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 14 hours ago
sepupicsepupic
6,786817
answered 14 hours ago
sepupicsepupic
6,786817
answered 14 hours ago
sepupicsepupic
6,786817
answered 14 hours ago
sepupicsepupic
6,786817
6,786817
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
13 hours ago
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
13 hours ago
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
13 hours ago
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
13 hours ago
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
13 hours ago
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
13 hours ago
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
13 hours ago
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
13 hours ago
add a comment |
J.J.Redic is a new contributor. Be nice, and check out our Code of Conduct.
J.J.Redic is a new contributor. Be nice, and check out our Code of Conduct.
J.J.Redic is a new contributor. Be nice, and check out our Code of Conduct.
J.J.Redic is a new contributor. Be nice, and check out our Code of Conduct.
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
15 hours ago