How to Recover Deleted SQL Server Database without Backup
Applicable to MS SQL Server 2017, 2016, 2014, 2012, 2008 and older versions
- Q 1. How do I restore a deleted SQL database?
- Q 2. Can I recover lost database in SQL Server without backup?
These are two top questions asked by most MS SQL Server administrators. It's a tremendous disaster for having no database backup when the SQL Administrator deleted or lost the SQL Server database and couldn't access SQL. Here Qiling software has collected two effective methods to help you restore lost or deleted SQL database without backup by using SQL recovery software or a manual way.
Recover Deleted SQL Database without Backup Using File Recovery and MS SQL Recovery Software
With the help of professional file recovery and SQL recovery software, the process of restoring lost SQL Server database or even resolving SQL database corruption errors will be easy and safe.
First. Scan and restore deleted database file
All you need is to search, test and find file recovery software for help. Deep Data Recovery supports fully scan your SQL Server hard drive on a local computer and restore the deleted database file - MDF or NDF files in 3 steps:
Step 1. Select a location to start finding data.
Step 2. Scan your partition where you save the SQL Server database file.
Step 3. Preview and recover the deleted database file.
Next. Restore database file back to SQL Server
After restoring the MDF or NDF SQL database files, you can import it back to your database to see if you can run the Server or not.
If yes, congratulations.
If not, don't worry. The found database file may have corrupted. Don't worry even if the MDF or NDF database file corrupted.
Professional Qiling MS SQL Recovery is able to assist you restoring deleted records including tables, queries, procedures and repair corrupted MDF or NDF database files with the deleted contents in just a few simple steps.
Plus: Qiling MS SQL Recovery full supports MS SQL Server 2017, 2016, 2014, 2012, 2008 and older versions
Step 1: Select the corrupted database for recovery
- Launch Qiling MS SQL Recovery.
- Select the corrupted database file by clicking "Browse" (the two dots) or "Search".
- After selecting the file, click the "Repair" button to start the Analyzing process.
Note: To use this software, you need to stop the SQL Server service.
Step 2: Repair the corrupted database
- The software displays all the recoverable items in a tree-like structure. The items are shown in a left pane.
- Select the desired component to be recovered. From the window, click the "Export" button.
Step 3: Export to the database or as scripts
- Choose to export the database objects to database or export the items as scripts
- If you choose "Export to database", enter the information required and choose the target database.
- A window appears up asking you to provide credentials to connect to the server and the destination to save the recovered items. In order to begin the repairing process, click "OK".
Note: Before clicking "OK", you need to restart the SQL Server service.
After restoring and repairing the database, you can save the database file as MDF/NDF back to the SQL Server. Then you should be able to access SQL database and run this powerful Server to work for your business again.
Manual Methods to Recover SQL Database without Backup
If you are experienced with SQL Server commands, you can try the commands listed below to restore the deleted SQL database:
- DBCC CHECKDB (DATABASE_NAME): Check the entire database for repair in command line or query.
- REPAIR_ALLOW_DATA_LOSS: Repair data by allocating and deallocating rows. (This command will remove damaged text, rows errors, page and column errors, which may result in data loss.)
- REPAIR_FAST: Repair the data in a fast process, but it makes only minor changes.
- DBCC CHECKTABLE (TABLE_NAME): Recover corrupted database table in command line or query manager.
Note: This manual way can only be performed within Microsoft SQL Server Management Studio.
You can run MS SQL Server Management Studio in your system and take the steps below to restore the deleted SQL database now:
Step 1. Go to the Toolbar, click on "New Query" button.
Step 2. On a new query page, enter the following SQL command:
EXEC sp_resetstatus [DATABASE_NAME];
ALTER DATABASE [DATABASE_NAME] SET EMERGENCY
DBCC CHECKDB ([DATABASE_NAME])
ALTER DATABASE [DATABASE_NAME] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC DATABASE ([DATABASE_NAME], REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE [DATABASE_NAME] SET MULTI_USER
Step 3. Click "Execute" to start the recovery process.
Wait patiently to let the process complete. The longer the process takes, the sever corruption exists in the database.
So if the process doesn't work out well, you'll still need to turn to an automated method or SQL repair tool for help.
Tip: Create Regular Backups of MS SQL Server Database
By taking the above methods, you can repair and restore the deleted SQL database. However, this is fairly not enough.
As a wise SQL administrator, you should always have plans of backing up the SQL database regularly. You can either choose a manual way or automated SQL Server backup software for help.
If you haven't decided to choose which SQL backup software for protecting your database, you can try Qiling Backup Advanced Server which supports to back up SQL database for free in both manual and automatic ways.
Related Articles
- How to Rebuild Master Database in SQL Server
- How to Fix A Corrupted SQL Server Transaction Log File
- [Solved] MS SQL Server Database Corruption Repair
- MS SQL Repair Tool | Repair Corrupted MDF/NDF File