Exporting a database from SQL Server Management Studio starts with the file you need, not with a single Export button. A .sql script, a .bak backup, a .bacpac file, and a copy of table rows solve different jobs. Match the command to that file, run it, and check the copy before you depend on it.
Generate Scripts, Back Up, Export Data-tier Application, and Export Data all sit on the Tasks menu. They do not write the same result. Pick the destination first. The clicks are straightforward after that.
Which SSMS export should you use?
SSMS has no single Export Database command. Use Generate Scripts for a .sql file, Back Up for a .bak file you can restore on another SQL Server, and Export Data-tier Application for a .bacpac file. Use Tasks > Export Data only when you need rows in a spreadsheet, CSV, or another database. That wizard does not package the whole database.
Method | Menu path | File | Schema | Data | Best use | Poor fit |
Generate Scripts | Tasks → Generate Scripts | .sql | Yes | Only if you set it | A script SQL Server can run | A large set of rows |
Back Up | Tasks → Back Up | .bak | Yes | Yes | Restore onto SQL Server | Azure SQL Database |
Export Data-tier Application | Tasks → Export Data-tier Application | .bacpac | Yes | Yes | Move schema and rows to Azure SQL Database | A spreadsheet |
Export Data | Tasks → Export Data | CSV, Excel, or rows in another database | Tables can be created at the destination | Yes | A data copy | A full object package |
Detach, then copy the files | Tasks → Detach | .mdf and .ldf | Yes | Yes | A stopped database during rare maintenance | A normal export |
Detach keeps the database offline until reattached.
How to export the database as a SQL script
Microsoft’s Generate and Publish Scripts Wizard is the reference for the options below.
Connect to the Database Engine and expand Databases.
Right-click the database, choose Tasks, then Generate Scripts.
Click Next on the introduction page.
Script the entire database, or select the objects you want.
On Set Scripting Options, save to a file.
Open Advanced and apply the two choices in the sections below before you continue.
Read the summary, then finish.
Confirm the .sql file is on the path you selected.
Set schema only or schema and data
Set Types of data to script yourself. Microsoft documents three values, and the default is Schema only.
Schema only writes object definitions and leaves the rows behind.
Schema and data adds the rows as script statements. Use it when the rows truly have to travel inside the .sql file.
Data only fits a rare refresh of tables that already exist.
Schema and data suits a few reference tables, or a database structure you want to keep in source control. Microsoft’s scripting tutorial notes that scripting the data can take more memory than SSMS can allocate, so put a large history table in a .bak or a .bacpac.
Keep the script usable
A script can create tables and still omit the rules that make them safe. In Advanced, set these to True:
Script Indexes
Script Primary Keys
Script Foreign Keys
Script Triggers
Turn on scripting for the USE statement so the file names the destination database. Set Script for Server Version to the Database Engine that will run the file. A script aimed at an older version leaves out features that engine cannot run. Leave Script DROP and CREATE on Script CREATE unless you intend to drop objects that already exist. Choose Unicode when the data contains characters a non-Unicode file would mangle. One file is easier to hand over. One file per object is easier to rerun when a single view or procedure fails.
How to export a full copy as a .bak or .bacpac file
A script is text. A full copy is a database file another SQL Server tool can load. The two files below are not interchangeable.
Create a full .bak backup
Right-click the database, choose Tasks, then Back Up. Set Backup type to Full. Under Destination, choose Disk and add a path that ends in .bak. Remove an old path if SSMS still points at a previous backup you do not want to overwrite. Run the backup.
A full backup is the usual archive, and it is the usual way to move a database between SQL Server instances that support backup and restore. Full means the whole database. A differential or a log backup is only a later piece, so neither one is a standalone export. The SQL Server service account must be able to write the folder you chose. A path your Windows user can open is not always a path the service can write. If that .bak already holds older backup sets, choose append or overwrite on purpose. Overwrite replaces what is already in the file.
Export a .bacpac file
Right-click the database, choose Tasks, then Export Data-tier Application. Move past the introduction. On Export Settings, save to local disk and include .bacpac in the file name. Finish the wizard.
Microsoft’s BACPAC export writes object definitions and table data. That file can be imported into another Database Engine instance or into Azure SQL Database. Azure SQL Database does not take the disk .bak from the Back Up dialog, so the BACPAC is the file you create for that destination.
A BACPAC is a logical copy, and its object model is limited to the Azure SQL Database surface area. Objects a .bak would contain can fail validation in a BACPAC.
How to export table data with the Import and Export Wizard
The Import and Export Wizard copies rows from a source to a destination. It does not build a .bak, a .bacpac, or a script of procedures, views, and keys. Right-clicking a result grid and choosing Save Results As is narrower still: that command saves the one result on screen.
Start it from the database you are connected to:
Connect to the Database Engine in SSMS.
Right-click the database, choose Tasks, then Export Data.
Choose the source. The current database is the usual source.
Choose the destination: another database, a flat file for CSV, or Excel.
Pick tables, or write a query when you need some rows rather than every table.
Review the mapping and run the package.
SSMS is a 32-bit application, and the wizard it installs is 32-bit. A 64-bit provider, including some Excel drivers, will not load in that wizard. Install the matching 32-bit provider, or run the 64-bit wizard that ships with SQL Server, before you fight the column mapping.
How to open the export on another SQL Server
Each file has its own load step. Connect Object Explorer to the destination instance first. Running the file on the source only creates another copy there. The destination has to be Microsoft SQL Server, or Azure SQL Database when the file is a BACPAC.
A .bak file, a .bacpac file, or a SQL Server script will not import into PostgreSQL, and it will not load through phpMyAdmin. On HostCry’s managed databases page, PostgreSQL is the engine available now. MySQL, MariaDB, MongoDB, and Redis are marked Coming Soon. Microsoft SQL Server is not listed. Match the application’s engine before you export.
Run the .sql file
Create an empty database on the destination instance. Open the script, change the USE line to that database name, and execute it. If SSMS cannot open the file, run it with sqlcmd against the same instance instead of pasting it into a query window.
Restore the .bak file
Right-click Databases, choose Restore Database, and select Device. Add the .bak file. Check the data and log file paths before you confirm. Restore over an existing name only when you mean to replace that database.
Database users travel inside the backup. Server logins live in master on the instance, so they do not come along. Microsoft’s orphaned-user guidance covers finding those users and mapping them to a login on the new server. Expect an application login to fail until that mapping exists.
Import the .bacpac file
Right-click the Databases folder, choose Import Data-tier Application, and select the local .bacpac. Set the new database name and finish. Import creates a new database. It does not patch objects inside one you already have.
How to fix an export that fails or comes out incomplete
A success message means the wizard ended. It does not mean the file contains what you asked for. Match the symptom, then change one setting.
What you see | Likely cause | What to change |
Tables exist in the script, and the rows do not | Types of data to script is still Schema only | Set Schema and data, then generate the file again |
SSMS will not open or execute the .sql file | The data script is larger than SSMS can handle | Create a .bak or a .bacpac instead |
Scripting stops on one object | Continue scripting on error is off, or a dependent object was not selected | Set Continue scripting on error to True, or include the dependent objects, then read the error |
BACPAC validation fails | The database uses an object outside the Azure SQL surface area | Export a .bak for a SQL Server destination, or remove that object and export again |
Backup stops before it writes the file | The path, the file permission, or the disk is the blocker | Choose a folder the SQL Server service account can write, and confirm the disk has room |
Export Data stops on a provider or a column | The 32-bit wizard cannot load the driver, or a type does not map | Install the matching 32-bit provider, or cast the column in a query |
Microsoft publishes no single maximum size for a .sql script. The failure you will see is SSMS running out of memory as it opens or runs the file.
How to confirm the export actually worked
Check the copy against the source before you delete anything.
Compare object counts: tables, views, procedures, and functions.
Compare row counts on the largest tables, not only on a lookup table. Count both sides in the same hour, because later writes can look like missing rows.
Open a sample of primary keys, foreign keys, and procedures and confirm they exist in the copy.
Restore a .bak or import a .bacpac on a spare instance and connect an application login to it.
The spare-instance load is the check that matters for a backup or a BACPAC. A file that was written to disk can still fail when SQL Server tries to read it.
After the export
Use the file your destination can accept, and load that copy on a spare server before you remove the original. If the application still needs a home, talk to HostCry and include the database name, the engine it uses, and the file you produced.