Arsal • Location • Starter

Technical Review: Hostcry Engineering Team

How to Export Database from SQL Server Management Studio

AI Summary
Exporting a database from SQL Server Management Studio depends on the file you need, since SSMS has no single Export Database command. Use Generate Scripts for a .sql file, Back Up for a .bak file that restores on another SQL Server, Export Data-tier Application for a .bacpac file (the right choice for Azure SQL Database), and Export Data only when you need rows in CSV, Excel, or another database. Set the options carefully, such as including both schema and data in scripts and making sure the SQL Server service account can write to the backup folder. Before deleting the original, confirm the copy works by comparing object and row counts and restoring or importing it on a spare instance. Remember that these files will not import into PostgreSQL, which is the engine HostCry currently offers for managed databases.

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.

  1. Connect to the Database Engine and expand Databases.

  2. Right-click the database, choose Tasks, then Generate Scripts.

  3. Click Next on the introduction page.

  4. Script the entire database, or select the objects you want.

  5. On Set Scripting Options, save to a file.

  6. Open Advanced and apply the two choices in the sections below before you continue.

  7. Read the summary, then finish.

  8. 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:

  1. Connect to the Database Engine in SSMS.

  2. Right-click the database, choose Tasks, then Export Data.

  3. Choose the source. The current database is the usual source.

  4. Choose the destination: another database, a flat file for CSV, or Excel.

  5. Pick tables, or write a query when you need some rows rather than every table.

  6. 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.

Comparing a source SQL Server database with its exported copy before deleting the origina

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.

Got questions?

Frequently Asked Questions

Quick answers about this article.

SSMS runs on Windows. From a Mac or Linux machine, open a Windows session that can reach the instance, or use SqlPackage, which runs on Windows, macOS, and Linux. Microsoft’s SSMS-on-Linux guidance shows the BACPAC commands when SSMS is not installed there.

A BACPAC includes schema and table data by default, and you import it into a new database. A DACPAC is a deployment package. Data stays out of a DACPAC unless it was selected during extract. For a copy of the rows, export a BACPAC. Microsoft’s overview separates the two files.

Schema-only scripting is usually the short job. Time grows with data volume, disk speed, and network distance. A schema-and-data script takes longer than a backup of the same database. No minutes-per-gigabyte figure fits every server.

Generate Scripts can target one schema or a few tables. The Import and Export Wizard can take selected tables or a query. A full backup still contains the whole database.

The wizards run when you click them. A repeating backup belongs in a maintenance plan or a SQL Server Agent job. SSMS will not rerun Generate Scripts on a schedule by itself.

The file is a copy of the data, and tables or procedures can hold personal details and secrets. Microsoft states that BACPAC and DACPAC files are compressed, not encrypted. Use a private channel.

A backup restores on the same Database Engine version or a newer one. An older instance rejects it. Microsoft documents that limit, including error 3169. Compare the versions before you create the file.

Scripting needs permission to view the definitions you select. BACKUP DATABASE permission defaults to sysadmin, db_owner, and db_backupoperator, as the BACKUP statement documents. A BACPAC export follows the separate list on Microsoft’s export page.