Can we recover deleted SQL database?

by
Can we recover deleted SQL database

Recovering a deleted SQL database is often possible, but depends on several factors. With the right tools and techniques, data can frequently be restored, but speed is of the essence.

Can a deleted SQL database be recovered?

In most cases, yes, a deleted SQL database can be recovered, at least partially. When a database is deleted in SQL Server, the files (MDF and LDF) are not immediately erased from disk. Rather, the space they occupy is marked as available to be overwritten. As long as those database pages have not yet been overwritten, the original data can still be retrieved.

Several recovery methods are available to restore a deleted SQL database. These include:

  • Restoring from backups
  • Using database snapshot restore
  • Leveraging transaction log backups
  • Attaching database files
  • Using third-party tools

The recovery process will be easier if you have recent backups available. Without backups, more advanced techniques are required, but even then, experts can often recover most or all of the original data.

Factors affecting recovery chances

The likelihood of successfully recovering a deleted SQL database depends on several key factors:

  • Time elapsed – The less time that has passed since deletion, the better. As more data is written to the server, more original data will be overwritten.
  • Server activity – Higher server workloads increase the chance of overwritten pages. A lightly used server is best for recovery.
  • Drive configuration – Recovery is easier from traditional, spinning HDDs than SSDs, which overwrite data faster.
  • Backups – Recent backups offer the fastest, most reliable recovery path.
  • Transactions logs – Intact logs allow restoring to a specific point in time.

Ideally, you will be trying to recover the database on the same SQL Server instance where it originally resided. Backup files will match the original database and increase the changes of a successful recovery.

How to recover a deleted SQL Server database

Here are the steps to follow to restore a deleted SQL database:

  1. Stop all activity on the SQL Server to prevent further overwrites.
  2. Attach any available backup files to the SQL Server.
  3. Use the native RESTORE DATABASE command to restore from backups.
  4. If no backups are available, try restoring from snapshots.
  5. Scan storage using recovery software to find database files.
  6. Attach any discovered MDF and LDF files to SQL Server.
  7. If you cannot find all database files, return to your latest backup.
  8. Use advanced recovery software or consult an expert for fully deleted databases.

Let’s look at these steps in more detail:

1. Stop SQL Server activity

As soon as you realize the database is deleted, stop all reads and writes to the SQL Server instance. Ongoing activity increases the chance of data pages being overwritten. Take the server offline if possible to maximize recovery.

2. Attach available backups

Recent backups are the fastest route to restore a deleted database. Attach the latest .BAK file to the SQL Server using Transact-SQL commands like:

RESTORE DATABASE <DatabaseName> 
FROM DISK = '<PathToBAK>'

This will recover to the specific point-in-time captured in that backup.

3. Use native RESTORE

With no backups, the native RESTORE DATABASE command can still recover a database by scanning attached drives for files. For example:

RESTORE DATABASE <DatabaseName>
FROM DISK = '<PathToMDF>'
WITH RECOVERY

This method requires that the original MDF and LDF files are still intact and accessible.

4. Try database snapshot restore

If you had any database snapshots, they function like backups and allow restores. List and restore snapshots with:

RESTORE DATABASE <DatabaseName>  
FROM DATABASE_SNAPSHOT = '<SnapshotName>';

5. Scan drives for database files

Absent backups, scan the SQL Server’s drives and storage volume for the original MDF and LDF files using recovery software. The location depends on your configuration.

6. Attach discovered files

Once found, attach any orphaned database files to the SQL instance like so:

CREATE DATABASE <DatabaseName>  
ON (FILENAME = '<PathToMDF>'),  
(FILENAME = '<PathToLDF>')
FOR ATTACH

This mounts the database in the state it was in when deleted. Run DBCC CHECKDB to assess damage.

7. Restore from latest backup

If you could not locate all database files, return to your latest backup. You can partially restore the database and recover at least some data, even if backups are not current.

8. Engage an expert

For databases deleted long ago or with no backups at all, engage a SQL recovery expert. Specialty firms have advanced tools and techniques to recover even fully overwritten databases.

The right approach depends largely on your specific situation. But in most cases, starting from your most recent backup will provide the quickest and simplest recovery. Act fast, as your odds diminish over time.

Recovery using database backups

