Member Avatar for aman_dba

hi friends

i am a new bee to the world of sql server, started with it a few months back...
i deleted a table from sql server by mistake, and trying to recover it. but i dont hav any backup concerned. is it possible to recover that table without backup, and if yes, then how??
please revert ASAP..

thanx in advance
Aman

  • 3 Contributors
  • 2 Replies
  • 189 Views
  • 2 Months Discussion Span
  • Latest Post Latest Post by Davidpoul

Dani AI

Generated

Short answer: possible but time‑sensitive and not guaranteed. If you truly have no backups, recovery depends on whether the transaction log or the MDF/LDF still contain the DROP information or row images. Object‑level undo is not built into SQL Server, but you can often (a) read the log to find the DROP, (b) extract DDL/rows from the log or MDF, or (c—last resort) use third‑party forensic tools. is right that a backup is the cleanest fix; ’s suggestion to try recovery software reflects a real option, but treat vendors and demos cautiously. (learn.microsoft.com)

Immediate triage (do these now on the production server — do not keep working against the DB):

  • Stop writes / limit connections (put DB in SINGLE_USER or take it offline) so log/pages aren’t overwritten.
  • If possible take a full copy/backup now (COPY_ONLY) and capture the tail of the log before doing anything else.
    Example commands:
    ALTER DATABASE [YourDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
BACKUP DATABASE [YourDB] TO DISK='C:\recovery\YourDB_copy.bak' WITH COPY_ONLY;
BACKUP LOG [YourDB] TO DISK='C:\recovery\YourDB_tail.trn' WITH NO_TRUNCATE, NORECOVERY;

    The tail‑log / NO_TRUNCATE options are the supported way to preserve recent transactions on a damaged or active DB. (learn.microsoft.com)

Recovery paths

  • If you have any backups: restore to an alternate server and copy the dropped table back with INSERT...SELECT — the least risky approach (restore chain / point‑in‑time). (learn.microsoft.com)
  • If no backups but the log is intact: a log reader or manual fn_dblog analysis can reveal the DROP and allow generation of undo/redo scripts (Paul Randal’s examples show how to find DROPOBJ entries). (sqlskills.com)
  • If logs aren’t usable: vendor tools (ApexSQL, Quest/LiteSpeed and similar) can sometimes rebuild schema+data from MDF/LOG — they work but cost money and are not a miracle. Test on copies only. (solutioncenter.apexsql.com)

If recovery fails, rebuild from application exports and harden your process: scheduled full + log backups, appropriate recovery model, DDL auditing or DDL triggers, and source control for schema. See Microsoft’s recovery‑model and backup guidance for recommended practices. (learn.microsoft.com)

Recommended Answers

All 2 Replies

Member Avatar for kvprajapati

You have a backup of db.

Member Avatar for Davidpoul

Its quite difficult to recover the deleted item manually. Try SQL recovery software to recover your deleted table. If you search on web you found lots of such software. But I recommend you to use Stellar Phoenix SQL Database Recovery Software or you download it from http://www.mssqldatabaserecovery.com/download-sql-database-recovery-software.php

David Poul

hi friends

i am a new bee to the world of sql server, started with it a few months back...
i deleted a table from sql server by mistake, and trying to recover it. but i dont hav any backup concerned. is it possible to recover that table without backup, and if yes, then how??
please revert ASAP..

thanx in advance
Aman

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.