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 the 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 EaseUS software has collected two effective methods to help you restore lost or deleted SQL databases 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 databases or even resolving SQL database corruption errors will be easy and safe.
First. Scan and restore the deleted database file
All you need is to search, test and find file recovery software for help. EaseUS Data Recovery Wizard supports fully scanning your SQL Server hard drive on a local computer and restoring 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 them back into 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 been corrupted. Don't worry even if the MDF or NDF database file is corrupted.
Professional EaseUS MS SQL Recovery is able to assist you in restoring deleted records including tables, queries, and procedures and repair corrupted MDF or NDF database files with the deleted contents in just a few simple steps.
Plus: EaseUS MS SQL Recovery fully supports MS SQL Server 2017, 2016, 2014, 2012, 2008, and older versions
Step 1: Select the corrupted database for recovery
- Launch EaseUS 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 the 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 the command line or query.
- REPAIR_ALLOW_DATA_LOSS: Repair data by allocating and deallocating rows. (This command will remove damaged text, row errors, and 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 the corrupted database table in the 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, and click on the "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 more severe 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 EaseUS Todo Backup Advanced Server which supports backing up SQL databases for free in both manual and automatic ways.
Was This Page Helpful?
Related Articles
-
How to Export Data from SQL Server to Excel Automatically
Cedric/2023-07-20
-
SQL Error 233 No Process Is on the Other End of the Pipe
Brithny/2024-11-27
-
MS SQL Repair Tool | Repair Corrupted MDF/NDF File
Cedric/2024-11-27
-
[Fixed] SQL Error 945 | Database Cannot Be Opened due to Inaccessible Files
Cedric/2024-11-27
EaseUS MS SQL Recovery
- Restore (.mdf and.ndf) Files
- Recover Corrupted Objects
- Fix Database Log Files