How To Generate SQL Database Script With Data
Generating a database script is one of the useful way to generate database schema and deploy on another database instance. We can generate database schema with or without data.
Generating a database script is useful when there is difference in versioning of database applications.
Normally we migrate database using backup and restore methods, but this is not possible to migrate database from lower version to higher version. So in this case, we generate a script and run on the higher instance of SQL Server.
The database script could include all the tables with all constraints, views, triggers, stored procedures, login details as well as tables data.
To deploy the database on another instance of SQL Server, Just run the script in query editor window. We will create a database script step by step using Microsoft SQL Server Management Studio 2014.
Step-I: Open SQL Server Management Studio and click on Connect button as shown in the following Image.
Step-II:
- Right-click on the database for which you want to create a script. In this demo, we will use NORTHWIND database.
- Move cursor to Tasks.
- Click on Generate Scripts…
Step-III:
An introductory Generate and Publish Scripts window will open, Click on Next button as shown in the below image.
Step-IV:
- Choose Objects from left pane of Generate and Publish Scripts window is selected. Keep selected, as it is.
- By default, Script entire database and all database objects radio button is selected.
If you want to generate script for the entire database including all Tables, Views and Stored Procedures, keep the default radio button Script entire database and all database objects selected.
- Click on the Next button.
OR
Step-IV:
- If you want to generate script for specific objects, choose the second radio button to Select specific database objects and check mark the objects for which you want to generate the script as shown below.
- Click on Next button.
Step-V:
- By default, Set Scripting Options is highlighted on the left pane of Generate and Publish Scripts window. Keep it as it is.
- Select Save scripts to a specific location radio button if you want to save on local machine or select Publish to Web service if you want to publish on Web service.
- Browse the path by clicking on three dots[…] where you want to save the script file on your machine, or leave it as default.
- Click on Next button.
NOTE:
If you click on the Next button without selecting the Types of data to script as (Data only, Schema and data, or Schema only) by clicking on Advanced button then only the Schema will be generated.
Step-VI:
- To generate the script for Data only or Schema and data, rather than Schema only then click on Advanced button as shown in the below image.
- Advanced Scripting Options window will open. Scroll down the horizontal scroll bar to Types of data to script and choose Data only or Schema and data or Schema only for which you want to generate script.
- Click on OK button.
- Click on Next button from Generate and Publish Scripts window.
Step-VII:
Next is the Summary which you can review your selections and then click on Next button.
Step-VIII:
Database script will be generated as per your selections. Click on Finish button.
To deploy your script on the SQL Server destination instance, just open the script file in query editor window and execute the script.
