The Generate and Publish Script Wizard of SQL Server helps to generate sql scripts and later on use that in other medium(s) like import to Version Control (e.g. TFS/SVN/Accurev etc.), generate scripts in some other SQL Server Instance etc. In this step by step article we will look into how to do so.
Introduction
The Generate and Publish Script Wizard of SQL Server helps to generate SQL scripts and later on use that in other medium(s) like import to Version Control (e.g. TFS/SVN/Accurev etc.), generate scripts in some other SQL Server Instance etc. In this step by step article we will look into how to do so.
Environment Setup
We have Sql Server 2014 installed in our system. However, the process will be same for SQL Server 2005/2008/2012 also.
Let us say we have a database by the name ExperimentalDB which has some Tables,SPs,Views,Functions etc.
Our objective is to publish the script. For that let us follow the below steps
Right click on the DB (ExperimentalDB) -> Tasks ->Generate Script.
The Generate and Publish Script Wizard will open
Click Next. From the Choose Objects screen, choose the Type of database objects as per the need. Here we will choose Script entire database and all database objects.
N.B.~ If we need any specific object's script, then we can go for Select specific database objects
Click Next.The Set Scripting Options screen will appear
Click Advanced Button.The last of the General Section will be Types of Data To Script which will be set to Schema Only (default).If we wish to get the Schema and Data, choose the option Schema and Data
For this article purpose, we will choose Schema and Data option. Then Click OK. And in the Set Scripting Options screen , choose Save to new query window
Click Next and we can review our selections
Click Next and we are done
Click Finish. We can figure out that the .mdf and .ldf files , the script for schema of various objects along with their data has been generated. An extracted portion of the entire script is presented below
Conclusion
Hope this will be helpful and how to generate schema and data of a database by using Generate and Publish Script Wizard of SQL Server.Thanks for reading.