September 23, 2008 at 6:22 am
Hi All,
Suppose I have mistakenly deleted a table.I dont have any backup ... can I recover the deleted table by
- Taking the Full backup
- Taking the TLog backup
- Restoring the full backup
- Restoring Tlog backup using stop at
Regards,
[font="Verdana"]Sqlfrenzy[/font]
September 23, 2008 at 7:09 am
If a Full backup has never been taken, there is no way to recover the table - any backup taken after the table was deleted would not include the table. If a Full backup had been taken and you had a complete chain of TLog backups, you could take a final TLog backup then restore the Full and each TLog backup using WITH NO RECOVERY until you reached the final TLog backup when you would use WITH RECOVERY and STOP AT.
Cath
September 23, 2008 at 7:09 am
That would depend if you have ever performed a full backup of that database. If not, that database would behave as if it was running in the simple recovery model, and the transaction log is truncated after every committed transaction, preventing you from recovering your table in the manner you specified.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
September 23, 2008 at 7:37 am
Cath Trimble (9/23/2008)
If a Full backup has never been taken, there is no way to recover the table - any backup taken after the table was deleted would not include the table. If a Full backup had been taken and you had a complete chain of TLog backups, you could take a final TLog backup then restore the Full and each TLog backup using WITH NO RECOVERY until you reached the final TLog backup when you would use WITH RECOVERY and STOP AT.Cath
Thanks...but what if I have a full database backup and no tran log back....then If i take the tlog back up and restore using with recover and stop at ..will this work...
Regards,
[font="Verdana"]Sqlfrenzy[/font]
September 23, 2008 at 8:38 am
Yes, it would, though bear in mind that if you do a Full backup the TLog will continue to grow until you do a TLog backup. There are posts on this site about exactly that scenario where the TLog has grown to fill the entire disk - it's not pretty. You really do need to do regular TLog backups. Do a search in BOL for 'Recovery Models ', you'll get loads of useful results.
September 23, 2008 at 8:52 am
What exactly do you have?
Full backup on: __/__/2008?
Log Backup?
When was the table deleted?
What has been done since the table was deleted.
September 23, 2008 at 9:03 am
Ahmad Osama (9/23/2008)
Cath Trimble (9/23/2008)
If a Full backup has never been taken, there is no way to recover the table - any backup taken after the table was deleted would not include the table. If a Full backup had been taken and you had a complete chain of TLog backups, you could take a final TLog backup then restore the Full and each TLog backup using WITH NO RECOVERY until you reached the final TLog backup when you would use WITH RECOVERY and STOP AT.Cath
Thanks...but what if I have a full database backup and no tran log back....then If i take the tlog back up and restore using with recover and stop at ..will this work...
Depends when the log was last truncated. In full your choices are to backup the log up or (bad option) truncate it regularly to stop it from growing to fill the drive.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 23, 2008 at 11:20 am
Steve Jones - Editor (9/23/2008)
What exactly do you have?Full backup on: __/__/2008?
Log Backup?
When was the table deleted?
What has been done since the table was deleted.
Latest full backup...no log backup.....nothing significant has been done since then
Regards,
[font="Verdana"]Sqlfrenzy[/font]
September 23, 2008 at 12:16 pm
To recover in this scenario you need a full backup from before the table was dropped and an unbroken log backup chain from that full backup up until just before the table was dropped.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 23, 2008 at 9:37 pm
GilaMonster (9/23/2008)
To recover in this scenario you need a full backup from before the table was dropped and an unbroken log backup chain from that full backup up until just before the table was dropped.
this means that as per the given scenario I can't get the deleted table
Regards,
[font="Verdana"]Sqlfrenzy[/font]
September 24, 2008 at 12:21 am
Ahmad Osama (9/23/2008)
GilaMonster (9/23/2008)
To recover in this scenario you need a full backup from before the table was dropped and an unbroken log backup chain from that full backup up until just before the table was dropped.this means that as per the given scenario I can't get the deleted table
Correct. If you restore a full backup and then a tran log backup, and StopAt time given must be after the full backup occurred.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply