How do i open an old sql express database file?

Question

I have an old (10+ years) database, I paid to have built. I would like to use again after not using it for almost as long, but I lost contact with the person who wrote it. When I try to open it in Notepad, some of what is readable says, "

M e s s e n g e r D B C : \ P r o g r a m   F i l e s \ M i c r o s o f t   S Q L   S e r v e r \ M S S Q L 1 0 . S Q L E X P R E S S \ M S S Q L \ D A T A \ M a y 0 9 . M D F SFIN¼                                *      ž                                      
                    à       àÿÿÿ    *                                                    ûP_'w{¯M£Ç¼Ø®tp†¿     ¯       ¿  X   
       ¿     ¯                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             M e s s e n g e r D B _ l o g C : \ P r o g r a m   F i l e s \ M i c r o s o f t   S Q L   S e r v e r \ M S S Q L 1 0 . S Q L E X P R E S S \ M S S Q L \ D A T A \ M a y 0 9 _ 1 . L D F 

How do I open it using MSSMS? I don't know what database engine to use or the authentication type. I can't connect to anything and don't understand how to work through the error messages.

Answer 1

Hi @Koogie Buffalo ,

I did a test for you.

On the first server, I created the database named T2 and created a table then inserted two records. Then I copied the .mdf file of this database to another server to simulate your situation.

On the second server, the following T-SQL statement creates a database named MyDatabase and attach the data file from first server to here.

CREATE DATABASE MyDatabase
ON (FILENAME = 'C:\Users\Administrator.TESTDOMAIN\Desktop\T2.mdf')
FOR ATTACH;

This is because you didn't have a log file to begin with, so it created itself.

Refresh the database, you will see the tables and two rows i inserted before.

Best regards,

Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

Try to attach you .mdf file from SSMS to see whether you can attach your old database.

Answer 3

Hi Koogie,

Welcome to Q&A Forum, this is a great place to get support, answers and tips.

Thank you for posting your query, I'll be more than glad to help you out.

What type of files do you have from that database?

As the others already stated... if you have only the mdf-file, then try to attach that file to a SQL Server installation...

https://www.mssqltips.com/sqlservertip/1894/attach-sql-server-database-without-log-file/

if you have both mdf AND ldf then follow the same instruction without the "Remove"...

If you have a file with an ending *.bak then it might be a backup file and you have to restore that file to a SQL Server.

As you didn't mentioned something about it... you need - of cause - a SQL Server installation to try above steps... on an existing SQL Server, on a test machine where you can install a SQL Server or even on your desktop/laptop...

for example... for this kind of activities you might want to use a SQL Server express edition...

https://www.microsoft.com/en-us/download/details.aspx?id=101064

I hope my answer is helpful to you,

Your

Bjoern Peters

If the reply was helpful, please upvote and/or accept it as an answer, as this helps others in the community with similar questions. Thanks!

Answer 4

If SSMS is unable to attach the database, you may need to troubleshoot the error messages that you are seeing. Some common issues include:

• Incorrect authentication type or login credentials: Make sure that you are using the correct authentication type and that the login credentials are valid.
• File permission issues: Ensure that the SQL Server service account has permission to access the database files.
• Database corruption: If the database files are corrupted, you may need to restore from a backup or attempt to repair the database using DBCC CHECKDB.

If you are unable to resolve the issue, you may need to seek the help of a SQL Server professional or a database administrator.