Restoring a database from an MDF file

If you do not have a .bak file of the 4Matrix database to hand, it is possible to restore your database using the MDF file using SQL Server Management Studio.

Link: https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15

It is worth noting that this is not guaranteed to work and the main reasons are outlined below:

1) If the connection to the database was open when the MDF file was updated, it may not load when attempting to attach it in SQL Management Studio.

2) If the database/MDF was created using a newer version of SQL to the version you're trying to attach it to.

For point 2, above, there is a work-around.

To check which version of SQL was being used, login to SQL Management Stuido and run a 'New Query'.

In the query dialogue, insert the following code, replacing the text in red with the location of your own MDF file:

dbcc checkprimaryfile ('C:\Folder\My4mDatabase.mdf', 2)

This will return a table with a database version number, which can be cross-checked against the list of SQL versions here: https://sqlserverbuilds.blogspot.com/2014/01/sql-server-internal-database-versions.html

If, for example, the version of SQL for the MDF file is 2016 and you are trying to restore it onto version 2014, this will not work as it's not backwards compatible. You will need to install the matching or later version of SQL. The 4Matrix full installer for 4Matrix includes an option to download and install 2016 as part of the setup but if you would prefer to install SQL outside of the 4Matrix setup, or wish to use 2017 or 2019, you must choose 'mixed mode authentication' as part of the setup.

When working through the 4Matrix installation, you will need to install a new database and complete the wizard. Once this is complete, the MDF can be restored and overwrite the new database which was just created.

If you have any queries relating to the above, please log a ticket or call us on 020 3475 4463.

 

Article revised: 21/05/2021