Summary: The blog describes the manual and software methods to recover SQL Server Database without backup. You can manually repair the database by running the ‘DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS’ command with some data loss. For quick and risk-free database recovery, consider using a specialized SQL database recovery software, such as Stellar Repair for MS SQL.
Data loss can occur anytime due to several known and unknown reasons. Many tables and other components of the database can turn unreadable without any prior notification or alerts. And a recent, good known backup copy of the SQL Server Database can help restore the deleted and lost database back to its original state. That is why administrators always recommend backing up databases regularly.
If you do not have an updated backup or if the backup is corrupt, it can be difficult to recover SQL Server database and its deleted or inaccessible data.
Also Read: How to Recover SQL Server Database from a Corrupt Backup File?
Methods to Recover SQL Server Database Data Without Backup
Manual Method
Note: This method will only work if the database you want to restore is still attached to the SQL Server instance. If your database is offline, you cannot execute the DBCC CHECKDB command. In that case, the only option is to use a specialized SQL recovery tool.
If you do not have backup of your SQL database, you can try repairing it by running the ‘DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS’ command.
| Caution! Running DBCC CHECKDB does not guarantee bringing the databases to a consistent state. Also, using the ‘REPAIR_ALLOW_DATA_LOSS’ option can result in data loss. |
Steps to Repair Corrupt SQL Database by Using DBCC CHECKDB
- Launch SQL Server Management Studio (SSMS), and connect to an instance of SQL Server instance.
- Put the database to EMERGENCY mode.
- Run DBCC CHECKDB on the database to check for corruption errors.
Note: If DBCC CHECKDB reports any errors, it will recommend minimal repair options to fix the errors.
- Bring the database into single-user mode and run DBCC CHECKDB with the REPAIR_ALLOW_DATA_LOSS command. If it is successful, your database will be repaired with some data loss.
For detailed steps on repairing the SQL database using the DBCC CHECKDB Command, refer to this link.
Software Method
Unlike the ‘DBCC CHECKDB’ repair command, Stellar Repair for MS SQL software does not involve data loss risk. The software is purpose-built to help users repair corrupt SQL database (MDF and NDF) files without backup, preserving the original file data. Once repaired, the file can be used to restore the database to its original, intact form.
The SQL database recovery tool can also handle all types of database corruption errors, such as database consistency errors, database in suspect mode, recovery pending mode, etc.
Also Read: How to Recover MS SQL Database from Suspect Mode?
The software can help recover unreadable database objects from corrupt SQL database MDF and NDF files.
Steps to Recover SQL Database with Stellar Repair for MS SQL Software
|
Points to Consider Before Using the Software: 1. Back up the SQL Server database you want to repair to prevent further damage. Also, ensure that the repair process is executed on the backup file and not the original database file. 2. Application auto-checks the SQL Server version: however, if the file is severely corrupted, you can select the SQL Server edition manually under the “Select Database Versions” section. If the selected version of the Server database file is invalid, the tool will display an “Invalid database version, Select the appropriate database version” message. |
Step 1: Download, install and run Stellar Repair for MS SQL software.
Step 2: In the software main interface window, click Browse to select the database file you want to recover.
Note: If you do not know your db file’s location, click ‘Search’ to find and select the file.
Figure 1 – Select Database Window
Step 3: Once the file is selected, select Repair to initiate the repair and recovery process.
Figure 2 – Repair the SQL Database
Step 4: When the repair process is completed, the software will display all the recoverable items, including Tables, Defaults, Views, Data Types, Synonyms, etc., in a tree-like structure in the left pane. Click on an individual object to view its content in the right pane.
Figure 3 – Stellar Repair for MS SQL Preview Window
Note: The checkboxes associated with each database object are selected by default in the Preview window. However, you can choose to save all or specific db objects.
Step 5: To save the database file, click Save on the File menu or click on the Save button on Quick Access Toolbar to restore the SQL server database without backup file.
Figure 4 – Saving Option
Step 6: In the Save Database window that appears, select the MDF option under Save As section.
Figure 5 – Stellar Repair for MS SQL Software File Saving Options
Step 7: Next, under Saving Option, chose any of these options:
- New Database: Select this option, if you want to save the recovered data to a new database
- Live Database: Select this option, if you want to save the recovered data to the existing database
To save the recovered objects to the New Database, follow the below steps:
- Click the New Database radio button.
- Provide Server/Instance Name, and then select a destination path for saving the recovered SQL database objects.
- Click Save to save the repaired MS SQL database file.
Figure 6 – Saving Database File to New Database
4. After the process is completed successfully, a message will appear stating ‘File saved at the desired path’. Click OK to proceed further.
Figure 7 – Save Complete Message
5. The software will save the database as New Database. See the image below:
Figure 8 – SQL Database File Saved at Desired Location
To save the recovered objects to the Live Database, follow the steps below:
- Click on Live Database radio button.
- Provide Server/Instance Name.
- Next, select a file from the Existing Database drop-down list into which you want to save the recovered database objects.
Figure 9 – Save Database File to Live Database
- Choose Save for saving the repaired db file.
- Once the repair process is completed, you will receive a “File saved at the desired path” message. Click OK.
Figure 10 – Save Complete Message Box
6. The software will save the database as a Live Database. See the image below:
Figure 11 – SQL Database File Saved to Live Database
Read what Microsoft MVPs say about the software.
Key Benefits of the Software
- Batch Repair Multiple Database Tables at Once: SQL Server Database Recovery software allows saving multiple database tables simultaneously. This saves time and effort, and the entire recovery process is accomplished seamlessly.
- Multiple File Saving Options: This allows saving the repaired objects into multiple file formats. Therefore, after recovery, the database files can be accessed using independent applications. When stored in HTML, CSV, and XLS file formats, the data can be previewed without connecting to the SQL Server.
- Generates Log Report: The tool will generate a log report of the repair process. This log report contains information about every single procedure being performed on the database. The log report can be stored for future reference.
End Note
In the event of SQL Server crash, disk failure, database corruption, or accidental data deletion, you can rely on the backup to bring the database to a consistent state and recover its data. However, you may come across situations when the backup is not available or corrupted. However, implementing the methods described in this post can prepare you to respond quickly and efficiently to any disaster. You can try repairing the database manually or use SQL file recovery software.
