Tool to Deploy SQL Server Database Changes
By: Daniel Calbimonte | 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 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
- 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.
- In the Introduction window, press Next.
- In the Choose Objects window, select "Script the entire database and all database objects".
- 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".
- In the Summary window, press Next.
- In the Save or Publish Scripts window, press Finish.
- 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
- Specify the SQL Server Name.
- 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.
- Finally, verify that your database and objects were created. In this example, the test database was created.
- The VB Solution can be downloaded here
- You can download a compiled version of the application
- Review the following tips and resources:
About the author
View all my tips