SSMS connects to a live instance and never to an MDF file directly. Attach adds the file to that instance. Four methods work and they are the SSMS Attach dialog, T-SQL attach, detach and attach for a move and recovery software. Three of them need every data file and a server on an equal or newer release. Recovery software exports a damaged file into a live SQL Server when the attach fails. Test every method on copies of the files.
Most readers want SSMS to show an MDF file. SSMS only talks to an instance so the real task is an attach.
Failed attaches trace back to a file from an older server, a missing NDF file or an access error. This guide explains the fixes in order of effort, the errors that stop the attach and the settings that need a check afterward.
Microsoft lists attach as the way to copy, move or upgrade a database. Four methods below use it in different ways.
Why Does SSMS Need an Attach for the Database File?
SQL Server Management Studio is a client tool that connects to a Database Engine instance and shows its databases. MDF files appear in Object Explorer only after the instance attaches them, as Microsoft explains in attach a database.
| File | Needed for the Attach | Microsoft Rule |
|---|---|---|
| MDF | Always | Every data file must be available |
| NDF | When the database has secondary files | Every data file must be available |
| LDF | Normally | A single missing log can be rebuilt after a clean shutdown |
Microsoft states that sp_attach_db and sp_attach_single_file_db still work but will go away in a future version. CREATE DATABASE with FOR ATTACH is the recommended replacement.
What to Check Before You Connect the MDF File?
Confirm six things before the attach:
- File List. Open Database Properties and the Files page on the source server to note every file and path. T-SQL gives the same list from sys.database_files.
- Version Path. Microsoft states that only files from the release of the server or an older release can attach.
- Service Account. The Database Engine account needs access to the folder with the files.
- Rights. You need one of three database creation rights that Microsoft lists for an attach.
- Encryption Key. An encrypted database needs its database master key opened by the owner on the first attach according to Microsoft.
- Trusted Source. Microsoft advises a DBCC CHECKDB test on a nonproduction server for files from unknown sources.
Copy the data and log files before you start. Attach upgrades the database and Microsoft documents no downgrade path.
4 Methods to Connect an MDF File to SSMS
Four methods exist and only the last one needs paid software. Method #4 fits files that refuse every attach.
| Feature | Attach Dialog | T-SQL Attach | Detach and Attach | Recovery Software |
|---|---|---|---|---|
| Cost | Included in SSMS | Included in SSMS | Included in SSMS | Demo free and licensed export |
| Best for | One file on a server you manage | Scripts and several data files | Planned move to another server | Damaged files and failed attaches |
| Needs | Every data file | Every data file | Rights on both servers | Windows PC and file copies |
| Biggest risk | Wrong file path | Typing error in a path | Skipped copy of a file | Demo export limit |
Method #1: Connect the MDF File With the SSMS Attach Dialog (Free)
Use the dialog for one database on a server that you manage. Microsoft states that the grid fills with the other details after you select the MDF file.
- Connect Object Explorer to the instance and expand it.
- Start Attach from the right click menu of Databases.
- Use Add in the Attach Databases window to choose the MDF file.
- Type a new name in Attach As when you need a different database name.
- Compare every path in the file grid with the folders on disk.
- Confirm with OK and look for the new entry under Databases.
Note. The Message column shows Not found beside a missing file. Fix the path of a missing NDF file. Missing log files need a corrected path or a removed entry according to Microsoft.
Method #2: Script the Attach With T-SQL (Free, for Several Files)
Scripts list every file in one statement and work the same on any server. Microsoft requires every MDF and NDF file in the statement.
CREATE DATABASE RetailDB
ON (FILENAME = N'D:\SQLData\RetailDB.mdf'),
(FILENAME = N'D:\SQLData\RetailDB_2.ndf'),
(FILENAME = N'D:\SQLData\RetailDB_log.ldf')
FOR ATTACH;Verify the files after the attach with the statement below.
USE RetailDB;
EXEC sp_helpfile;Microsoft documents the RESTRICTED_USER option for FOR ATTACH. RESTRICTED_USER limits access to members of db_owner, dbcreator and sysadmin during your checks.
Method #3: Detach and Attach to Move the Database (Free, Planned Moves)
Detach removes a database from an instance while its files remain on disk as Microsoft documents in detach a database. Microsoft names the move to a server on an equal or higher release as a main use.
- List the files with the statement below and note every path.
- Detach in SSMS through Tasks and Detach or call sp_detach_db.
- Tick Drop Connections in the Detach Database window when sessions exist.
- Copy every file to the new server.
- Attach the files with the new paths and recreate logins and Agent tasks.
USE RetailDB;
SELECT type_desc, name, physical_name
FROM sys.database_files;EXEC sp_detach_db @dbname = N'RetailDB';Microsoft states that replicated databases and databases with active connections cannot detach. Snapshots, availability groups, mirroring, suspect state and system databases also stop a detach.
Method #4: Recover and Export the MDF File With EmailDoctor (Free Preview, Licensed Export)
Damaged files and files that stop every attach need a different route. Software like EmailDoctor SQL Database Recovery opens the damaged primary file and its secondary files. Every object appears in a preview before you export anything.
According to the vendor the demo exports just 25 records from a table and 5 other objects while the preview shows every component. Exports go to a SQL Server that you can reach. Work on copies of the files.
Scan the MDF and NDF Files
- Launch the recovery software on your Windows machine.
- Press the Open icon and locate your RetailDB.mdf copy.
- Select Quick Scan as the first mode. The Auto detect box detects the file version automatically and a cleared box opens a manual version list.
- Open NDF Options and add every secondary file of the database.
- Confirm the scan report and then the note about Quick Scan.
- Select a table in the tree to preview its recovered rows.
Export the Recovered Data to SQL Server
- Use the Export menu to send the data to a live server with the Export As SQL Server Database item.
- Set the destination server and the authentication mode.
- Click Create New Database and enter RetailDB_Recovered in the name box.
- Tick the components to export and choose With Schema & Data.
- Start the transfer with Export and watch for the success message.
- Refresh SSMS and browse RetailDB_Recovered to confirm the tables.
Red entries in the preview mark deleted objects. Heavy damage needs the Advanced Scan mode that the corrupt MDF repair steps explain.
What Happens After You Attach the MDF File?
Microsoft lists five effects of an attach on a database from an older server:
- Version Upgrade. The new instance converts the database to its own internal version.
- Compatibility Level. The level remains the same unless the instance no longer accepts it. Level 90 on SQL Server 2019 becomes level 100 as Microsoft states.
- CDC Upgrade. Databases from SQL Server 2014 or earlier with CDC enabled need sys.sp_cdc_vupgrade.
- Reset Options. Cross database ownership chaining is off and the broker and trustworthy options default to OFF.
- Missing Logins. Logins and SQL Server Agent tasks of the old server need a new setup on the destination server.
Check the level with the statement below.
SELECT name, compatibility_level
FROM sys.databases
WHERE name = N'RetailDB';What if the MDF File Does Not Connect?
Five errors explain most failed attaches:
| Error | Meaning | Fix |
|---|---|---|
| 5120 | SQL Server cannot open the physical file | Give the service account access to the folder as described for error 5120 during an attach |
| 1813 | CREATE DATABASE could not open the new database | Follow the repair steps for SQL Server error 1813 |
| 948 | The database release is newer than the server | Move the file to a server on a matching or later release |
| 15105 | Windows message text is missing | Decode the real error with the error 15105 guide |
| Not found | A file path in the grid is wrong | Correct the path or remove the log entry |
Microsoft describes error 948 in its error 948 reference as a database version that the server does not support with no downgrade path. Open the database on the original version and disable the newer features first.
Frequently Asked Questions
Can I Attach the Database Without the LDF File?
Yes in many cases. Microsoft states that a single missing log can be rebuilt when the database shut down cleanly. Databases with a primary file that cannot take updates need their log files at the recorded location.
Does SSMS Open an MDF File Without an Attach?
No. SSMS needs an instance that attaches the file first. Attach adds the file and the database then appears in Object Explorer.
Can I Attach an MDF File With NDF Files?
Yes. Microsoft requires every MDF and NDF file to be available. Add all of them in the Attach window or in the FOR ATTACH statement.
Can I Attach an MDF File From SQL Server 2008 on SQL Server 2019?
Yes. During the attach SQL Server converts the database to match the new server. Attach on an older server fails because a newer file cannot go backward.
Does an Older Server Accept the MDF File?
No. Microsoft does not allow an attach on an earlier version. Error 948 reports the missing downgrade path.
Does Detach Delete the MDF File?
No. Microsoft states that detach removes the database from the instance while the files remain on disk. Copy the files before you move them.
Do I Need to Detach a Database Before I Copy Its Files?
Yes. Microsoft states that a database moved from another instance must detach first or the attach returns an error. Move the files to the other instance after the detach.
Can the Attach Fail Because of Permissions?
Yes. You need CREATE DATABASE or a similar right and the Database Engine account needs access to the folder. Error 5120 reports the access failure.
Can SSMS Attach a Corrupt MDF File?
Rarely. Expect an attach error or a suspect database. Recovery software scans the damaged file and exports its objects to a new database.
Is Every Record Exported in the Demo?
No. Per the vendor the demo stops at 25 records of a table and 5 other objects. Licensing removes the limit.
Is a Backup Better Than Detach and Attach?
Yes. Microsoft states that a detached file set has no log backups and no recovery to a point in time. Use backups for recovery work.
Author’s Verdict
SSMS connects to an instance and the instance attaches the MDF file. Collect every data file and check the version before you click Attach.
Use the dialog for one database and T-SQL for scripts or several files. Detach and attach suits a planned move to another server.
Software for damaged files saves the objects of a failed MDF file in a fresh database. Backups remain the safer route for recovery work.
- SSMS connects to an instance and the instance attaches the MDF file.
- Every MDF and NDF file must be available and the server needs the same or a newer version.
- Detach and attach moves a database to another server and the attach upgrades it.
- Recovery software moves the objects of a damaged file into a fresh database.
- Copy the files before you start.
Next Read: Open an MDF File With or Without SQL Server
Also Read: Fix SQL Database Error 1813 | Repair Corrupt SQL MDF Files
