Recover deleted table from the database
Currently I deleted one table from the database. But now I want some of the information from that table.
Please suggest how to restore or get back the table.
I am using SQL Server 2008 R2.
sql-server sql-server-2008-r2 recovery disaster-recovery
edited Feb 5 '15 at 12:06
Colin 't Hart
6,40582433
asked Feb 5 '15 at 11:21
B NB N
1232417
add a comment |
Currently I deleted one table from the database. But now I want some of the information from that table.
Please suggest how to restore or get back the table.
I am using SQL Server 2008 R2.
sql-server sql-server-2008-r2 recovery disaster-recovery
edited Feb 5 '15 at 12:06
Colin 't Hart
6,40582433
asked Feb 5 '15 at 11:21
B NB N
1232417
DBMS? Oracle, SQL-Server, DB2? Do you have backups of the database?
– ypercubeᵀᴹ
Feb 5 '15 at 11:24
Sorry, i did not mentioned that. I am usingSQL Server 2008 R2. Yes I have the back up also
– B N
Feb 5 '15 at 11:25
Nadeem: Its good you have backup did you tried restoring it using valid backup. Restore sequence would be full backup backup then differential (if you have) then log backup with stopat clause to stop before table was dropped
– Shanky
Feb 5 '15 at 14:04
add a comment |
Currently I deleted one table from the database. But now I want some of the information from that table.
Please suggest how to restore or get back the table.
I am using SQL Server 2008 R2.
sql-server sql-server-2008-r2 recovery disaster-recovery
edited Feb 5 '15 at 12:06
Colin 't Hart
6,40582433
asked Feb 5 '15 at 11:21
B NB N
1232417
Currently I deleted one table from the database. But now I want some of the information from that table.
Please suggest how to restore or get back the table.
I am using SQL Server 2008 R2.
sql-server sql-server-2008-r2 recovery disaster-recovery
sql-server sql-server-2008-r2 recovery disaster-recovery
edited Feb 5 '15 at 12:06
Colin 't Hart
6,40582433
asked Feb 5 '15 at 11:21
B NB N
1232417
edited Feb 5 '15 at 12:06
Colin 't Hart
6,40582433
asked Feb 5 '15 at 11:21
B NB N
1232417
edited Feb 5 '15 at 12:06
Colin 't Hart
6,40582433
edited Feb 5 '15 at 12:06
Colin 't Hart
6,40582433
edited Feb 5 '15 at 12:06
Colin 't Hart
6,40582433
6,40582433
asked Feb 5 '15 at 11:21
B NB N
1232417
asked Feb 5 '15 at 11:21
B NB N
1232417
asked Feb 5 '15 at 11:21
B NB N
1232417
1232417
DBMS? Oracle, SQL-Server, DB2? Do you have backups of the database?
– ypercubeᵀᴹ
Feb 5 '15 at 11:24
Sorry, i did not mentioned that. I am usingSQL Server 2008 R2. Yes I have the back up also
– B N
Feb 5 '15 at 11:25
Nadeem: Its good you have backup did you tried restoring it using valid backup. Restore sequence would be full backup backup then differential (if you have) then log backup with stopat clause to stop before table was dropped
– Shanky
Feb 5 '15 at 14:04
add a comment |
DBMS? Oracle, SQL-Server, DB2? Do you have backups of the database?
– ypercubeᵀᴹ
Feb 5 '15 at 11:24
Sorry, i did not mentioned that. I am usingSQL Server 2008 R2. Yes I have the back up also
– B N
Feb 5 '15 at 11:25
Nadeem: Its good you have backup did you tried restoring it using valid backup. Restore sequence would be full backup backup then differential (if you have) then log backup with stopat clause to stop before table was dropped
– Shanky
Feb 5 '15 at 14:04
DBMS? Oracle, SQL-Server, DB2? Do you have backups of the database?
– ypercubeᵀᴹ
Feb 5 '15 at 11:24
DBMS? Oracle, SQL-Server, DB2? Do you have backups of the database?
– ypercubeᵀᴹ
Feb 5 '15 at 11:24
Sorry, i did not mentioned that. I am using
SQL Server 2008 R2. Yes I have the back up also– B N
Feb 5 '15 at 11:25
Sorry, i did not mentioned that. I am using
SQL Server 2008 R2. Yes I have the back up also– B N
Feb 5 '15 at 11:25
Nadeem: Its good you have backup did you tried restoring it using valid backup. Restore sequence would be full backup backup then differential (if you have) then log backup with stopat clause to stop before table was dropped
– Shanky
Feb 5 '15 at 14:04
Nadeem: Its good you have backup did you tried restoring it using valid backup. Restore sequence would be full backup backup then differential (if you have) then log backup with stopat clause to stop before table was dropped
– Shanky
Feb 5 '15 at 14:04
add a comment |
3 Answers
3
active
oldest
votes
You can restore the BACKUP with a different name in the same instance and follow steps below.
1) restore the database ( right click on databases > Restore ( it can be in the same instance )) with a differente name.
2) In this new database ( The restored one ) , open tables , search the table you've deleted, right button, Script table as > CREATE TO. This will crate a Script. Execute it on the old database ( the one you have deleted the table). It will create the structure of that table.
3) Now you can do something like this:
USE [Old Database]
GO
SELECT * INTO [the blank table you've created in the new database]
FROM [the table on the backup, that has Data]
This will copy the data from the backup table, to the table you created.
answered Feb 5 '15 at 11:45
Racer SQLRacer SQL
2,97642458
4
At least a comment with the reason for the Downvote would be great for me.
– Racer SQL
Feb 5 '15 at 12:19
I did not downvoted. ALso, this is not working for me :)
– B N
Feb 5 '15 at 12:25
Oh, yes i know it wasn't you. I just want to know why the downvote. And...why didn't work for you? i work with a lot of databases and this is what i do the most. Developers love to drop tables and them they want me to restore them.
– Racer SQL
Feb 5 '15 at 12:26
2
I did not downvoted and I agree a downvote should be followed by reason. Downvote is misused here , misuse I mean when you downvote please give reason so that answerer can improve it. As per me your answer lacks clarity if you can add more details how to do a restore it would help or post some script. I also think there is no point in adding apexsql link it is not required OP has backup
– Shanky
Feb 5 '15 at 14:02
@Shanky i will try to explain better. I will update.
– Racer SQL
Feb 5 '15 at 15:22
|
show 4 more comments
If you backup your transaction log you will be able to restore up to a given point in time using the stopat option.
As previously mentioned you should restore it on a different database.
That way you will be able as much data back as possible.
RESTORE LOG AdventureWorks FROM AdventureWorksBackups WITH FILE=4, NORECOVERY, STOPAT = 'Apr 15, 2020 12:00 AM';
answered Feb 9 '15 at 6:35
artificerartificer
130139
add a comment |
Please follow below mentioned steps:
- Restore your DB backup on other server or on same server if it not a production server and you have enough space exist on this server.
- Right click on new restored database and select Task --> Export data
- select --Source server and database ---> destination server and database
- choose your dropped table and export it on required server.
please mentioned the issue which you are facing during the above mentioned activity
edited Feb 5 '15 at 17:38
LowlyDBA
6,82152341
answered Feb 5 '15 at 13:24
Pawan SinghPawan Singh
11
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%2f91168%2frecover-deleted-table-from-the-database%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
You can restore the BACKUP with a different name in the same instance and follow steps below.
1) restore the database ( right click on databases > Restore ( it can be in the same instance )) with a differente name.
2) In this new database ( The restored one ) , open tables , search the table you've deleted, right button, Script table as > CREATE TO. This will crate a Script. Execute it on the old database ( the one you have deleted the table). It will create the structure of that table.
3) Now you can do something like this:
USE [Old Database]
GO
SELECT * INTO [the blank table you've created in the new database]
FROM [the table on the backup, that has Data]
This will copy the data from the backup table, to the table you created.
answered Feb 5 '15 at 11:45
Racer SQLRacer SQL
2,97642458
4
At least a comment with the reason for the Downvote would be great for me.
– Racer SQL
Feb 5 '15 at 12:19
I did not downvoted. ALso, this is not working for me :)
– B N
Feb 5 '15 at 12:25
Oh, yes i know it wasn't you. I just want to know why the downvote. And...why didn't work for you? i work with a lot of databases and this is what i do the most. Developers love to drop tables and them they want me to restore them.
– Racer SQL
Feb 5 '15 at 12:26
2
I did not downvoted and I agree a downvote should be followed by reason. Downvote is misused here , misuse I mean when you downvote please give reason so that answerer can improve it. As per me your answer lacks clarity if you can add more details how to do a restore it would help or post some script. I also think there is no point in adding apexsql link it is not required OP has backup
– Shanky
Feb 5 '15 at 14:02
@Shanky i will try to explain better. I will update.
– Racer SQL
Feb 5 '15 at 15:22
|
show 4 more comments
You can restore the BACKUP with a different name in the same instance and follow steps below.
1) restore the database ( right click on databases > Restore ( it can be in the same instance )) with a differente name.
2) In this new database ( The restored one ) , open tables , search the table you've deleted, right button, Script table as > CREATE TO. This will crate a Script. Execute it on the old database ( the one you have deleted the table). It will create the structure of that table.
3) Now you can do something like this:
USE [Old Database]
GO
SELECT * INTO [the blank table you've created in the new database]
FROM [the table on the backup, that has Data]
This will copy the data from the backup table, to the table you created.
answered Feb 5 '15 at 11:45
Racer SQLRacer SQL
2,97642458
4
At least a comment with the reason for the Downvote would be great for me.
– Racer SQL
Feb 5 '15 at 12:19
I did not downvoted. ALso, this is not working for me :)
– B N
Feb 5 '15 at 12:25
Oh, yes i know it wasn't you. I just want to know why the downvote. And...why didn't work for you? i work with a lot of databases and this is what i do the most. Developers love to drop tables and them they want me to restore them.
– Racer SQL
Feb 5 '15 at 12:26
2
I did not downvoted and I agree a downvote should be followed by reason. Downvote is misused here , misuse I mean when you downvote please give reason so that answerer can improve it. As per me your answer lacks clarity if you can add more details how to do a restore it would help or post some script. I also think there is no point in adding apexsql link it is not required OP has backup
– Shanky
Feb 5 '15 at 14:02
@Shanky i will try to explain better. I will update.
– Racer SQL
Feb 5 '15 at 15:22
|
show 4 more comments
You can restore the BACKUP with a different name in the same instance and follow steps below.
1) restore the database ( right click on databases > Restore ( it can be in the same instance )) with a differente name.
2) In this new database ( The restored one ) , open tables , search the table you've deleted, right button, Script table as > CREATE TO. This will crate a Script. Execute it on the old database ( the one you have deleted the table). It will create the structure of that table.
3) Now you can do something like this:
USE [Old Database]
GO
SELECT * INTO [the blank table you've created in the new database]
FROM [the table on the backup, that has Data]
This will copy the data from the backup table, to the table you created.
answered Feb 5 '15 at 11:45
Racer SQLRacer SQL
2,97642458
You can restore the BACKUP with a different name in the same instance and follow steps below.
1) restore the database ( right click on databases > Restore ( it can be in the same instance )) with a differente name.
2) In this new database ( The restored one ) , open tables , search the table you've deleted, right button, Script table as > CREATE TO. This will crate a Script. Execute it on the old database ( the one you have deleted the table). It will create the structure of that table.
3) Now you can do something like this:
USE [Old Database]
GO
SELECT * INTO [the blank table you've created in the new database]
FROM [the table on the backup, that has Data]
This will copy the data from the backup table, to the table you created.
answered Feb 5 '15 at 11:45
Racer SQLRacer SQL
2,97642458
edited Feb 5 '15 at 16:38
answered Feb 5 '15 at 11:45
Racer SQLRacer SQL
2,97642458
answered Feb 5 '15 at 11:45
Racer SQLRacer SQL
2,97642458
answered Feb 5 '15 at 11:45
Racer SQLRacer SQL
2,97642458
2,97642458
4
At least a comment with the reason for the Downvote would be great for me.
– Racer SQL
Feb 5 '15 at 12:19
I did not downvoted. ALso, this is not working for me :)
– B N
Feb 5 '15 at 12:25
Oh, yes i know it wasn't you. I just want to know why the downvote. And...why didn't work for you? i work with a lot of databases and this is what i do the most. Developers love to drop tables and them they want me to restore them.
– Racer SQL
Feb 5 '15 at 12:26
2
I did not downvoted and I agree a downvote should be followed by reason. Downvote is misused here , misuse I mean when you downvote please give reason so that answerer can improve it. As per me your answer lacks clarity if you can add more details how to do a restore it would help or post some script. I also think there is no point in adding apexsql link it is not required OP has backup
– Shanky
Feb 5 '15 at 14:02
@Shanky i will try to explain better. I will update.
– Racer SQL
Feb 5 '15 at 15:22
|
show 4 more comments
4
At least a comment with the reason for the Downvote would be great for me.
– Racer SQL
Feb 5 '15 at 12:19
I did not downvoted. ALso, this is not working for me :)
– B N
Feb 5 '15 at 12:25
Oh, yes i know it wasn't you. I just want to know why the downvote. And...why didn't work for you? i work with a lot of databases and this is what i do the most. Developers love to drop tables and them they want me to restore them.
– Racer SQL
Feb 5 '15 at 12:26
2
I did not downvoted and I agree a downvote should be followed by reason. Downvote is misused here , misuse I mean when you downvote please give reason so that answerer can improve it. As per me your answer lacks clarity if you can add more details how to do a restore it would help or post some script. I also think there is no point in adding apexsql link it is not required OP has backup
– Shanky
Feb 5 '15 at 14:02
@Shanky i will try to explain better. I will update.
– Racer SQL
Feb 5 '15 at 15:22
4
4
At least a comment with the reason for the Downvote would be great for me.
– Racer SQL
Feb 5 '15 at 12:19
At least a comment with the reason for the Downvote would be great for me.
– Racer SQL
Feb 5 '15 at 12:19
I did not downvoted. ALso, this is not working for me :)
– B N
Feb 5 '15 at 12:25
I did not downvoted. ALso, this is not working for me :)
– B N
Feb 5 '15 at 12:25
Oh, yes i know it wasn't you. I just want to know why the downvote. And...why didn't work for you? i work with a lot of databases and this is what i do the most. Developers love to drop tables and them they want me to restore them.
– Racer SQL
Feb 5 '15 at 12:26
Oh, yes i know it wasn't you. I just want to know why the downvote. And...why didn't work for you? i work with a lot of databases and this is what i do the most. Developers love to drop tables and them they want me to restore them.
– Racer SQL
Feb 5 '15 at 12:26
2
2
I did not downvoted and I agree a downvote should be followed by reason. Downvote is misused here , misuse I mean when you downvote please give reason so that answerer can improve it. As per me your answer lacks clarity if you can add more details how to do a restore it would help or post some script. I also think there is no point in adding apexsql link it is not required OP has backup
– Shanky
Feb 5 '15 at 14:02
I did not downvoted and I agree a downvote should be followed by reason. Downvote is misused here , misuse I mean when you downvote please give reason so that answerer can improve it. As per me your answer lacks clarity if you can add more details how to do a restore it would help or post some script. I also think there is no point in adding apexsql link it is not required OP has backup
– Shanky
Feb 5 '15 at 14:02
@Shanky i will try to explain better. I will update.
– Racer SQL
Feb 5 '15 at 15:22
@Shanky i will try to explain better. I will update.
– Racer SQL
Feb 5 '15 at 15:22
|
show 4 more comments
If you backup your transaction log you will be able to restore up to a given point in time using the stopat option.
As previously mentioned you should restore it on a different database.
That way you will be able as much data back as possible.
RESTORE LOG AdventureWorks FROM AdventureWorksBackups WITH FILE=4, NORECOVERY, STOPAT = 'Apr 15, 2020 12:00 AM';
answered Feb 9 '15 at 6:35
artificerartificer
130139
add a comment |
If you backup your transaction log you will be able to restore up to a given point in time using the stopat option.
As previously mentioned you should restore it on a different database.
That way you will be able as much data back as possible.
RESTORE LOG AdventureWorks FROM AdventureWorksBackups WITH FILE=4, NORECOVERY, STOPAT = 'Apr 15, 2020 12:00 AM';
answered Feb 9 '15 at 6:35
artificerartificer
130139
add a comment |
If you backup your transaction log you will be able to restore up to a given point in time using the stopat option.
As previously mentioned you should restore it on a different database.
That way you will be able as much data back as possible.
RESTORE LOG AdventureWorks FROM AdventureWorksBackups WITH FILE=4, NORECOVERY, STOPAT = 'Apr 15, 2020 12:00 AM';
answered Feb 9 '15 at 6:35
artificerartificer
130139
If you backup your transaction log you will be able to restore up to a given point in time using the stopat option.
As previously mentioned you should restore it on a different database.
That way you will be able as much data back as possible.
RESTORE LOG AdventureWorks FROM AdventureWorksBackups WITH FILE=4, NORECOVERY, STOPAT = 'Apr 15, 2020 12:00 AM';
answered Feb 9 '15 at 6:35
artificerartificer
130139
answered Feb 9 '15 at 6:35
artificerartificer
130139
answered Feb 9 '15 at 6:35
artificerartificer
130139
answered Feb 9 '15 at 6:35
artificerartificer
130139
130139
add a comment |
add a comment |
Please follow below mentioned steps:
- Restore your DB backup on other server or on same server if it not a production server and you have enough space exist on this server.
- Right click on new restored database and select Task --> Export data
- select --Source server and database ---> destination server and database
- choose your dropped table and export it on required server.
please mentioned the issue which you are facing during the above mentioned activity
edited Feb 5 '15 at 17:38
LowlyDBA
6,82152341
answered Feb 5 '15 at 13:24
Pawan SinghPawan Singh
11
add a comment |
Please follow below mentioned steps:
- Restore your DB backup on other server or on same server if it not a production server and you have enough space exist on this server.
- Right click on new restored database and select Task --> Export data
- select --Source server and database ---> destination server and database
- choose your dropped table and export it on required server.
please mentioned the issue which you are facing during the above mentioned activity
edited Feb 5 '15 at 17:38
LowlyDBA
6,82152341
answered Feb 5 '15 at 13:24
Pawan SinghPawan Singh
11
add a comment |
Please follow below mentioned steps:
- Restore your DB backup on other server or on same server if it not a production server and you have enough space exist on this server.
- Right click on new restored database and select Task --> Export data
- select --Source server and database ---> destination server and database
- choose your dropped table and export it on required server.
please mentioned the issue which you are facing during the above mentioned activity
edited Feb 5 '15 at 17:38
LowlyDBA
6,82152341
answered Feb 5 '15 at 13:24
Pawan SinghPawan Singh
11
Please follow below mentioned steps:
- Restore your DB backup on other server or on same server if it not a production server and you have enough space exist on this server.
- Right click on new restored database and select Task --> Export data
- select --Source server and database ---> destination server and database
- choose your dropped table and export it on required server.
please mentioned the issue which you are facing during the above mentioned activity
edited Feb 5 '15 at 17:38
LowlyDBA
6,82152341
answered Feb 5 '15 at 13:24
Pawan SinghPawan Singh
11
edited Feb 5 '15 at 17:38
LowlyDBA
6,82152341
edited Feb 5 '15 at 17:38
LowlyDBA
6,82152341
edited Feb 5 '15 at 17:38
LowlyDBA
6,82152341
6,82152341
answered Feb 5 '15 at 13:24
Pawan SinghPawan Singh
11
answered Feb 5 '15 at 13:24
Pawan SinghPawan Singh
11
answered Feb 5 '15 at 13:24
Pawan SinghPawan Singh
11
11
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%2f91168%2frecover-deleted-table-from-the-database%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
DBMS? Oracle, SQL-Server, DB2? Do you have backups of the database?
– ypercubeᵀᴹ
Feb 5 '15 at 11:24
Sorry, i did not mentioned that. I am using
SQL Server 2008 R2. Yes I have the back up also– B N
Feb 5 '15 at 11:25
Nadeem: Its good you have backup did you tried restoring it using valid backup. Restore sequence would be full backup backup then differential (if you have) then log backup with stopat clause to stop before table was dropped
– Shanky
Feb 5 '15 at 14:04