Get started Bring yourself up to speed with our introductory content.

How to use a SQL Server create database script to set up databases

You can create SQL Server databases manually, but knowing how to do a scripted database setup is valuable. Here are the steps involved in executing a create database script.

Although SQL Server databases can be created manually, it's also possible to perform scripted database creation...

to make the process more easily repeatable. In fact, at least some Microsoft server products use this very technique behind the scenes as part of their setup process.

It's really simple to write a SQL Server create database script without even having to use Microsoft's PowerShell scripting language and engine. You can see a sample script for creating a SQL Server database, written in Notepad, in Figure 1 below.

The first of the three lines of code checks to see if the database that we are creating currently exists. In this case, the script checks for the existence of a database named Demo beneath the master database. The second line of code creates the new database -- and, as you can see, it couldn't be simpler.

The final line of code tells SQL that this is the end of the block of instructions, and that it should execute the script and create the database. As you look at the image, you'll notice that the script's file name uses a .SQL extension. This is necessary so that SQL will recognize the script.

Database named Demo
Figure 1: This script creates a database named Demo.

To use the SQL Server create database script, you must open SQL Server Management Studio (SSMS). For the purposes of this article, I'm using SQL Server 2016, which doesn't include SSMS in its main installation routine. As a result, you need to download SSMS separately from Microsoft's website.

Once SSMS is open, click on the toolbar's Open File icon, and then select your script. You'll see the script code displayed within the SSMS window, as shown in Figure 2.

Database within SSMS
Figure 2: The create database script is displayed inside of SSMS.

In addition, a new row of icons is added to the toolbar beneath the Open File icon. Make sure the drop-down menu that now exists on the toolbar is set to Master, as shown in Figure 2. This indicates that the create database script will be run against the master database. Now, click the Execute button to execute the script.

When the execution completes, you should see a message like the one at the bottom of Figure 3, indicating that the script has executed successfully. You should also see the new database listed within the console tree. Remember, our script was designed to create a database named Demo, and you can see the newly created Demo database in the image.

Creating Demo database
Figure 3: The Demo database has been created.

Now, let's take a look at how to script the creation of a table within the database that we just created. Some sample code that you can use is shown in Figure 4 below.

The code starts by checking for the existence of a table named MyTable. If the table doesn't already exist, it is created. The new table will contain three columns: a primary key named Id, and separate name and value columns. Notice that the column definitions are separated by commas in the script.

MyTable database
Figure 4: This script creates a database table named MyTable.

We can execute the script by using the same techniques that we did for the create database script, but with one key difference. Instead of selecting Master from the drop-down menu, we need to select Demo so that the table is created within the Demo database. You can see what this looks like in Figure 5.

Script against Demo
Figure 5: This script is being run against the Demo database.

When the script is executed, the new table is created. You'll have to refresh the database by right-clicking on it and choosing the Refresh command before the new table becomes visible. Figure 6 shows the new table and its columns.

New database table
Figure 6: The script has created a new database table with three columns.

In all honesty, it probably would have been faster to create the database and its table manually. However, by using a SQL Server create database script and create table one, we've made it possible to add new versions of the database and table on an as-needed basis, and in a consistent manner.

Next Steps

Full database backups through SQL Server

User-defined server roles in SQL Server

SQL Server on Linux signals a change

This was last published in August 2017

Dig Deeper on SQL Server Data Warehousing

Join the conversation

1 comment

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

What has been your experience using scripts to create SQL Server databases?
Cancel

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close