Restoring from an LDF fileWhy Does the Transaction Log Keep Growing or Run Out of Space?Recover accidentally...
Is it common to refer to someone as "Prof. Dr. [LastName]"?
Does an increasing sequence of reals converge if the difference of consecutive terms approaches zero?
Was Opportunity's last message to Earth "My battery is low and it's getting dark"?
How do I add a strong "onion flavor" to the biryani (in restaurant style)?
How do I write a maintainable, fast, compile-time bit-mask in C++?
What are Holorydmachines?
Short story where Earth is given a racist governor who likes species of a certain color
Is it ethical to apply for a job on someone's behalf?
Why is Shelob considered evil?
Automated testing of chained Queueable jobs in Salesforce
Why didn't Lorentz conclude that no object can go faster than light?
Is it appropriate to give a culturally-traditional gift to a female coworker?
Coworker is trying to get me to sign his petition to run for office. How to decline politely?
Identical projects by students at two different colleges: still plagiarism?
I hate taking lectures, can I still survive in academia?
Buying a "Used" Router
Why would you use 2 alternate layout buttons instead of 1, when only one can be selected at once
Manager has noticed coworker's excessive breaks. Should I warn him?
How bad is a Computer Science course that doesn't teach Design Patterns?
How should I ship cards?
How can guns be countered by melee combat without raw-ability or exceptional explanations?
Discouraging missile alpha strikes
Run a command that requires sudo after a time has passed
Why is the meaning of kanji 閑 is "leisure"?
Restoring from an LDF file
Why Does the Transaction Log Keep Growing or Run Out of Space?Recover accidentally deleted rows from a tableRestoring SQL Server database backup file for read-only access - is it possible to skip the ldf file creation?Restore Database SQL Server 2012Restoring differential backup creates DEFUNCT log file?Combining log shipping and regular log file backupsUnderstanding The Transaction LogBloated transaction log and .bak backup size confusionSQL Server: can I restore full DB file backup to First LSN (i.e. when backup started)?Make a newer version of the LDF-file work with an older version of the MDF-fileAttach ldf more current than mdf
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♦ 17 mins 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♦ 17 mins 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♦ 17 mins 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♦ 17 mins 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