SSMS cannot convert MDF to SQL file directly. Attach the MDF file to an instance first and the Generate Scripts wizard then saves a SQL script. Three methods exist and they are the wizard, specific object scripts and recovery software. Wizard output has the schema only until you set the data option. Recovery software handles a damaged MDF file that refuses to attach. Test every script on a test server.
You can convert MDF to SQL file with SSMS or with recovery software. SSMS shows databases from an instance and never a loose file so the MDF file needs an attach first. Most readers who search for this want a script they can execute on another server.
Methods below go in order of effort and include a route for files that refuse to attach. You also get the one wizard setting that decides whether table rows reach the script, the settings that exclude objects and a check that proves nothing went missing.
Microsoft states that the Generate Scripts wizard creates a script for all objects of a database or for a subset of them.
What Does It Mean to Convert MDF to SQL File?
MDF is the extension of the primary data file of a database. MDF files store data pages in a binary format and no editor can display them. SQL files are text scripts of T-SQL statements that rebuild the schema and optionally the rows on any server.
Conversion therefore means two steps. First the engine opens the MDF file as a database and then a script generator produces text from it. Restore works differently because it starts from a backup file and never creates a script.
Many searches for a master database file mean the primary data file of a database. Microsoft describes the master database in its master database reference as the place that records all system level information of a SQL Server system.
| Term | Meaning | Route to a SQL File |
|---|---|---|
| MDF file | Primary data file of any database | Methods #1 to #3 below |
| master database | System database with instance level information | Backup and restore |
| master.mdf | Primary file of the master database | Backup and restore |
Microsoft documents a restore of master from a full database backup in its system database backup and restore article. Methods below handle the primary file of a user database.
Which Method Should You Choose to Convert MDF to SQL File?
Four reasons push readers to convert MDF files:
- Portable Copy. Text scripts move to another server without a copy of the MDF file.
- Version Move. The wizard has a Script for server version setting for a different release.
- Readable Review. Text shows every statement before a server executes anything.
- Damaged File. Recovery software saves objects as scripts when an attach fails.
Match your file to a method with this table:
| Your Situation | Method | Cost |
|---|---|---|
| Healthy file and you need the full database with rows | Method #1: Generate Scripts wizard | Part of SSMS |
| Healthy file and you need one table or procedure | Method #2: Script specific objects | Part of SSMS |
| Damaged file or an attach that fails | Method #3: Recovery software | Free demo and paid export |
Prerequisites and Safety Checklist Before You Convert MDF to SQL File
Confirm five things before you start:
- File Copies. Copy the MDF file and every NDF and LDF file to a safe folder.
- Instance. Use a Database Engine instance on the same or a newer release than the file.
- Rights. Microsoft names the db_ddladmin fixed database role on the origin database for the wizard.
- Health Check. DBCC CHECKDB shows whether the database is healthy and failed checks point to Method #3.
- Disk Space. Scripts with data grow with the row count so free space in the output folder matters.
Attach upgrades the database to match the new instance. Use copies of the MDF file because the original cannot go back to an older release.
3 Methods to Convert MDF to SQL File
Methods #1 and #2 use SSMS and cost nothing. Method #3 uses recovery software and fits files that refuse an attach.
| Feature | Generate Scripts Wizard | Specific Object Scripts | Recovery Software |
|---|---|---|---|
| Cost | Part of SSMS | Part of SSMS | Free demo and paid export |
| Best for | Database or several objects with rows | One table or procedure | Damaged files and failed attaches |
| Needs | Attached healthy database | Attached healthy database | Windows computer and file copies |
| Main limit | Schema only default | Single object scope | Demo export limit |
Method #1: Convert MDF to SQL Using SSMS (Free)
Attach the file first with the attach steps for an MDF file. Then the wizard scripts the live database.
- Right click FleetDB in Object Explorer and select Tasks then Generate Scripts.
- Click Next on the introduction page.
- Choose Script entire database and all database objects on the Choose Objects page.
- Select Save scripts to a specific location then Save as script file on the Set Scripting Options page and set the file path.
- Choose Single script file or One script file per object and a Unicode text or ANSI text format.
- Open Advanced and set Types of data to script to Schema and data.
- Confirm with OK and check the Summary page.
- Click Next to generate the script and Finish when the Result column shows success.
Note. Microsoft states that Schema only is the default for Types of data to script. Without the data setting the script creates empty tables.
Microsoft lists three outputs on the options page and they are a script file, the clipboard and a Query Editor window.
Method #2: Generate SQL Scripts for Specific Database Objects (Free)
Use this method for a single table or a stored procedure. Microsoft lists the Script as menu for one or more objects with a Query Editor window, a file or the clipboard as the destination.
- Expand FleetDB and Tables in Object Explorer.
- Right click a table and choose Script Table as then CREATE To then File.
- Save the .sql file in a folder you can find later.
- Repeat the steps for views and stored procedures from their own menus.
Several objects of one type work through the Object Explorer Details pane. Press F7 and select the objects with Ctrl clicks. Then right click one of them and choose the Script as command.
Use Method #1 when the destination needs the table rows because the wizard has the data setting. Choose Select specific database objects on its Choose Objects page to script a subset with rows.
Method #3: Convert MDF to SQL Without Attaching the File (EmailDoctor, Demo Preview, Paid Export)
Damaged files fail the attach and the wizard alike. EmailDoctor SQL Database Recovery opens the primary file together with the secondary files and needs no attach. Preview mode lists every object before you save a script.
Recovery software accepts MDF and NDF files and never asks for the LDF file. Demo exports stop at 25 rows from a table and 5 further objects. Work on copies of the MDF file.
Scan the Database Files
- Open the recovery software on a Windows PC.
- Click Open in the top bar and browse to your FleetDB.mdf copy.
- Select Quick Scan for the first attempt. Clear the Auto detect box to choose the SQL Server version by hand.
- Open the NDF Options tab and add the NDF files.
- Check the scan report and then the Quick Scan note.
- Open a table from the tree on the left and check that its rows appear.
Save the Data as a SQL Script
- Open the Export menu in the toolbar.
- Choose SQL Server Compatible SQL Scripts from the format list.
- Tick the objects to save in the components list.
- Choose With Schema & Data and press Save.
- Select an output folder for the script files.
- Confirm the Export Completed dialog and close the status window.
- Open the output folder to see the SQL files. Check the script in SSMS before execution.
Deleted objects show in red in the preview tree. Files with heavy damage need Advanced Scan and the corrupt MDF repair guide has the steps.
Generate Scripts Advanced Settings Explained
Advanced settings decide what the script contains. Microsoft documents these defaults on the Advanced Scripting Options page of the wizard:
| Setting | What It Does | Default |
|---|---|---|
| Types of data to script | Saves schema only or data only or both | Schema only |
| Script for server version | Creates a script for the selected release | Version of the origin server |
| Script logins | Adds the logins that a database user needs | False |
| Script object level permissions | Adds permissions set on objects | False |
| Script triggers | Adds DML triggers of tables | False |
| Script indexes | Adds index creation | True for the entire database and False for selected objects |
| Script full text indexes | Adds full text index creation | False |
| Check for object existence | Wraps every CREATE in an existence check | False |
| Script DROP and CREATE | Places a drop before every create | Script CREATE |
| Script USE DATABASE | Adds a USE statement | True |
Set Types of data to script to Schema and data for a full copy. Switch on triggers, permissions and logins when the new server must match the old one.
What Data and Database Objects Does the Script Include or Exclude?
Wizard defaults include tables, primary keys, foreign keys, check constraints, unique keys, defaults and extended properties. Table rows join the script only after you set Schema and data.
Defaults exclude these items unless you switch them on:
- Triggers. DML triggers need the Script triggers setting.
- Permissions and Logins. Object level permissions and logins remain out of the script.
- Full Text Indexes. Full text indexes need their own setting.
- Statistics. Statistics need the Script statistics setting.
- Collation. Collation details join the script only through Script collation.
Server level items live outside the database. Linked servers and SQL Agent items therefore never appear in a database script.
How to Convert a Corrupted or Unattachable MDF File to SQL File
Convert MDF to SQL file with recovery software when the attach fails. Three signs point to this route:
- Attach Errors. SSMS shows error 5120 or error 1813 during the attach.
- Suspect State. The database shows as suspect after the attach.
- Missing Files. The attach fails because a data file is missing.
Microsoft states that SSMS shows a missing log file in the database details grid. You must update the path or remove the log file from that grid. Use the error 5120 guide and the error 1813 guide for the engine messages.
Method #3 opens the damaged primary file directly. Work on copies and review the preview tree before you save a script.
How to Convert MDF to SQL File for an Older SQL Server Version
Attach cannot move a database to an older release. Microsoft states that attach upgrades the database to the internal version of the new instance. Scripts solve this problem because text has no version.
- Attach the MDF file to the newest instance you have.
- Open Advanced in the wizard and set Script for server version to the older release.
- Save the script and execute it on the older server.
Microsoft states that features new to a version cannot be scripted for earlier versions. Remove those objects or edit the script by hand when the wizard reports them.
How to Execute the Generated SQL Script on Another Server
Two ways work for a script file:
- SSMS. Connect to the destination instance and open the .sql file with File then Open. Click Execute.
- sqlcmd. Microsoft documents -S for the server and -E for a trusted connection. Use -i for the input file.
sqlcmd -S localhost -E -i "D:\Scripts\FleetDB.sql"
Microsoft details the options in its sqlcmd utility reference. Check the CREATE DATABASE statement and its file paths before you execute the script on a new server.
Large script files open slowly in an editor. Use sqlcmd for them because it opens the input file directly.
How to Verify That the Conversion Missed No Data
Compare the new database with the source after the script finishes. Exact record counts, object counts, a DBCC check and an application test prove the result.
Start with the row counts of every table:
USE FleetDB;
SELECT t.name AS table_name, SUM(p.rows) AS row_count
FROM sys.tables AS t
JOIN sys.partitions AS p ON p.object_id = t.object_id
WHERE p.index_id IN (0, 1)
GROUP BY t.name
ORDER BY t.name;
Count the user objects next and compare both lists:
SELECT type_desc, COUNT(*) AS object_count
FROM sys.objects
WHERE is_ms_shipped = 0
GROUP BY type_desc
ORDER BY type_desc;
Then check the structure of the new database with DBCC CHECKDB:
DBCC CHECKDB (FleetDB) WITH NO_INFOMSGS;
Finish with the permissions and an application level test. Open the application against the new server and open a few records.
Common Errors When You Convert MDF to SQL File and Their Fixes
Six problems explain most failed conversions:
| Problem | Meaning | Fix |
|---|---|---|
| Empty tables after the script | The data option is still Schema only | Repeat Method #1 with Schema and data |
| Path error in CREATE DATABASE | The script names folders of the source server | Correct every file path in the script |
| Error 5120 | The engine fails to open the file at its path | Apply the fixes in the error 5120 guide |
| Error 1813 | CREATE DATABASE stops while it opens the new database | Apply the fixes in the error 1813 guide |
| Script fails on an older server | The script is written for a newer release | Repeat Method #1 with Script for server version set |
| Suspect or corrupt file | The attach or the wizard fails on damaged pages | Use Method #3 on a copy of the file |
Frequently Asked Questions
What is the Difference Between an MDF File and a SQL Script?
MDF files are binary primary data files and only an instance can open them. SQL scripts are text files of T-SQL statements that rebuild the schema and rows on any server. Convert the MDF file when you need a portable copy.
Do You Need Both MDF and LDF Files to Generate a SQL Script?
Not always. SSMS lists a missing log file in the database details grid and Microsoft states that you update its path or remove it from the grid. Recovery software accepts the MDF file and the NDF files and never asks for the LDF file.
How do You Convert MDF to SQL File Without Losing Table Records?
Open Advanced in the Generate Scripts wizard and set Types of data to script to Schema and data. Microsoft states that Schema only is the default and it creates empty tables. Compare the row counts after the script finishes.
What Database Objects Does a Generated SQL Script Exclude?
Defaults exclude triggers, object level permissions, logins, full text indexes, statistics and collation details. Switch on the matching settings on the Advanced page to include them. Linked servers and SQL Agent items live outside the database and no database script includes them.
Can You Convert MDF to SQL File for an Older SQL Server Version?
Yes when the release is supported. Open Advanced and set Script for server version to the older release. Microsoft states that features new to a version cannot be scripted for earlier versions so check the script before you execute it.
How can You Split a Large MDF File Into Manageable SQL Scripts?
Choose One script file per object on the Set Scripting Options page. Script tables one at a time when a single table has millions of rows. Execute the files in order with sqlcmd.
Can You Use the Script Directly in MySQL or PostgreSQL?
No. SSMS creates T-SQL and the script uses T-SQL syntax with GO batch separators. MySQL and PostgreSQL need edits to the data types, the identity columns and the batch separators before the script works.
How can You Check That the MDF to SQL Conversion Worked?
Compare row counts and object counts between the source and the new database. Execute DBCC CHECKDB on the new database and open the application against it. See the verification section above for the queries.
Why Does an Exported SQL Script Fail During Execution?
Four causes explain most failures and they are wrong folder paths, a script for a newer release, a rejected GO separator and a damaged file. Check the paths first and then the version.
Is Converting an MDF File to SQL the Same as Restoring a Database?
No. Restore starts from a backup file and recreates the database directly. Conversion produces text scripts from an attached or recovered database and you execute them to build a new database.
Final Checklist and Author’s Verdict
Check these items before you execute a script on a production server:
- Copies. Work on copies of the MDF file and its companions.
- Data Setting. Confirm Schema and data in the wizard.
- Extras. Switch on triggers, permissions and logins when needed.
- Test Server. Execute the script on a test server first.
- Verification. Compare row counts and object counts after the script.
SSMS cannot convert MDF to SQL file on its own. Attach the file first and Generate Scripts then saves the SQL file. Set Schema and data before you start or the script creates empty tables.
Use specific object scripts for a single table or procedure. Recovery software converts the files that fail an attach. Review every script on a test server first.
- MDF is the binary primary data file and a SQL file is a text script.
- Generate Scripts converts an attached database and its default saves the schema only.
- Schema and data must be set for rows.
- Advanced settings add triggers, permissions and logins.
- Recovery software converts a damaged file without an attach.
- Test every script on a nonproduction server.
Next Read: Connect an MDF File to SSMS
Also Read: Generate Script With Data in SQL Server | Repair Corrupt SQL MDF Files