Restoring from a database backup is the easiest and most straightforward recovery method. Backups contain the database files and objects in a known usable state. Follow these general steps:

  1. Stop SQL Server activity to prevent further data loss.
  2. Locate your most recent full database backup (BAK file).
  3. Use T-SQL RESTORE DATABASE statements to restore the backup.
  4. Apply incremental and log backups as needed to reach desired point.
  5. Bring database online using RESTORE WITH RECOVERY.

Assuming you have an intact backup, you will be able to fully restore the database to the point in time captured in that backup file. All objects like tables, indexes, stored procedures, etc. will be restored.

Restore database backup only

At minimum, you can restore just your last full database backup. This will recover the database to that point in time. For example:

RESTORE DATABASE <DatabaseName>  
FROM DISK = 'C:\Backups\DBBackup.bak'
WITH RECOVERY

Apply differential and log backups

To restore to a time after your latest full backup, apply differential and/or transaction log backups in sequence. For example:

RESTORE DATABASE <DatabaseName> FROM DISK = 'C:\Backups\DBBackup.bak'
RESTORE DATABASE <DatabaseName> FROM DISK = 'C:\Backups\DBDiffBackup.bak'  
RESTORE LOG <DatabaseName> FROM DISK = 'C:\Backups\DBLogBackup.trn'

When finished, bring the database online with:

RESTORE DATABASE <DatabaseName> WITH RECOVERY

Using all available backups allows you to achieve a detailed, granular restore to a specific point in time before deletion.

Recovery using native tools

SQL Server includes native tools that can recover deleted databases even without backups, if certain conditions are met. These tools leverage the original files retained on disk.

RESTORE DATABASE

The T-SQL RESTORE DATABASE command can locate deleted data and log files still available on disk. For example:

RESTORE DATABASE <DatabaseName>   
FROM DISK = 'C:\SQLData\Data Files\DBDataFile.mdf',   
FROM DISK = 'C:\SQLData\LogFiles\DBLogFile.ldf'

This instructs SQL Server to search the specified paths for the original database files. If found, the database is restored by attaching those files.

Database Snapshots

Snapshots capture database state at a point in time, similar to backups but with minimal overhead. The SNAPSHOT RESTORE command rolls back to that point:

  
RESTORE DATABASE <DatabaseName>
FROM DATABASE_SNAPSHOT = 'SnapshotName';

This quickly restores the database to the state saved in the snapshot .

Using third-party tools

When native SQL Server options fall short, third-party tools offer more advanced recovery capabilities. Top options include:

Ontrack

Ontrack offers renowned SQL recovery tools like Ontrack PowerControls. Features include:

  • Deleted File Reader – rescues deleted database files.
  • Advanced Object Recovery – granularly restores individual objects.
  • Flexible Export – exports recovered data to other databases.

Stellar

Stellar SQL Database Repair rebuilds corrupt MDF files and restores lost data. Key highlights:

  • Extracts data from corrupt databases.
  • Repairs corrupted, deleted or damaged MDF files.
  • Exports recovered data to SQL Server.

EaseUS

EaseUS Data Recovery Wizard quickly locates residual database files for recovery. Features include:

  • Locates deleted MDF/LDF/NDF files.
  • Recovers data after accidental deletes or corruption.
  • Allows previewing found DB files before recovery.

Choosing advanced third-party tools can increase your chances of successfully restoring databases even in challenging situations beyond SQL Server’s native capabilities.

Preventing SQL database loss

While recovery is often possible, prevention is always the best approach. Use these database administration best practices to avoid deletions and data loss:

  • Enable regular SQL backups to provide restore points.
  • Use redundant backup destinations like disks and tape.
  • Retain multiple backup generations for flexibility.
  • Test backups periodically for reliability.
  • Restrict SQL access to authorized DBAs.
  • Enable database snapshots for point-in-time recovery.
  • Monitor SQL Server logs for dangerous operations.

Careful planning and processes will help avoid disaster. But even if a database is accidentally deleted, in most cases it can be recovered fully or partially using the techniques discussed here.

Conclusion

Recovering a deleted SQL database is very possible using built-in SQL Server tools and third-party software. The key is taking immediate action before original files are overwritten. Restore from backups first if available. Otherwise, restore database files from disk or employ advanced recovery tools. With the right approach, you can often retrieve even large deleted databases.