Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Tool to Deploy SQL Server Database Changes


By:   |   Last Updated: 2012-07-05   |   Comments (7)   |   Related Tips: 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 VB.net 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.
     
    Introduction

  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.

    Summary

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


Last Updated: 2012-07-05


get scripts

next tip button



About the author
MSSQLTips author Daniel Calbimonte Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and Microsoft Certified IT Professional.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Saturday, June 28, 2014 - 10:20:24 AM - [email protected] Back To Top
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 01, 2014 - 7:31:51 AM - Kartar Rana Back To Top

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


Thursday, July 05, 2012 - 9:40:02 PM - Daniel Calbimonte Back To Top

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 05, 2012 - 12:53:03 PM - Tony Back To Top

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?

http://www.mssqltips.com/sqlservertip/2064/transfer-database-task-and-transfer-sql-server-objects-task-in-ssis/


Thursday, July 05, 2012 - 12:21:27 PM - Patel H Back To Top

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 05, 2012 - 10:23:58 AM - Patel H Back To Top

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


Thursday, July 05, 2012 - 10:06:41 AM - Patel H Back To Top

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 **************
mscorlib
    Assembly Version: 4.0.0.0
    Win32 Version: 4.0.30319.269 (RTMGDR.030319-2600)
    CodeBase: file:///C:/Windows/Microsoft.NET/Framework/v4.0.30319/mscorlib.dll
----------------------------------------
InstallDatabase
    Assembly Version: 1.0.0.0
    Win32 Version: 1.0.0.0
    CodeBase: file:///C:/Users/patelh/Desktop/2673_InstallDatabaseCompiled/InstallDatabase.exe
----------------------------------------
Microsoft.VisualBasic
    Assembly Version: 10.0.0.0
    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
----------------------------------------
System
    Assembly Version: 4.0.0.0
    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
----------------------------------------
System.Core
    Assembly Version: 4.0.0.0
    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
----------------------------------------
System.Windows.Forms
    Assembly Version: 4.0.0.0
    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
----------------------------------------
System.Drawing
    Assembly Version: 4.0.0.0
    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
----------------------------------------
System.Configuration
    Assembly Version: 4.0.0.0
    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
----------------------------------------
System.Xml
    Assembly Version: 4.0.0.0
    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
----------------------------------------
System.Runtime.Remoting
    Assembly Version: 4.0.0.0
    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 system.windows.forms section.
The application must also be compiled with debugging
enabled.

For example:

<configuration>
    <system.windows.forms jitDebugging="true" />
</configuration>

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.


 


Learn more about SQL Server tools