How to Recover SQL Server Database without Backup?

Written By Jyoti Prakash
Approved By Kuljeet Singh
Updated on 3 Aug, 2022
Min Reading 6 Min
Share

Table of Content

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.

Free Download100% secure

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.

stellar repair for sql file saving options

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.

recovered sql file saved at desired location

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.

Was this article helpful?

YES0

About The Author

Jyoti Prakash

Problem solver and Data recovery specialist. Usually share informative articles on data recovery, database corruption and ways to recover lost data.

25 comments

Benjamin says:

September 27, 2017 at 3:42 am

A recent virus attack had severely damaged my file to the extent that it even went into suspect mode, although I was able to recover about 70% of data by running DBCC CHECKDB (dbname, REPAIR_ALLOW_DATA_LOSS) in emergency mode, I was not able to recover the remaining data since the the virus had even damaged my .BAK file.
I am glad that I stumbled upon this article because after repairing my MDF file using Stellar SQL database repair my database is back to normal functioning state.

Reply
1. Priyanka Chauhan says:
  
  September 27, 2017 at 4:26 am
  
  Hello Benjamin,
  
  Thank you so much for your feedback.
  It’s good to know that, our SQL recovery software fixed the issue.
  
  Keep reading.
  
  Reply
decaptcher says:

June 29, 2017 at 1:34 am

I woulԁ like to thɑnk you for the effoгts you have put in writing this bⅼog.

In fact, уour creative writing abilities haѕ encouragеd me to get my own websіte now.

Reply
1. Jyoti Prakash says:
  
  June 29, 2017 at 6:08 am
  
  Hello decaptcher,
  
  Thanks for your appreciation. keep reading!
  
  Reply
Kim Grantham says:

March 6, 2017 at 10:50 am

I have an old backup, and many transactions were missing from it. In this weird situation, the software was only the last option to repair the corrupt database. I purchased the license of this tool and repaired my database.

Reply
Liam says:

February 7, 2017 at 8:11 am

I have deleted records of from my table. I performed ROLLBACK, but data place is still blank. I am very thankful of this software because it has a fantastic feature to recover the deleted records.

Reply
Nirvan says:

January 31, 2017 at 8:38 am

We had the corruption in the company’s database, but the backup was not updated. I am very thankful to you to provide the solution to this problem.

Reply
Pratha says:

January 23, 2017 at 6:03 am

Hello,

I have the tape drive, and I want to define a logical backup device to a tape drive. What are the limitations of this process?

Reply
1. Jyoti Prakash says:
  
  January 23, 2017 at 6:54 am
  
  Pratha,
  
  To do this process, you need to consider these points:
  
  – The drive must be supported by the Windows OS
  – The drive must be physically connected to the system that has running SQL Server instance. The remote tape drive doesn’t support the OS.
  
  Reply
Zachary Wilson says:

January 9, 2017 at 8:57 am

I am working on a production database. We take the backup of our database at the end of the day. We faced the unexpected reboot of the server and database was into the suspect mode.

As we do not have the updated backup so, my team decided to try the luck with Stellar Repair for MS SQL tool.

I am impressed with the article because you have mentioned each step very clearly. I downloaded the tool and saved the database by Live Database option.

Reply
Jyoti Prakash says:

December 23, 2016 at 10:12 am

Hi Joel,

As you do not have updated backup and log backup. In this case, download Stellar Repair for MS SQL tool and fix the problem.

Reply
Joel Smith says:

December 23, 2016 at 5:14 am

What would I do to recover a database if my backup is 3 months old? No log backup of earlier time only current log backup is there.

Reply
Jaxon says:

December 20, 2016 at 5:39 am

I have only transactional log backup. What should I do?

Reply
1. Jyoti Prakash says:
  
  December 21, 2016 at 8:19 am
  
  Hi Jaxon,
  
  Transactional log backup consists all the log backup records generated since the most recent backup occurred. You cannot perform the log backup until a full backup has performed. In this case, the log backup and the full backup doesn’t interact or effect each other.
  
  Reply