Restoring from an LDF file
A record was accidentally updated which I need to be restored. Now, please bear in mind I'm not super-experienced in restore/backing up databases...
I have a 7gb MDF file and a 10gb LDF file (Sql Express 2016) . The database's recovery mode was set as Simple - I have since changed this to FULL and performed a backup of the Transaction LOG.
I have restored the DB's original backup file (some 6 months ago - this is a development Sql Instance, so no backups are regularly taken of it). but when I try to restore the log file, I can select no timeline from the past.
My question is, is it possible to just "rollback" the db to a few hours ago , using this huge LDF file and if not, what possible use does this LDF file serve if I can't restore from it?
Thanks
sql-server
bumped to the homepage by Community♦ 1 min ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
migrated from stackoverflow.com Jul 27 '17 at 16:48
This question came from our site for professional and enthusiast programmers.
|
show 6 more comments
A record was accidentally updated which I need to be restored. Now, please bear in mind I'm not super-experienced in restore/backing up databases...
I have a 7gb MDF file and a 10gb LDF file (Sql Express 2016) . The database's recovery mode was set as Simple - I have since changed this to FULL and performed a backup of the Transaction LOG.
I have restored the DB's original backup file (some 6 months ago - this is a development Sql Instance, so no backups are regularly taken of it). but when I try to restore the log file, I can select no timeline from the past.
My question is, is it possible to just "rollback" the db to a few hours ago , using this huge LDF file and if not, what possible use does this LDF file serve if I can't restore from it?
Thanks
sql-server
bumped to the homepage by Community♦ 1 min ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
migrated from stackoverflow.com Jul 27 '17 at 16:48
This question came from our site for professional and enthusiast programmers.
1
If the transaction was committed and you were in simple recovery then you can't do a point in time recovery.
– SQLChao
Jul 27 '17 at 14:41
1
Since you are in simple recovery you are screwed unless you have a recent backup. If you do have a backup you could restore that backup using a different database name and find the data you need to recover. Then transfer it to your main database. If you don't have that data in a backup there is nothing you can do.
– Sean Lange
Jul 27 '17 at 14:43
1
The LDF file holds changes that are not yet committed to the MDF file. When transactions are committed they are removed from the LDF but the LDF remains the same size, but is empty. If you have a large enough series of data changes in a transaction that exceed the LDF size, it will grow. The LDF remains at the largest size until it is shrunk.
– cloudsafe
Jul 27 '17 at 14:51
1
@MuckersMate Do not delete the LDF unless you really need it. It will only have to grow again next time you have large enough concurrent transaction s, which will affect performance.
– cloudsafe
Jul 27 '17 at 16:23
2
@Chuck The size limit only applies to the sum of all data files, not to log files.
– Aaron Bertrand♦
Jul 27 '17 at 17:03
|
show 6 more comments
A record was accidentally updated which I need to be restored. Now, please bear in mind I'm not super-experienced in restore/backing up databases...
I have a 7gb MDF file and a 10gb LDF file (Sql Express 2016) . The database's recovery mode was set as Simple - I have since changed this to FULL and performed a backup of the Transaction LOG.
I have restored the DB's original backup file (some 6 months ago - this is a development Sql Instance, so no backups are regularly taken of it). but when I try to restore the log file, I can select no timeline from the past.
My question is, is it possible to just "rollback" the db to a few hours ago , using this huge LDF file and if not, what possible use does this LDF file serve if I can't restore from it?
Thanks
sql-server
A record was accidentally updated which I need to be restored. Now, please bear in mind I'm not super-experienced in restore/backing up databases...
I have a 7gb MDF file and a 10gb LDF file (Sql Express 2016) . The database's recovery mode was set as Simple - I have since changed this to FULL and performed a backup of the Transaction LOG.
I have restored the DB's original backup file (some 6 months ago - this is a development Sql Instance, so no backups are regularly taken of it). but when I try to restore the log file, I can select no timeline from the past.
My question is, is it possible to just "rollback" the db to a few hours ago , using this huge LDF file and if not, what possible use does this LDF file serve if I can't restore from it?
Thanks
sql-server
sql-server
asked Jul 27 '17 at 14:31
Muckers Mate
bumped to the homepage by Community♦ 1 min ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 1 min ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
migrated from stackoverflow.com Jul 27 '17 at 16:48
This question came from our site for professional and enthusiast programmers.
migrated from stackoverflow.com Jul 27 '17 at 16:48
This question came from our site for professional and enthusiast programmers.
1
If the transaction was committed and you were in simple recovery then you can't do a point in time recovery.
– SQLChao
Jul 27 '17 at 14:41
1
Since you are in simple recovery you are screwed unless you have a recent backup. If you do have a backup you could restore that backup using a different database name and find the data you need to recover. Then transfer it to your main database. If you don't have that data in a backup there is nothing you can do.
– Sean Lange
Jul 27 '17 at 14:43
1
The LDF file holds changes that are not yet committed to the MDF file. When transactions are committed they are removed from the LDF but the LDF remains the same size, but is empty. If you have a large enough series of data changes in a transaction that exceed the LDF size, it will grow. The LDF remains at the largest size until it is shrunk.
– cloudsafe
Jul 27 '17 at 14:51
1
@MuckersMate Do not delete the LDF unless you really need it. It will only have to grow again next time you have large enough concurrent transaction s, which will affect performance.
– cloudsafe
Jul 27 '17 at 16:23
2
@Chuck The size limit only applies to the sum of all data files, not to log files.
– Aaron Bertrand♦
Jul 27 '17 at 17:03
|
show 6 more comments
1
If the transaction was committed and you were in simple recovery then you can't do a point in time recovery.
– SQLChao
Jul 27 '17 at 14:41
1
Since you are in simple recovery you are screwed unless you have a recent backup. If you do have a backup you could restore that backup using a different database name and find the data you need to recover. Then transfer it to your main database. If you don't have that data in a backup there is nothing you can do.
– Sean Lange
Jul 27 '17 at 14:43
1
The LDF file holds changes that are not yet committed to the MDF file. When transactions are committed they are removed from the LDF but the LDF remains the same size, but is empty. If you have a large enough series of data changes in a transaction that exceed the LDF size, it will grow. The LDF remains at the largest size until it is shrunk.
– cloudsafe
Jul 27 '17 at 14:51
1
@MuckersMate Do not delete the LDF unless you really need it. It will only have to grow again next time you have large enough concurrent transaction s, which will affect performance.
– cloudsafe
Jul 27 '17 at 16:23
2
@Chuck The size limit only applies to the sum of all data files, not to log files.
– Aaron Bertrand♦
Jul 27 '17 at 17:03
1
1
If the transaction was committed and you were in simple recovery then you can't do a point in time recovery.
– SQLChao
Jul 27 '17 at 14:41
If the transaction was committed and you were in simple recovery then you can't do a point in time recovery.
– SQLChao
Jul 27 '17 at 14:41
1
1
Since you are in simple recovery you are screwed unless you have a recent backup. If you do have a backup you could restore that backup using a different database name and find the data you need to recover. Then transfer it to your main database. If you don't have that data in a backup there is nothing you can do.
– Sean Lange
Jul 27 '17 at 14:43
Since you are in simple recovery you are screwed unless you have a recent backup. If you do have a backup you could restore that backup using a different database name and find the data you need to recover. Then transfer it to your main database. If you don't have that data in a backup there is nothing you can do.
– Sean Lange
Jul 27 '17 at 14:43
1
1
The LDF file holds changes that are not yet committed to the MDF file. When transactions are committed they are removed from the LDF but the LDF remains the same size, but is empty. If you have a large enough series of data changes in a transaction that exceed the LDF size, it will grow. The LDF remains at the largest size until it is shrunk.
– cloudsafe
Jul 27 '17 at 14:51
The LDF file holds changes that are not yet committed to the MDF file. When transactions are committed they are removed from the LDF but the LDF remains the same size, but is empty. If you have a large enough series of data changes in a transaction that exceed the LDF size, it will grow. The LDF remains at the largest size until it is shrunk.
– cloudsafe
Jul 27 '17 at 14:51
1
1
@MuckersMate Do not delete the LDF unless you really need it. It will only have to grow again next time you have large enough concurrent transaction s, which will affect performance.
– cloudsafe
Jul 27 '17 at 16:23
@MuckersMate Do not delete the LDF unless you really need it. It will only have to grow again next time you have large enough concurrent transaction s, which will affect performance.
– cloudsafe
Jul 27 '17 at 16:23
2
2
@Chuck The size limit only applies to the sum of all data files, not to log files.
– Aaron Bertrand♦
Jul 27 '17 at 17:03
@Chuck The size limit only applies to the sum of all data files, not to log files.
– Aaron Bertrand♦
Jul 27 '17 at 17:03
|
show 6 more comments
1 Answer
1
active
oldest
votes
Lots of comments saying it but no answer posted.
My question is, is it possible to just "rollback" the db to a few hours ago , using this huge LDF file
No, You are in simple recovery, once the transaction is committed it is not kept in the .ldf there are no point in time recovery options.
and if not, what possible use does this LDF file serve if I can't restore from it?
You do not differentiate between the allocated and used space on .ldf file. My best guess is that long ago it was in full recovery, and no one was doing t-log backups so it grew huge. Then is was changed to simple recovery and it became mostly empty. See related Why Does the Transaction Log Keep Growing or Run Out of Space?
answered Jan 16 at 17:37
James JenkinsJames Jenkins
1,73821938
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%2f182009%2frestoring-from-an-ldf-file%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
Lots of comments saying it but no answer posted.
My question is, is it possible to just "rollback" the db to a few hours ago , using this huge LDF file
No, You are in simple recovery, once the transaction is committed it is not kept in the .ldf there are no point in time recovery options.
and if not, what possible use does this LDF file serve if I can't restore from it?
You do not differentiate between the allocated and used space on .ldf file. My best guess is that long ago it was in full recovery, and no one was doing t-log backups so it grew huge. Then is was changed to simple recovery and it became mostly empty. See related Why Does the Transaction Log Keep Growing or Run Out of Space?
answered Jan 16 at 17:37
James JenkinsJames Jenkins
1,73821938
add a comment |
Lots of comments saying it but no answer posted.
My question is, is it possible to just "rollback" the db to a few hours ago , using this huge LDF file
No, You are in simple recovery, once the transaction is committed it is not kept in the .ldf there are no point in time recovery options.
and if not, what possible use does this LDF file serve if I can't restore from it?
You do not differentiate between the allocated and used space on .ldf file. My best guess is that long ago it was in full recovery, and no one was doing t-log backups so it grew huge. Then is was changed to simple recovery and it became mostly empty. See related Why Does the Transaction Log Keep Growing or Run Out of Space?
answered Jan 16 at 17:37
James JenkinsJames Jenkins
1,73821938
add a comment |
Lots of comments saying it but no answer posted.
My question is, is it possible to just "rollback" the db to a few hours ago , using this huge LDF file
No, You are in simple recovery, once the transaction is committed it is not kept in the .ldf there are no point in time recovery options.
and if not, what possible use does this LDF file serve if I can't restore from it?
You do not differentiate between the allocated and used space on .ldf file. My best guess is that long ago it was in full recovery, and no one was doing t-log backups so it grew huge. Then is was changed to simple recovery and it became mostly empty. See related Why Does the Transaction Log Keep Growing or Run Out of Space?
answered Jan 16 at 17:37
James JenkinsJames Jenkins
1,73821938
Lots of comments saying it but no answer posted.
My question is, is it possible to just "rollback" the db to a few hours ago , using this huge LDF file
No, You are in simple recovery, once the transaction is committed it is not kept in the .ldf there are no point in time recovery options.
and if not, what possible use does this LDF file serve if I can't restore from it?
You do not differentiate between the allocated and used space on .ldf file. My best guess is that long ago it was in full recovery, and no one was doing t-log backups so it grew huge. Then is was changed to simple recovery and it became mostly empty. See related Why Does the Transaction Log Keep Growing or Run Out of Space?
answered Jan 16 at 17:37
James JenkinsJames Jenkins
1,73821938
answered Jan 16 at 17:37
James JenkinsJames Jenkins
1,73821938
answered Jan 16 at 17:37
James JenkinsJames Jenkins
1,73821938
answered Jan 16 at 17:37
James JenkinsJames Jenkins
1,73821938
1,73821938
add a comment |
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%2f182009%2frestoring-from-an-ldf-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
1
If the transaction was committed and you were in simple recovery then you can't do a point in time recovery.
– SQLChao
Jul 27 '17 at 14:41
1
Since you are in simple recovery you are screwed unless you have a recent backup. If you do have a backup you could restore that backup using a different database name and find the data you need to recover. Then transfer it to your main database. If you don't have that data in a backup there is nothing you can do.
– Sean Lange
Jul 27 '17 at 14:43
1
The LDF file holds changes that are not yet committed to the MDF file. When transactions are committed they are removed from the LDF but the LDF remains the same size, but is empty. If you have a large enough series of data changes in a transaction that exceed the LDF size, it will grow. The LDF remains at the largest size until it is shrunk.
– cloudsafe
Jul 27 '17 at 14:51
1
@MuckersMate Do not delete the LDF unless you really need it. It will only have to grow again next time you have large enough concurrent transaction s, which will affect performance.
– cloudsafe
Jul 27 '17 at 16:23
2
@Chuck The size limit only applies to the sum of all data files, not to log files.
– Aaron Bertrand♦
Jul 27 '17 at 17:03