zagandesign - Fotolia

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.

There are several reasons why you might want to incorporate a database schema into a SQL Server create database script. In addition to streamlining the development of multiple databases based on the same schema, you can use a script as a backup for re-creating databases in the event of a catastrophic system failure, or to set up development and test environments, or to train database administrators and developers on working with SQL Server.

Using SQL, you can generate a single script for a full database with all of its objects -- tables, indexes, stored procedures, etc. Alternatively, separate script files can be built for a single database object or a group of objects. Below you'll find basic examples of how to write and execute scripts for creating a database and a table within the database.

Create a create database script

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.

Use a script to create a database

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.

Create a database table via a script

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

Dig Deeper on SQL Server Data Warehousing