Export SQL Server database to SQL file with the Generate Scripts wizard or the Script as menu. Generate Scripts saves structure by default and needs the data option set to schema and data for rows. Migration software exports a live database to SQL scripts with schema and rows.
You need to export SQL Server database to SQL file for a test server or an archive and SSMS gives you a script without a single row. Two settings in the wizard decide that.
Most guides stop at the wizard pages. Few explain the data setting, the server version and the encoding that decide whether the file works on another server.
This guide explains three ways to save a database as a SQL file and how to use the file on another server.
Microsoft lists every option in its Generate Scripts wizard reference.
Which Method Fits Your Database?
Match your need to a method first:
| Your Need | Method |
|---|---|
| A full database with rows in one file | Method #1 |
| One table or a few objects of one type | Method #2 |
| A live database with a preview and object selection | Method #3 |
Types of data to script defaults to Schema only according to Microsoft. Rows appear in the file after you change that setting.
What Should You Prepare First?
Four items prevent a failed file:
- Sign in with a login that can access every object you plan to script.
- Note the SQL Server version of the destination server because the script must match it.
- Create a backup before the move because a script cannot replace one. Explore Different Types of Backups in SQL Server explains the types.
- Free disk space for the file because scripts with data grow large.
3 Ways to Export SQL Server Database to SQL File
You get two SSMS options and one software option:
| Method | Best For | Limit |
|---|---|---|
| Generate Scripts Wizard | Full database | Schema only by default |
| Script as Menu | Single objects | No table rows |
| Migration Software | Live database with preview | Trial exports are capped |
Method #1: Export the Database With the Generate Scripts Wizard (Free)
Generate Scripts saves the full database in one file. Eight steps create the file:
- Right click your database under Databases in Object Explorer.
- Point to Tasks and select Generate Scripts....
- Continue past the Introduction page.
- Select the option for the entire database on Choose Objects.
- Select Save as script file on Set Scripting Options and choose Single script file.
- Select Unicode text or ANSI text and type the file path.
- Click Advanced and choose schema and data for Types of data to script. Click OK.
- Click Next on the Summary page and click Finish on the Save Scripts page.
Note: Set Script for server version to the version of the destination server. Microsoft sets the source version as the default. Check for object existence adds a test before every object so a second use of the file does not fail.
Method #2: Script Single Objects With the Script as Menu (Free)
Script as creates the definition of one object or several objects of one type. Six steps script a table:
- Expand the database and the Tables node.
- Right click the table and point to Script Table as.
- Point to Create to.
- Select New Query Editor Window, File or Clipboard as the location.
Several tables need two more steps:
- Press F7 to open Object Explorer Details and click the first table. Press Ctrl and click the others.
- Right click one selected table and select Script Table as.
Note: Microsoft states that the output uses Unicode format. Script as saves definitions and no rows so choose Method #1 or Method #3 for data.
Method #3: Export a Live Database to SQL Scripts With EmailDoctor (Trial Limit Applies)
EmailDoctor SQL Server Data Migration Tool reaches a live server in Online mode and exports tables, views, stored procedures and triggers to SQL Server compatible scripts. You select the objects and see a preview before the export.
Free mode exports 25 records from every table and 5 other categories according to the vendor.
Connect to the Live Server
Install the software on Windows. Six steps connect and show the objects:
- Open the software.
- Click Open and select Online mode.
- Type the source server and the login in Server Name with Authentication.
- Open the Drop Down List and select the database. Click OK.
- Check the objects in the preview tree.
- Click Export.
Save the SQL Scripts
Three steps create the files:
- Select the SQL Server Compatible SQL Scripts option from the list of four.
- Tick the objects and choose With Schema & Data for rows or With only Schema for structure.
- Click Save and set the destination folder. Close the Export Completed message after the export.
Note: Offline mode scans MDF files that no server has attached. How to Convert MDF File to SQL Script in SQL Server has the steps for it.
How do You Use the SQL File on Another Server?
sqlcmd applies the file in three steps:
- Open Command Prompt on a PC that reaches the destination server.
- Type the command with your own server and path:
sqlcmd -S SQLHOST02 -E -i "C:\scripts\SalesDB.sql" -b- Check the messages for errors.
-i names the file and -b stops with an error level when an error occurs. Add -x when the data contains text in the $(name) format because sqlcmd treats it as a scripting variable.
Note: Microsoft states that quotation marks must surround paths with spaces. -i and -Q cannot work together.
Which Problems Break the Exported File?
Check these five failures before you blame the wizard:
| Problem | Reason | Fix |
|---|---|---|
| The file has no rows | Types of data to script defaults to Schema only | Select schema and data |
| The script fails on an older server | The script was made for a newer server version | Set Script for server version to the destination version |
| Dependent objects are missing | Dependent objects are off for selected objects | Script the entire database |
| The wizard stops at an error | Continue scripting on error is off | Set it to True |
Text with $(name) fails in sqlcmd | sqlcmd treats it as a variable | Add -x |
Frequently Asked Questions
Does Generate Scripts Include the Data?
No. Types of data to script defaults to Schema only according to Microsoft. Change it to include rows.
Can I Save the Full Database in One File?
Yes. Select Single script file on Set Scripting Options. One script file per object creates many files.
Is mssql-scripter Still Available?
Microsoft archived the repository in May 2024 and calls the tool no longer actively maintained. Use the Generate Scripts wizard instead.
Why Does the SQL File Show Strange Characters?
Your editor opens the file with another encoding. Select Unicode text for non English data and open the file in an editor that handles it.
How do I Keep the Old File When I Export Again?
Use a new file name. Overwrite existing file replaces the old file and Append to file under Advanced is off by default.
Can I Export Logins With the Database?
Yes. Script logins and Script object-Level permissions are off by default. Set both to True under Advanced.
Does the Script as Menu Include Table Rows?
No. Script as saves only the definition of the object. Use the wizard with Types of data to script for rows.
Can I Try the Migration Software Before I Buy?
Yes. Free mode stops at 25 records from every table and 5 other categories.
Author’s Verdict
Use the wizard for a full database and check Types of data to script before you click Finish. Match the server version to the destination.
Use the Script as menu for one table and migration software when you want a preview and object selection.
Test the file on a scratch server with sqlcmd before any production use.
- Generate Scripts exports a full database to one SQL file and needs the data setting for rows.
- Script as exports definitions only.
- Migration software exports a live database to SQL scripts with a preview.
Next Read: SQL Server Generate Scripts
Also Read: How to Convert MDF File to SQL Script in SQL Server | How to Transfer Data Between Two SQL Servers
