Tips and Tricks to Recover SQL Server Database with No Backup

Siddharth Rawat

Updated On - October 18, 2022

Structured Query Language or SQL is used to communicate specifically with relational databases. Using SQL statements, it is possible to perform complex tasks such as update or retrieve a database. Some of the most popular relational database management systems that use SQL are Oracle, Access, Sybase, Ingres, Microsoft SQL Server, and more. These databases contain data or information which is stored in tables. For various reasons, SQL databases can go corrupt. In such situations, an earlier created database backup can save you a lot of time and effort. Hence administrators always recommend backing up databases on a regular basis. Lost or deleted databases can be recovered and restored in a jiffy if you have a backup. But what if you have no SQL database backups? In this post, we are going to address this issue in detail. There are a few manual commands that can be tried in this case, but if the file corruption is severe, then this task should be better left to a professional third-party tool. So, without further ado, let’s just dive right in.

Some Useful SQL Commands for Database Recovery
Following are some of the essential commands that can be used to perform the SQL database recovery process:

• DBCC CHECKDB: This command checks the physical and logical integrity of all the objects in the specified database by performing the following operations:

I. DBCC CHECKALLOC
II. DBCC CHECKTABLE
III. DBCC CHECKCATALOG
IV. Validation of contents of every indexed view in the database
V. Validation of link-level consistency between table metadata and filesystem directories
VI. Validation of Service Broker data

Note: This implies that DBCC CHECKALLOC, DBCC CHECKTABLE, or DBCC CHECKCATALOG commands do not have to be executed separately from DBCC CHECKDB.

• DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS:

Using the REPAIR_ALLOW_DATA_LOSS option may result in some data loss. It might result in more data loss than anticipated but is worth a try if all the other options fail.

Before that, the database should be set to the single-user mode as follows:


Next, run CHECKDB with REPAIR_ALLOW_DATA_LOSS as follows:


Note: In the above SQL query, “Testmachine2” is the name of the affected database, replace this with your affected database’s name.

• DBCC CHECKDB with REPAIR_FAST: It does a minor but fast repair of the database but without the risk of data loss.



Note: Make sure that the database is set in a single user mode before running this fix.

Recoveryfix for SQL Database Recovery
It can be concluded that the previous inbuilt database recovery options are of not much use as these involves data loss or performs only a minor repair on the database. Hence the SQL database recovery should be left to the professionals or professional tools. One such professional SQL database repair tool is Recoveryfix for SQL Database Recovery. It is a reliable recovery tool that can deal with any level of MDF file corruption. It can efficiently recover all SQL database objects as it is embedded with advanced algorithms. You need not worry about the properties of all SQL database objects after recovery as it maintains the data integrity. Moreover, the tool allows the user to preview the recovered SQL database items prior to saving.

Let us now go through the detailed tool process:

• Select the MDF database file using the Browse button and click Next.



• Next, a popup will show you the progress of the repair process.



• The recovered database files can be found in the left pane of the interface. Select all the database objects that you would like to save and click Next.



• One can choose to save the retrieved database objects either to a live SQL instance or to a disk drive. If you have selected the latter option, then click on the browse button to provide a destination path and click Save.



• The progress bar shows the status of the saving process.



• A new popup window will show you the summary of the saved database items. Click OK to proceed.



• Click on Finish to exit the tool.

Conclusion
We have discussed some helpful commands that can be executed for SQL database recovery when there is no backup, and the actual database file is corrupted. Running these T-SQL commands involves high risk of data loss, hence are not recommended. In case of severe SQL database corruption, we highly recommend Recoveryfix for SQL Database Recovery.