Generate and Publish SQL Database Scripts

This article helps to generate and publish SQL Database scripts.

There are various ways to deploy or publish a database by taking a backup and restore it or generating entire database scripts and executing. Generating and deploying the database is very simple to do. Using SQL Server Management Studio we can easily generate the scripts.

Open the SQL Server Management Studio and select a database then right-click and select Tasks followed by Generate Scripts. That will open the introduction wizard window.

SQlScripts1.jpg

You will see the wizard itself given the information of the four steps to completion:

  1. Select database objects.
  2. Specify scripting or publishing options.
  3. Review your selections.
  4. Generate scripts, then save or publish them.

Let's start with the first step, click on the "Next" button.

Select database objects

The next screen comes up, to choose objects. We need to select the database objects to scripts. There are two options:

  1. Script an entire database and all database objects.

    By selecting this option we can script an entire database, that includes database creation scripts, tables, schemas, Stored Procedures, Logins etcetera.

    SQlScripts2.jpg
     
  2. Select specific database objects.

    If we want more specific objects that need to be scripted, then we can use this option.

    SQlScripts3.jpg

    Click on the "Next" button.

Specify scripting or publishing options.

Here we can specify how the scripts should be saved or published. Save the scripts to a specified location like the Clipboard or to a new window. Even publishing to a web service is available. If we publish to a web service then we must add a new web service address along with target database information.

SQlScripts4.jpg

For Advanced Scripting Options while scripting like "Include IF NOT EXISTS = True", "Script DROP and CREATE = Script DROP and CREATE", "Script Triggers = True" etcetera then click on the "Advanced" button.

SQlScripts5.jpg

Review your selections

Click on the "Next" button and review your selections.

SQlScripts6.jpg

Generate scripts, then save or publish them

Click on the "Next" button to complete the process and save or publish the script. During this process if any of the Actions fails then the Result tab will shows the error.

SQlScripts7.jpg

That's it; based on the scripting or publishing options the script will be saved.

Hope this helped you.

Thank You!