In this article we’ll learn how to generate complete database script. To generate full database script using SSMS, go to Object Explorer, right click on a database for which want to generate scripts; from the context menu under the Task sub-menu, choose the “Generate Script” option:
What we’ll cover:
On selecting the “generate Script” following screen will open
On click of the next button given on above screen you will get below screen in which you can select the object whose database script you want (Schema or with data).
Select the check box for required objects (tables/ procedure / functions names etc.).Now click of “Next button” following screen will appear where you will find “ADVANCED” button. Using this option you will get multiple ways to generate the scripts as per you requirement.
Above advance button will open the following screen
Check for Object existence:
Under this option script will be generated with if exist option in it. Means you can run such scripts multiple time and it will not give error because it will create the missing object in DB and will not do anything for the other which are already there in DB.
Types of data to Script:
Under this you will get three options.
Schema Only: Script generated under this option will only copy schema and new blank DB can get created with same structure but without any data in it.
DATA Only: Script generated under this option will create the Insert scripts of the data only, this script can be used to insert the data into DB. Please it will not create the table so table must be present prior to run this script in DB.
Schema and Data: This option is combination of above two options. Generated scripts will contain the schema script and also data script in it. So, script generated using this option is like and complete script of database (like backups etc.).You can use this script to restore the same DB again on same or some other server.
Script DROP and CREATE:
Scripts CREATE: Under this option you can generate Create scripts.
Script DROP: Under this option you can create DELETE scripts for the selected objects with in.
Scripts DROP and CREATE: This option is the combination of above two. Means generated script will first delete the object and then create it. You can opt any of the above options as per your needs.
After doing the above advanced configurations click on “OK” button and advanced setting window will close.
On main window please select “Open in new query window” option and click on NEXT button. Same is shown in below screen.
After clicking the Next button of above screen following screen will appear and it will show you the progress while generating the scripts.
On click of finish button script will get generated and open in new window. You can save this script and can use this where you want.
I hope with above detailed explanation you have gained the knowledge of SQL script generation and its various settings. For same information you can refer HERE
For more knowledge of MS SQL, you can check this section HERE
You may also like :
- SQL server Column Encryption using symmetric key.
- How to Update using Select statement in SQL Server.
- SQL Server Interview Questions and Answers.
- Import One Database to another in SQL server.
- Generate database scripts in SQL server.
- Different ways to search SQL objects in database.
- SQL CASE statement
- SQL IIF function
- QUOTENAME function in SQL
- How to Take SQL Server Database Offline/Online
- How to use SQL ORDER BY in Your Queries
- SQL Paging using OFFSET and FETCH
- Fill Factor in SQL Server
- How to Search SQL Object in Complete Database
- How to generate SQL table script with data