Tool to Deploy SQL Server Database Changes

By: Daniel Calbimonte   |   Comments (7)   |   Related: More > Database Administration

Problem

When deploying an application or application updates, sometimes there is a need to create or modify SQL Server database(s), tables, views, stored procedures etc...  This can be done using scripts and connecting to each instance, but what if you need to deploy to multiple servers or what if you want someone else to be able to create these objects.  In this this article I show a VB application I built to easily deploy database objects.

Solution

I created the following VB form to deploy database changes.  This could be for the creation of a database, tables, views and stored procedures or any database modification that can be scripted.

The application calls a SQL script and the script executes any valid T-SQL commands. The VB Solution can be downloaded here. It was created in Visual Studio 2010, but the code is valid in VS 2008 and 2005. It is a Windows Form Project.

As I mentioned, this application can execute any valid SQL script whether it is hand coded or scripted from SSMS.  For this example I will show how you can generate the script using SSMS.  We will script out the database creation along with database objects.  I am going to script out the database and objects for database TEST from one SQL Server instance and create this exact same database and objects on another instance of SQL Server.

Creating a SQL Script File

1. To generate the script from SSMS for a database and its objects, the database objects need to already exist. The easiest way to do this is to right click on the database, go to Tasks and select the Generate Scripts... option as shown below.
   
   
   
   
   
2. In the Introduction window, press Next.
    
   
   
   
3. In the Choose Objects window, select "Script the entire database and all database objects".
   
   
   
   
   
4. In the Output Type select "Save scripts to a specific location" and in the File name specify the path and the name of the .sql file.  For this example I am using "C:\GenerateDatabase.sql".
   
   
   
   
   
5. In the Summary window, press Next.
   
   
   
   
   
6. In the Save or Publish Scripts window, press Finish.
   
   
   
   
   
7. What we have done is created a script named GenerateDatabase.sql that has code to create a database with all of its objects.
   
   

Explanation of VB Code

Now, that we have the script, we need to execute the script using the VB Form, but before we do that let me explain the code behind the VB Form.

Imports System.Diagnostics
Public Class Form1
    Private Sub btnBrowse_Click( _
        ByVal sender As System.Object,
        ByVal e As System.EventArgs) _
        Handles btnBrowse.Click
        'This function opens a selected sql file
        Dim ofd As New OpenFileDialog
        ofd.Filter = "SQL files|*.sql"
        ofd.ShowDialog()
        txtFile.Text = ofd.FileName
    End Sub


    Private Sub ckAuthentication_CheckedChanged _
        (ByVal sender As System.Object, _
         ByVal e As System.EventArgs) _
        Handles ckAuthentication.CheckedChanged
        'Function to enable or disable username 
        'and password textboxes according to 
        'the(authentication)
        If ckAuthentication.CheckState = 1 Then
            txtUserName.Enabled = False
            txtPassword.Enabled = False
        Else
            txtUserName.Enabled = True
            txtPassword.Enabled = True
        End If
    End Sub


    Private Sub btnCreateDatabase_Click( _
        ByVal sender As System.Object, _
        ByVal e As System.EventArgs) _
        Handles btnCreateDatabase.Click
        'This function calls the sqlcmd
        Dim command As String
        If txtUserName.Enabled = False Then
            'Windows Authentication
            command = "sqlcmd -S " + txtSQLName.Text + _
            " -i " + txtFile.Text
        Else
            'SQL Authentication
            command = "sqlcmd -S " + txtSQLName.Text + _
            " -i " + txtFile.Text + " -U " + txtUserName.Text + _
            " -P " + txtPassword.Text + " -i " + txtFile.Text
        End If
        Shell(command)
        MsgBox("Database created successfully.")
    End Sub
End Class

There are 3 functions in the code.

• The btnBrowse_Click function is used to Browse the file system to select the .sql file to execute.
• The ckAuthentication_CheckedChanged function is used to specify if Windows Authentication or SQL Authentication is going to be used.
• Finally in the btnCreateDatabase_Click function we are calling SQLCMD. SQLCMD is the command line tool used to run T-SQL scripts in SQL Server. The VB code is using the shell function to call SQLCMD. SQLCMD is receiving the path of the .sql script.
  

Using the Application

1. Specify the SQL Server Name.
   


2. Set the Authentication information. In this case, I am using Windows Authentication and press the Browse button and select the .sql script that you want to execute and then click on "Create Database and tables" to run the script.
   
   
   
   
   
3. Finally, verify that your database and objects were created. In this example, the test database was created.
   
   
   

Next Steps

• The VB Solution can be downloaded here
• You can download a compiled version of the application
• Review the following tips and resources:
  • Connecting to SQL Server Using SQLCMD Utility
  • The shell function in VB.net
  • Using sqlcmd with script variables

About the author

Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and 6-time Microsoft Certified IT Professional. Daniel started his career in 2001 and has worked with SQL Server 6.0 to 2022. Daniel is a DBA as well as specializes in Business Intelligence (SSIS, SSAS, SSRS) technologies.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips