You can generate a database scripts including the schema and the data by using the Generate Scripts option available in SQL Server Management Studio (SSMS), which generates a scripts for selected database.
When you perform generate scripts tasks, a script is generated for all the tables, views, functions, stored procedures, indexes and other objects of a selected database.
It may be useful in such requirement when .bak failed to restore a database from a lower version database to an upper version due to differing versions. In this case you can use generate scripts toย generate the script of database schema and data and execute that script against the destination database.
It can also be useful when you need only single table data, you can also script a single table data using generate scripts option.
Lets see, how you can generate a scripts for entire database or specific database objects.
Following are the steps :
Open SQL Server Management Studio, connect to SQL Server Instance.
Next, expand the databases folder then select the database,ย and right click on database then point to Tasks and click on Generate Scripts..
You will see a Introduction Dialog box opens, click on Next Button.
Once you click on Next button, a choose objects dialog box opens.
Now you can select either entire database or specific objects in the database as follows:
- Script entire database and all database objects: it will generate a script for all the tables, views, stored procedure, functions and other objects in that database. By default it is set to Script entire database and all database objects.
- Specific database objects: it will generate a scripts only for the objects that you have selected.
Lets generates the scripts for entire database, click on Next button as shown below.
[Also Read: Create Database]
Now you will see, a set scripting options dialog box. You can see various options regarding where to save the script, you can chose location where you want to save you script, or can select save to clipboard or save to new query window.
In Advance option, you can do the required changes for generating a script for the selected objects.
that you can select based on your requirement.
Lets see the option Types of data to scripts, that allows you to choose a type of data to script that can be either Data only, schema only or Shema and Data, By default it is set to Schema only.ย
If you want script including both schema and data then select schema and data and click on OK button.
After that click on Next button.
Once you click on Next button, you will see a summary dialog box, here you can review your selections.
If you want to make some changes then you can click on Previous button other wise you can click on Next button.
After clicking on Next button, you will see a dialog box Save Scripts, which let you know the current status while scripting is being generated, currently it is in in progress.
Once it is completed, you will see a summary of all the completed script with status as shown below.
If you want to save this report for your reference, you can click on Save Report button also.
Finally, click on Finish button to close the wizard.ย
Now you see the generated scripts in the specified location that you provided.
You can open the scripts in SQL Server and run.
As you can see, the scripts is taken for Prod_Db,ย therefore when you execute this script a database will be created with same name Prod_Db, including all the schema and data.
If you want to change the database name then you can replace database name with new one in this scripts and execute.
Also Read..
10,671 total views, 1 views today