Tool to Deploy SQL Server Database Changes

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


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.


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

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.

    Generate Script

  2. In the Introduction window, press Next.

  3. In the Choose Objects window, select "Script the entire database and all database objects".

    Choose 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".

    Set Scripting Options

  5. In the Summary window, press Next.


  6. In the Save or Publish Scripts window, press Finish.

    Save or Publish Scripts

  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"
        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. Select the SQL Server Name
  3. 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.

    Select the script

  4. Finally, verify that your database and objects were created. In this example, the test database was created.

    Verify the database created
Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Daniel Calbimonte 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

Comments For This Article

Saturday, June 28, 2014 - 10:20:24 AM - [email protected] Back To Top (32447)
so .. the title of this blog is 'Tool to Deploy SQL Server Database Changes' but you only show how to create scripts for a new database. Yepp, you also mentioned one can use hand crafted sql statements, but you really don't want to do that in a production environment, right? Why do you excplicitly state your blog deals with db changes (imho schema changes incl. data transformation fall under this topic), when you actually don't deal with this complex topic?? Why misleading people searching for professional knowledge to your site?

Tuesday, April 1, 2014 - 7:31:51 AM - Kartar Rana Back To Top (29934)

Doesn't Central Management Server server this purpose already? To deploy a script to multiple servers.

Thursday, July 5, 2012 - 9:40:02 PM - Daniel Calbimonte Back To Top (18366)

It is a installer of objects with a .sql script.

About the incopatibility errors, I left the code in order to compile in other environments. I used VS 2010

Thursday, July 5, 2012 - 12:53:03 PM - Tony Back To Top (18361)

I'm not following why one would do this. For copying objects from one instance to another, why not use Transfer Database Task in SSIS?

Thursday, July 5, 2012 - 12:21:27 PM - Patel H Back To Top (18359)

I am using windows 7.  Found that its the windows compaibility issue, as when I run after the .exe after appling Windows XP SP2 it works.  But when I try to save this compatibility applied to the .exe it doesnt let you.  So I can only run if I run troublshoot compatibility check everytime..

Thursday, July 5, 2012 - 10:23:58 AM - Patel H Back To Top (18357)

I have just installed .net 4.0 just incase but still same errors..

Thursday, July 5, 2012 - 10:06:41 AM - Patel H Back To Top (18356)

Nice idea, but when I tried to use in my sql express 2008 r2 instance, I get below file not found error:


See the end of this message for details on invoking
just-in-time (JIT) debugging instead of this dialog box.

************** Exception Text **************
System.IO.FileNotFoundException: File not found.
   at Microsoft.VisualBasic.Interaction.Shell(String PathName, AppWinStyle Style, Boolean Wait, Int32 Timeout)
   at InstallDatabase.Form1.btnCreateDatabase_Click(Object sender, EventArgs e)
   at System.Windows.Forms.Control.OnClick(EventArgs e)
   at System.Windows.Forms.Button.OnClick(EventArgs e)
   at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
   at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
   at System.Windows.Forms.Control.WndProc(Message& m)
   at System.Windows.Forms.ButtonBase.WndProc(Message& m)
   at System.Windows.Forms.Button.WndProc(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

************** Loaded Assemblies **************
    Assembly Version:
    Win32 Version: 4.0.30319.269 (RTMGDR.030319-2600)
    CodeBase: file:///C:/Windows/Microsoft.NET/Framework/v4.0.30319/mscorlib.dll
    Assembly Version:
    Win32 Version:
    CodeBase: file:///C:/Users/patelh/Desktop/2673_InstallDatabaseCompiled/InstallDatabase.exe
    Assembly Version:
    Win32 Version: 10.0.30319.1 built by: RTMRel
    CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/Microsoft.VisualBasic/v4.0_10.0.0.0__b03f5f7f11d50a3a/Microsoft.VisualBasic.dll
    Assembly Version:
    Win32 Version: 4.0.30319.269 built by: RTMGDR
    CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System/v4.0_4.0.0.0__b77a5c561934e089/System.dll
    Assembly Version:
    Win32 Version: 4.0.30319.233 built by: RTMGDR
    CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Core/v4.0_4.0.0.0__b77a5c561934e089/System.Core.dll
    Assembly Version:
    Win32 Version: 4.0.30319.278 built by: RTMGDR
    CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Windows.Forms/v4.0_4.0.0.0__b77a5c561934e089/System.Windows.Forms.dll
    Assembly Version:
    Win32 Version: 4.0.30319.282 built by: RTMGDR
    CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Drawing/v4.0_4.0.0.0__b03f5f7f11d50a3a/System.Drawing.dll
    Assembly Version:
    Win32 Version: 4.0.30319.1 (RTMRel.030319-0100)
    CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Configuration/v4.0_4.0.0.0__b03f5f7f11d50a3a/System.Configuration.dll
    Assembly Version:
    Win32 Version: 4.0.30319.233 built by: RTMGDR
    CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Xml/v4.0_4.0.0.0__b77a5c561934e089/System.Xml.dll
    Assembly Version:
    Win32 Version: 4.0.30319.1 (RTMRel.030319-0100)
    CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Runtime.Remoting/v4.0_4.0.0.0__b77a5c561934e089/System.Runtime.Remoting.dll

************** JIT Debugging **************
To enable just-in-time (JIT) debugging, the .config file for this
application or computer (machine.config) must have the
jitDebugging value set in the section.
The application must also be compiled with debugging

For example:

    < jitDebugging="true" />

When JIT debugging is enabled, any unhandled exception
will be sent to the JIT debugger registered on the computer
rather than be handled by this dialog box.


get free sql tips
agree to terms