How to query data in backups?SQL Server Restore a SINGLE table from a backupIs it possible to backup and restore part of a database in sql-server?Indications from obscenely high Operator Costs in Query Plan?SQL Server 2008 R2 Restore COPY_ONLY full backup with transaction logsAre ZRM (Zmanda Recovery Manager) incremental backups reliable?Execution plan has TOP operator for query without TOP or ORDER BYHow do I recover from a failed SQL Restore?Sql Server (2017?) Lat/Long index and queryHow can I track down what's filling my 'tempdb' transaction log?Archiving SQL Server data on disk to be pottentially restoredRestoring PostgreSQL tables fasterCannot insert duplicate key row on a non-unique index?
Why is there a need to prevent a racist, sexist, or otherwise bigoted vendor from discriminating who they sell to?
During the Space Shuttle Columbia Disaster of 2003, Why Did The Flight Director Say, "Lock the doors."?
Does the United States guarantee any unique freedoms?
Dereferencing a pointer in a for loop initializer creates a seg fault
Why does Intel's Haswell chip allow multiplication to be twice as fast as addition?
Why did the RAAF procure the F/A-18 despite being purpose-built for carriers?
Why are the inside diameters of some pipe larger than the stated size?
Dropdowns & Chevrons for Right to Left languages
How to help new students accept function notation
Could one become a successful researcher by writing some really good papers while being outside academia?
In the movie Harry Potter and the Order or the Phoenix, why didn't Mr. Filch succeed to open the Room of Requirement if it's what he needed?
How can I tell if a flight itinerary is fake?
Does this smartphone photo show Mars just below the Sun?
Can a character who casts Shapechange and turns into a spellcaster use innate spellcasting to cast spells with a long casting time?
How to write "upright" integrals with automatic sizing
Is it true that control+alt+delete only became a thing because IBM would not build Bill Gates a computer with a task manager button?
Acceptable to cut steak before searing?
Is TA-ing worth the opportunity cost?
Can a College of Swords bard use Blade Flourishes multiple times in a turn?
How do we avoid CI-driven development...?
What are good ways to improve as a writer other than writing courses?
Blocking people from taking pictures of me with smartphone
Can a PC attack themselves with an unarmed strike?
Double blind peer review when paper cites author's GitHub repo for code
How to query data in backups?
SQL Server Restore a SINGLE table from a backupIs it possible to backup and restore part of a database in sql-server?Indications from obscenely high Operator Costs in Query Plan?SQL Server 2008 R2 Restore COPY_ONLY full backup with transaction logsAre ZRM (Zmanda Recovery Manager) incremental backups reliable?Execution plan has TOP operator for query without TOP or ORDER BYHow do I recover from a failed SQL Restore?Sql Server (2017?) Lat/Long index and queryHow can I track down what's filling my 'tempdb' transaction log?Archiving SQL Server data on disk to be pottentially restoredRestoring PostgreSQL tables fasterCannot insert duplicate key row on a non-unique index?
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;
I have an ASP.NET app using SQL Server 2017. There's a bug somewhere that's causing some records to be deleted, and I'm trying to recover the data and find the cause of the bug. We take daily backups, and I have a query to find which records should be there but aren't, so I want to go through and run the query on each backup. The backups are fairly large, but I only need 2 tables out of the whole database for the query. What would be the most efficient way to do this over more than a month of backups?
sql-server backup restore sql-server-2017
asked 10 hours ago
user4323718user4323718
161 bronze badge
New contributor
user4323718 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 have an ASP.NET app using SQL Server 2017. There's a bug somewhere that's causing some records to be deleted, and I'm trying to recover the data and find the cause of the bug. We take daily backups, and I have a query to find which records should be there but aren't, so I want to go through and run the query on each backup. The backups are fairly large, but I only need 2 tables out of the whole database for the query. What would be the most efficient way to do this over more than a month of backups?
sql-server backup restore sql-server-2017
asked 10 hours ago
user4323718user4323718
161 bronze badge
New contributor
user4323718 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
3
You can't restore individual tables from SQL backups without resorting to third-party solutions. See dba.stackexchange.com/questions/34358/… and dba.stackexchange.com/questions/4000/….
– Doug Deden
10 hours ago
How big is the backup file?
– Anthony Genovese
10 hours ago
@AnthonyGenovese about 4GB
– user4323718
9 hours ago
add a comment |
I have an ASP.NET app using SQL Server 2017. There's a bug somewhere that's causing some records to be deleted, and I'm trying to recover the data and find the cause of the bug. We take daily backups, and I have a query to find which records should be there but aren't, so I want to go through and run the query on each backup. The backups are fairly large, but I only need 2 tables out of the whole database for the query. What would be the most efficient way to do this over more than a month of backups?
sql-server backup restore sql-server-2017
asked 10 hours ago
user4323718user4323718
161 bronze badge
New contributor
user4323718 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
I have an ASP.NET app using SQL Server 2017. There's a bug somewhere that's causing some records to be deleted, and I'm trying to recover the data and find the cause of the bug. We take daily backups, and I have a query to find which records should be there but aren't, so I want to go through and run the query on each backup. The backups are fairly large, but I only need 2 tables out of the whole database for the query. What would be the most efficient way to do this over more than a month of backups?
sql-server backup restore sql-server-2017
sql-server backup restore sql-server-2017
asked 10 hours ago
user4323718user4323718
161 bronze badge
New contributor
user4323718 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 10 hours ago
user4323718user4323718
161 bronze badge
New contributor
user4323718 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 10 hours ago
user4323718user4323718
161 bronze badge
New contributor
user4323718 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 10 hours ago
user4323718user4323718
161 bronze badge
asked 10 hours ago
user4323718user4323718
161 bronze badge
161 bronze badge
New contributor
user4323718 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
user4323718 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
3
You can't restore individual tables from SQL backups without resorting to third-party solutions. See dba.stackexchange.com/questions/34358/… and dba.stackexchange.com/questions/4000/….
– Doug Deden
10 hours ago
How big is the backup file?
– Anthony Genovese
10 hours ago
@AnthonyGenovese about 4GB
– user4323718
9 hours ago
add a comment |
3
You can't restore individual tables from SQL backups without resorting to third-party solutions. See dba.stackexchange.com/questions/34358/… and dba.stackexchange.com/questions/4000/….
– Doug Deden
10 hours ago
How big is the backup file?
– Anthony Genovese
10 hours ago
@AnthonyGenovese about 4GB
– user4323718
9 hours ago
3
3
You can't restore individual tables from SQL backups without resorting to third-party solutions. See dba.stackexchange.com/questions/34358/… and dba.stackexchange.com/questions/4000/….
– Doug Deden
10 hours ago
You can't restore individual tables from SQL backups without resorting to third-party solutions. See dba.stackexchange.com/questions/34358/… and dba.stackexchange.com/questions/4000/….
– Doug Deden
10 hours ago
How big is the backup file?
– Anthony Genovese
10 hours ago
How big is the backup file?
– Anthony Genovese
10 hours ago
@AnthonyGenovese about 4GB
– user4323718
9 hours ago
@AnthonyGenovese about 4GB
– user4323718
9 hours ago
add a comment |
2 Answers
2
active
oldest
votes
My advice would be to just restore the entire backup. In my current environment, someone accidentally screws up some table once every few months. It just happens. Of course, it always hits our biggest database. 200GB backup file, 1+TB when fully restored. It is just easier to restore a copy somewhere, find the specific table we need, and just update that specific data. The whole process takes about an hour or so. I doubt any other solution would be quicker, worth the extra financial costs, or added complexity.
answered 10 hours ago
Anthony GenoveseAnthony Genovese
1,7502 gold badges12 silver badges26 bronze badges
add a comment |
Without using third party software, your best bet is to restore the backups one by one and running your query on the restored databases.
If you're looking for the last backup that contains those records, you can be smart about which databases you recover, so you don't have to recover all of them.
Example, restore the oldest backup, if it contains the records, go the the backup in the middle, if that doesn't contain the records take the backup between those backups, etc etc.
It's still tedious work, but at least you wouldn't have to restore them all.
answered 10 hours ago
Yannick LiekensYannick Liekens
1814 bronze badges
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
);
);
user4323718 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%2f244983%2fhow-to-query-data-in-backups%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
My advice would be to just restore the entire backup. In my current environment, someone accidentally screws up some table once every few months. It just happens. Of course, it always hits our biggest database. 200GB backup file, 1+TB when fully restored. It is just easier to restore a copy somewhere, find the specific table we need, and just update that specific data. The whole process takes about an hour or so. I doubt any other solution would be quicker, worth the extra financial costs, or added complexity.
answered 10 hours ago
Anthony GenoveseAnthony Genovese
1,7502 gold badges12 silver badges26 bronze badges
add a comment |
My advice would be to just restore the entire backup. In my current environment, someone accidentally screws up some table once every few months. It just happens. Of course, it always hits our biggest database. 200GB backup file, 1+TB when fully restored. It is just easier to restore a copy somewhere, find the specific table we need, and just update that specific data. The whole process takes about an hour or so. I doubt any other solution would be quicker, worth the extra financial costs, or added complexity.
answered 10 hours ago
Anthony GenoveseAnthony Genovese
1,7502 gold badges12 silver badges26 bronze badges
add a comment |
My advice would be to just restore the entire backup. In my current environment, someone accidentally screws up some table once every few months. It just happens. Of course, it always hits our biggest database. 200GB backup file, 1+TB when fully restored. It is just easier to restore a copy somewhere, find the specific table we need, and just update that specific data. The whole process takes about an hour or so. I doubt any other solution would be quicker, worth the extra financial costs, or added complexity.
answered 10 hours ago
Anthony GenoveseAnthony Genovese
1,7502 gold badges12 silver badges26 bronze badges
My advice would be to just restore the entire backup. In my current environment, someone accidentally screws up some table once every few months. It just happens. Of course, it always hits our biggest database. 200GB backup file, 1+TB when fully restored. It is just easier to restore a copy somewhere, find the specific table we need, and just update that specific data. The whole process takes about an hour or so. I doubt any other solution would be quicker, worth the extra financial costs, or added complexity.
answered 10 hours ago
Anthony GenoveseAnthony Genovese
1,7502 gold badges12 silver badges26 bronze badges
answered 10 hours ago
Anthony GenoveseAnthony Genovese
1,7502 gold badges12 silver badges26 bronze badges
answered 10 hours ago
Anthony GenoveseAnthony Genovese
1,7502 gold badges12 silver badges26 bronze badges
answered 10 hours ago
Anthony GenoveseAnthony Genovese
1,7502 gold badges12 silver badges26 bronze badges
1,7502 gold badges12 silver badges26 bronze badges
add a comment |
add a comment |
Without using third party software, your best bet is to restore the backups one by one and running your query on the restored databases.
If you're looking for the last backup that contains those records, you can be smart about which databases you recover, so you don't have to recover all of them.
Example, restore the oldest backup, if it contains the records, go the the backup in the middle, if that doesn't contain the records take the backup between those backups, etc etc.
It's still tedious work, but at least you wouldn't have to restore them all.
answered 10 hours ago
Yannick LiekensYannick Liekens
1814 bronze badges
add a comment |
Without using third party software, your best bet is to restore the backups one by one and running your query on the restored databases.
If you're looking for the last backup that contains those records, you can be smart about which databases you recover, so you don't have to recover all of them.
Example, restore the oldest backup, if it contains the records, go the the backup in the middle, if that doesn't contain the records take the backup between those backups, etc etc.
It's still tedious work, but at least you wouldn't have to restore them all.
answered 10 hours ago
Yannick LiekensYannick Liekens
1814 bronze badges
add a comment |
Without using third party software, your best bet is to restore the backups one by one and running your query on the restored databases.
If you're looking for the last backup that contains those records, you can be smart about which databases you recover, so you don't have to recover all of them.
Example, restore the oldest backup, if it contains the records, go the the backup in the middle, if that doesn't contain the records take the backup between those backups, etc etc.
It's still tedious work, but at least you wouldn't have to restore them all.
answered 10 hours ago
Yannick LiekensYannick Liekens
1814 bronze badges
Without using third party software, your best bet is to restore the backups one by one and running your query on the restored databases.
If you're looking for the last backup that contains those records, you can be smart about which databases you recover, so you don't have to recover all of them.
Example, restore the oldest backup, if it contains the records, go the the backup in the middle, if that doesn't contain the records take the backup between those backups, etc etc.
It's still tedious work, but at least you wouldn't have to restore them all.
answered 10 hours ago
Yannick LiekensYannick Liekens
1814 bronze badges
answered 10 hours ago
Yannick LiekensYannick Liekens
1814 bronze badges
answered 10 hours ago
Yannick LiekensYannick Liekens
1814 bronze badges
answered 10 hours ago
Yannick LiekensYannick Liekens
1814 bronze badges
1814 bronze badges
add a comment |
add a comment |
user4323718 is a new contributor. Be nice, and check out our Code of Conduct.
user4323718 is a new contributor. Be nice, and check out our Code of Conduct.
user4323718 is a new contributor. Be nice, and check out our Code of Conduct.
user4323718 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%2f244983%2fhow-to-query-data-in-backups%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
3
You can't restore individual tables from SQL backups without resorting to third-party solutions. See dba.stackexchange.com/questions/34358/… and dba.stackexchange.com/questions/4000/….
– Doug Deden
10 hours ago
How big is the backup file?
– Anthony Genovese
10 hours ago
@AnthonyGenovese about 4GB
– user4323718
9 hours ago