How to export a MS SQL Server database to a SQL script (database to sql file) with Microsoft SQL Server Management Studio 17

Most of the developers feel not so comfortable with .bak files that are generated by the export backup option of SQL Server, at least for not so huge databases. For this reason, you may need to export sometimes the database in the script format (.sql). However, trying to export it in this format may be a headache for some people that are not familiar with the SQL Server Managemente Studio.

In this article, we'll explain briefly how to export the structure and data from a database stored in SQL Server into a .sql file easily.

Generating .sql file of your database with data

SQL Server Management Studio provides the ability to generate scripts for creating databases, tables, stored procedures, functions, views and inserting data. For smaller amounts of data, this method can be used to send table structures and/or data to Scribe Support for testing purposes.

To proceed, Open SSMS and access the database engine with the default Windows authentication (or from the connection that you want to access):

SQL Server Management Studio SSMS Login

Connect to the server and explore the databases in the object explorer. In our case, we want to export the my_database database, that, as you can see, is available in the Databases directory:

SQL Server Management Studio Database Viewer 

Do right click on the database and select Tasks and then on Generate Scripts:

Generate Scripts SQL Server

This will open the Generate and Publish Scrips dialog. As first step, define which tables of the database you want to export, optionally you can script the entire database and objects:

Export Tables MS SQL Server Management Studio

After confirm which tables do you want to export, proceed to define if you want a single file per table or everything in a single file. If you go to the advanced scripting options, you can define the types of data to script, this means if you want to include the data on the file directly or only the structure of the database:

SQL Server Export Data and Structure

Finally, the export will begin and you will see the success message at the end:

Success SQL Server Script Structure and Data Export

and if you edit the generate script.sql, you will see plain SQL text:

USE [master]
GO
/****** Object:  Database [my_database]    Script Date: 12-Mar-19 7:07:12 PM ******/
CREATE DATABASE [my_database]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'SICitas', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\my_database.mdf' , SIZE = 24512KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
 LOG ON 
( NAME = N'SICitas_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\my_database_1.LDF' , SIZE = 32448KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
GO
ALTER DATABASE [my_database] SET COMPATIBILITY_LEVEL = 100
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [my_database].[dbo].[sp_fulltext_database] @action = 'disable'
end
GO
ALTER DATABASE [my_database] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [my_database] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [my_database] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [my_database] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [my_database] SET ARITHABORT OFF 
GO
ALTER DATABASE [my_database] SET AUTO_CLOSE ON 
GO

...


...

Happy coding !

This could interest you

Become a more social person