Recovering Data From a Database

Recovering Data From a Database
Photo by Jan Antonin Kolar / Unsplash

I recently had a client that had a server fail and did not have a backup of the database for program that needed recovered. Luckily, they were able to get the files off of the old hard drive.

I was provided the "database.mdf" file and was determined to get the information we needed from it. I searched around the internet for tools I could use to extract the data from this database file and came across multiple versions of essentially the same tool. The problem was they were all "freemium" style pieces of software, each one had a trial mode that allowed me to view the data but not copy it. I decided to not try and purchase one of these programs as the websites to purchase them from seemed somewhat untrustworthy. I knew there had to be a better way.


Using SQL Management Studio

After digging around and a little trial and error, I was able to find the right SQL commands to get the job done. The tool I used was Microsoft SQL Server Management Studio.

The first step is to download Microsoft SQL Server Express and follow the basic installation instructions. Next you will need to install SQL Server Management Studio. After both of these programs are installed you will need to get the database file into SQL Server. This is what worked for me.

First open your Windows Services panel and look for the SQL Server service.

Now stop the SQL Server service.

Next you will need to open file explorer and navigate to where the database files are stored.

"C:\Program Files\Microsoft SQL Server\MSSQL16.SQLEXPRESS\MSSQL\DATA"

Copy your "database.mdf" file into this folder. After that is done go back to your services and start the SQL Server service again.

Now you will want to open the SQL Server Management Studio program. Connect to the server using Windows authentication and you should see your servers name in the Object Explorer.

You should now see the database listed with the "Recovery Pending" marked on it.

Next we will run some queries to be able to open the damaged database.

First let's set the database in "Emergency" mode.

Click on "New Query" and then execute the following query on your database.

ALTER DATABASE <database_name> SET EMERGENCY, MULTI_USER

Right click on the database name in the Object Explorer and click refresh, you should now see the database set in "Emergency" mode.

Next we are going to rebuild the log file for the database. Open a new query and run.

ALTER DATABASE <database_name> REBUILD LOG ON (NAME= logicalname, FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL16.SQLEXPRESS\MSSQL\DATA\<logfilename>.ldf')

If successful you should get "Warning: The log for database <database_name> has been rebuilt...."

The database is now labeled as being in "Restricted User" mode.

Next you will need to set the database online so you can open it. Open a new query and run.

ALTER DATABASE <database_name> SET ONLINE;

You may see it list multiple upgrade versions.

Database 'infinias' running the upgrade step from version 944 to version 945.
Database 'infinias' running the upgrade step from version 945 to version 946.
Database 'infinias' running the upgrade step from version 946 to version 947.
Database 'infinias' running the upgrade step from version 947 to version 948.
Database 'infinias' running the upgrade step from version 948 to version 949.
Database 'infinias' running the upgrade step from version 949 to version 950.
Database 'infinias' running the upgrade step from version 950 to version 951.
Database 'infinias' running the upgrade step from version 951 to version 952.
Database 'infinias' running the upgrade step from version 952 to version 953.
Database 'infinias' running the upgrade step from version 953 to version 954.
Database 'infinias' running the upgrade step from version 954 to version 955.
Database 'infinias' running the upgrade step from version 955 to version 956.
Database 'infinias' running the upgrade step from version 956 to version 957.

Completion time: 2022-12-14T13:10:50.0514766-05:00

Now you should be able to expand the database files and see the tables and other data in the database.


Conclusion

This was mostly trial and error for me as I'm not a database expert, but in the end I was able to retrieve the data I was needing from a damaged database. You may end up having to add more steps to your process to make it work for what you need. Hopefully this may help someone out there trying to get the information they need.