Q 1. How to restore a deleted SQL database?
Q 2. Can I restore a lost database in SQL Server without a backup?
These are two of the most common questions that most MS SQL Server administrators would ask.
Not having a database backup is a huge disaster when the SQL administrator deleted or lost the SQL Server database and was unable to access SQL. Here SalvationDATA has found two effective methods that you can use to restore lost or deleted SQL databases without backup using SQL recovery software (automately)or manually.
Undelete SQL Database without Backup
——by using SalvationDATA Database Forensics System
DBR for SQL Server is an efficient and powerful SQL Server database repair tool that can recover corrupted, damaged or inaccessible SQL Server databases, including database tables, views, functions, stored procedures, and triggers. In addition to repairing the database, it can also scan and recover deleted data in the database.
First, Scan and restore deleted database file.
SalvationDATA DBR for SQL Server supports full scanning of your SQL Server hard drive on local computer and recovery of deleted database file – MDF or NDF files in 3 steps:
Step 1. Select a location to start searching for data.
Step 2. Scan your partition where you will save the SQL Server database file.
Step 3. Preview and restore the deleted database file.
Next, Restore the database file on 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 so, congratulations!
If not, don’t worry. The database file found may be damaged. Don’t worry even if the MDF or NDF database file is damaged.
Besides, there is another option if it doesn’t work with your case.
SalvationDATA DBF6300 as an overall function database tool specifically developed for database Experts can help you recover not only deleted records such as tables, queries, procedures but repair damaged MDF or NDF database files with the deleted contents in just a few simple steps.
Step 1: Select the damaged database to restore
Start SalvationDATA DBF6300.
Select the damaged database file by clicking “Browse” (the two dots) or “Find”.
After selecting the file, click the Repair button to start the analysis process.
Note: To use this software, you must stop the SQL Server service.
Step 2: Repair the damaged database
The software displays all recoverable items in a tree-like structure. The items are displayed in a left pane.
Select the component you want to restore. Click the “Export” button in the window.
Step 3: Export to the database or as scripts
Choose whether to export the database objects to the database or export the items as scripts
If you chose Export to Database, fill in the required information and select the target database.
After restoring and repairing the database, you can save the database file back to the SQL Server as MDF / NDF. Then you should be able to access the SQL database and run this powerful server to get your business back to work.
Manual methods to restore SQL database without backup
If you are experienced with SQL Server commands, you can try the commands below to restore the deleted SQL database:
- DBCC CHECKDB (DATABASE_NAME): Check the entire database for repair on the command line or query.
- REPAIR_ALLOW_DATA_LOSS: Repair data by assigning and releasing rows. (This command removes damaged text, line errors, page and column errors that can lead to data loss.)
- REPAIR_FAST: Repair the data in a quick process, but only minor changes are made.
- DBCC CHECKTABLE (TABLE_NAME): Recover a damaged database table from the command line or from the query manager.
Note: This manual way can only be carried out within Microsoft SQL Server Management Studio.
In addition, You can also run MS SQL Server Management Studio in your system and follow the steps below to restore the deleted SQL database:
Step 1. Go to the toolbar and click the “New Query” button.
Step 2. On a new query page, enter the following SQL command:
EXEC sp_resetstatus [DATABASE_NAME];
SET ALTER DATABASE [DATABASE_NAME] 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 “Run” to start the recovery process.
Wait patiently for the process to complete. The longer the process takes, the greater the damage to the server in the database.
So, if the process doesn’t work well, you still need to turn to an automated method or SQL repair tool for help.
Tip: Make regular backups of the MS SQL Server database
Using the above methods, you can repair and restore the deleted SQL database. However, this is pretty much not enough.
As a savvy SQL administrator, you should always plan to back up your SQL database regularly. You can choose either a manual route or automated SQL Server backup software for help.
If you haven’t chosen SQL backup software to protect your database, you can try SalvationDATA DBF6300 for overall functions, which supports manual and automatic backup of SQL databases. Or, apply for a free download with DBR for SQLite first!