SQL Server restores primary data files in two ways. Attach adds an existing MDF file to an instance and RESTORE DATABASE rebuilds the file from a backup. Four methods exist and they are the SSMS attach, the SSMS restore dialog, T-SQL restore with MOVE and recovery software. Methods one to three need a healthy file or a usable backup. When no backup exists recovery software exports the damaged file to a new database. Test every method on a copy first.
Most readers who search for this have either a loose MDF file or a backup file and no clear path to a working database. Both cases need different commands.
I wrote this guide to separate the two cases and to add a route for damaged ones. I ordered the methods by effort. Stay tuned for the rules that stop file level restores.
Microsoft describes RESTORE FILELISTONLY as the command that lists the database and log files inside a backup set. Method #3 below uses that fact to find the logical names.
What Does a Primary File Restore Mean?
Every database has one primary data file. Microsoft recommends the .mdf extension for it. Startup details and pointers to the other files live in that file as the database files and filegroups reference explains.
Restoring that file means two different actions in practice. Attach adds files that already exist and RESTORE builds the files again from a backup.
| You Have | Best Method |
|---|---|
| MDF and LDF files only | Method #1 |
| A full backup file | Method #2 or Method #3 |
| A backup and new folder paths | Method #3 |
| A damaged MDF file and no backup | Method #4 |
Which Checks Come Before the Restore?
Check five items before you start:
- Source. Files and backups from unknown sources need a nonproduction server and a DBCC CHECKDB test first, as Microsoft advises.
- Copies. Work on copies of your primary data file and the backup file.
- Rights. A new database needs CREATE DATABASE permission. Existing databases need sysadmin, dbcreator or dbo rights according to Microsoft.
- Free Space. The destination disk needs room for the data and log files.
- Encryption. An encrypted database needs the certificate or key that protected it.
Microsoft states that REPLACE deletes an existing database with the same name. Restore under a new name when you want a safe copy.
4 Methods to Restore a Primary Data File
Methods #1 to #3 cost nothing and need a healthy file or backup. Method #4 uses recovery software and fits damaged files.
| Feature | SSMS Attach | SSMS Restore | T-SQL Restore | Recovery Software |
|---|---|---|---|---|
| Cost | Free in SSMS | Free in SSMS | Free in SSMS | Demo scan and licensed export |
| Source | MDF and LDF files | Backup file | Backup file | Damaged MDF file |
| Best for | Orphan files | One time restores | Scripts and new paths | No backup available |
| Main limit | Needs a healthy file | Needs a good backup | Typing errors in paths | Demo export limit |
Method #1: Attach the MDF File in SSMS (Free)
Use attach when you own the MDF file and its log. Dialog and script steps appear in the MDF attach guide.
- Choose Attach from the Databases context menu in Object Explorer.
- Press Add in the attach window and browse to the MDF file.
- Check the file paths in the lower grid and fix any entry that shows Not found.
- Click OK and refresh Databases to see the new entry.
Note. Attach needs every data file. Error 5120 means the engine cannot open the file at its path as the error 5120 guide explains.
Method #2: Restore the Primary Data File in the SSMS Restore Dialog (Free)
Use the dialog when a full backup contains the primary file. Microsoft documents the steps for a new location in restore a database to a new location.
- Right click Databases and select Restore Database.
- Select Device under Source and add the backup file. Microsoft uses Database for backups from the msdb history.
- Type a new name in the Database box under Destination when you need a copy.
- Open the Files page and tick Relocate all files to folder.
- Enter the Data file folder and the Log file folder.
- Open the Options page and tick Overwrite the existing database (WITH REPLACE) only to replace a database.
- Click OK and check the new database in Object Explorer.
Method #3: Restore the Backup With T-SQL (Free, for Scripts and New Paths)
Scripts list all files in one statement and execute on any server. List the files in the backup first with the statement below.
RESTORE FILELISTONLY
FROM DISK = N'E:\Backups\AssetDB.bak';Microsoft documents LogicalName and PhysicalName as result columns of RESTORE FILELISTONLY. Type D marks a data file and Type L marks a log file. CREATE DATABASE permission is the requirement for the command.
Copy the logical names into a RESTORE statement with one MOVE clause for every file you relocate.
RESTORE DATABASE AssetDB_Copy
FROM DISK = N'E:\Backups\AssetDB.bak'
WITH MOVE N'AssetDB' TO N'F:\SQLData\AssetDB_Copy.mdf',
MOVE N'AssetDB_log' TO N'G:\SQLLogs\AssetDB_Copy_log.ldf',
RECOVERY;Microsoft states that files restore to their original locations without MOVE. Use NORECOVERY instead of RECOVERY when log backups follow. Confirm the state of the new database after the restore.
SELECT name, state_desc, recovery_model_desc
FROM sys.databases
WHERE name = N'AssetDB_Copy';Microsoft advises RESTORE VERIFYONLY with the same MOVE parameters before a restore to another disk. Low free space and file collisions show up in the check.
Method #4: Recover the Damaged Primary Data File With EmailDoctor (Free Scan, Licensed Export)
No backup and a damaged file defeat every earlier method. Try EmailDoctor SQL Database Recovery on the damaged primary data file and its secondary files because no attach is needed. Every object shows in a preview before the export.
Demo exports stop at 25 rows for a table and 5 other objects according to product notes. Use copies of the damaged primary data file.
Scan the Primary File
- Start the software on your Windows computer.
- Click Open and browse to your AssetDB.mdf copy.
- Choose Advanced Scan because your primary data file is damaged.
- Select the SQL Server version of the file by hand when you know it.
- Add every secondary file through the NDF Options tab.
- Check the scan report after the scan ends.
- Press Yes at the save prompt and name an STR file for the scan.
- Open any table in the tree and check the recovered rows.
Export to a New Database
- Click Export and choose the Export As SQL Server Database entry.
- Choose the server and the login mode for the new database.
- Select Create New Database and type AssetDB_Restored as the name.
- Choose the objects and set the With Schema & Data option.
- Press Export and check the confirmation message.
- Refresh SSMS and open AssetDB_Restored to check the tables.
What Rules Apply to File and Filegroup Restores?
Microsoft lists six rules for a restore of files and filegroups in restore files and filegroups:
| Rule | Microsoft Requirement |
|---|---|
| SIMPLE recovery model | The file must belong to a READ_ONLY filegroup |
| Full or bulk logged model | Back up the tail of the log before the restore |
| Single user | The administrator who restores must be the only user of the database |
| Transactions | RESTORE is not allowed in an explicit or implicit transaction |
| Permission | db_owner members have no RESTORE permission by default |
| Encryption | Retain the certificate or key as long as the backup is needed |
Microsoft states that PARTIAL restores the primary filegroup and any secondary filegroup that you name. Partial restores start a piecemeal restore.
What if the Restore Fails?
Four problems explain most failed restores:
| Symptom | Cause | Remedy |
|---|---|---|
| Database name exists | A database with that name is on the server | Restore under a new name or use WITH REPLACE |
| Files already exist | The paths in the backup are taken | Add a MOVE clause for every file |
| Error 5120 | Windows denies access to the file at the given path | See the error 5120 guide for access fixes |
| Error 1813 | The new database fails to open at creation | See the error 1813 guide for repair steps |
Damaged backups and files that fail every step need Method #4 on a copy of the file. Heavy damage needs the Advanced Scan steps in the corrupt MDF repair guide.
Frequently Asked Questions
Can I Restore the MDF File Without the Log File?
Yes after a clean shutdown. Microsoft states that SQL Server rebuilds one missing log during the attach. Restores from backup create their own log file.
Is Attach the Same as Restore?
No. Attach adds existing files to an instance. Restore builds the files again from a backup.
Does a Backup File Contain the Primary Data File?
Yes. RESTORE FILELISTONLY lists the data and log files in a backup set. Type D marks the data files.
Can I List the Logical File Names Before the Restore?
Yes. RESTORE FILELISTONLY returns LogicalName and PhysicalName for every file. You need CREATE DATABASE permission.
Can I Restore to a Different Folder?
Yes. Add a MOVE clause for every logical file or use Relocate all files to folder in SSMS. Files return to their original paths without MOVE.
Does REPLACE Delete the Existing Database?
Yes. Microsoft states that SQL Server deletes the existing database with the same name. Restore under a new name to avoid the loss.
Can I Restore Only the Primary Filegroup?
Yes. PARTIAL restores the primary filegroup and any secondary filegroup that you name.
Does the Database Owner Role Include Restore Permission?
No. Microsoft states that db_owner members have no RESTORE permission by default. Members of sysadmin and dbcreator and the owner dbo have it when the database exists.
Do I Need the Same Name for the Restored Database?
No. Type a new name under Destination in SSMS. Microsoft requires new file names with MOVE when you copy a database on the same instance.
Can Recovery Software Restore a Primary Data File Without a Backup?
Yes. Recovery software works on the damaged file alone and sends its objects to a new database. Preview the tables before the export.
Does the Free Demo Limit the Export?
Yes. Product notes list 25 rows for a table and 5 other objects as the demo limit. Licensing removes both limits.
Author’s Verdict
Primary data files come back through an attach or through a restore. Choose the method from the files you have.
Attach suits orphan files and a restore suits backups. Scripts add control over file paths.
Recovery software exports damaged file objects when no backup exists. Test every method on a copy first.
- Attach adds an existing primary data file and RESTORE rebuilds the file from a backup.
- RESTORE FILELISTONLY lists the logical names and MOVE sets the new paths.
- REPLACE deletes an existing database with the same name.
- File level restores follow the Microsoft rules for recovery models and permissions.
- Recovery software moves damaged file objects into a fresh database.
- Work on copies.
Next Read: Convert an MDF File to SQL Script
Also Read: How to Open an MDF File | Fix SQL Database Error 1813
