SQL Server - Create a Database

Note that this tutorial uses the 2000 edition of SQL Server (SQL Server 2000).

For the latest version, see SQL Server tutorial.

One of the first things we should look at with SQL Server/Enterprise Manager is how to create a database. After all, most of the tasks you perform with SQL Server will evolve around one or more databases.

System Databases

If you've only just installed SQL Server, you might notice that some databases have already been created. These are system and sample databases.

DatabaseTypeDescription
masterSystem databaseStores system level information such as user accounts, configuration settings, and info on all other databases.
modelSystem databaseThis database is used as a template for all other databases that are created.
msdbSystem databaseUsed by the SQL Server Agent for configuring alerts and scheduled jobs etc
tempdbSystem databaseHolds all temporary tables, temporary stored procedures, and any other temporary storage requirements generated by SQL Server.
pubsSample databaseThis database can be used for training purposes, and is based on a book publishing company.
NorthwindSample databaseThis database can be used for training purposes, and is based on a fictitious company called "Northwind Traders".

We will now create another database for our own use.

Creating a New Database

The following steps demonstrate how to create a database in SQL Server using Enterprise Manager.

  1. Right click on the "Databases" icon and select "New Database...":
    Creating a new database in SQL Server - step 1
  2. Name your database and click "OK":
    Creating a new database in SQL Server - step 2

Your New Database

You will now notice your new database appears under the "Databases" section of Enterprise Manager.

Your new database is based on the "Model" database. The Model database is a system database which is used as a template whenever a new database is created. If you use the left pane to navigate to your database and expand the tree, you will notice that your database already contains a number of objects. For example, it already contains tables, views, and stored procedures. These are system objects which provide information about the database.

Creating a new database in SQL Server - step 3

Other Options

We have just created a database using the default options. When we created the database, a "Data File" and a "Transaction Log" were created. They were created in the default location for our server.

If we'd wanted to, we could have specified a different location for these files by using the "Data Files" tab and "Transaction Log" tab. We also could have changed specifications on whether to allow the file to grow automatically (as it stores more and more data), and if so, how that growth should be managed.

Creating a new database in SQL Server - step 4