Generate script for both schema and data

Question

I have a SQL Server database for which I want to generate script of data as well as schema both. I tried Tasks -> Generate Scripts -> Script all objects in the selected database but it does not give the .sql for the data itself.

How do we generate the .sql database for both data as well schema? Please suggest some easy method such as a tool or something that can be used easily

Answer 1

Backing up/Exporting database

There are two ways to Back up/Export a SQL Server database using SQL Server Management Studio:

1. Right click database → Tasks → Generate Scripts → Choose DB → Change “Script Data” option to true → …

2. Right click database → Tasks → Backup → ...

The first method creates a .sql file that then we need to run. The problem with this method is that the .sql file can be too big to be opened with SQL Server Management Studio. In that case we need to use the sqlcmd utility (should be already installed if we have SQL Server MS). Instructions below.

The second method creates a .back file that is then easy to import into an empty database.

Importing Database

If we have a .sql file and it’s not too big we can just open it with SQL Server MS and run it.

If we have a .sql file but it’s too big to be opened with SQL Server MS we have to use sqlcmd like this:

>sqlcmd -i C:\panels_QA28July11.sql -o C:\PanelsImportResult.txt

The parameter after -i is the file to import. The parameter after -o is where to save the output. We can omit the second parameter if we want to see the process on the screen.

By default it will use the local machine and local database server. If we want to use a different machine and server we use the -S option.

Answer 2

Right Click on db => Tasks => Generate Scripts => In "Set Scripting Options: Click Advanced, find Types of data to script. You can choose between Data only, Script and data and Schema only. The default is Schema only

Answer 3

Images talks better than words, :)