Creating Databases

You add a database using SQL Enterprise Manager or the CREATE DATABASE statement. This chapter describes the use of SQL Enterprise Manager. For information about using CREATE DATABASE and other statements, see the Microsoft SQL Server Transact-SQL Reference.

Creating a database allocates storage space for the database on a device. When you create a database, a transaction log is also created to record all transactions performed on that database. You should store the transaction log on a different device from the database to simplify recovery and to improve performance. If you do not specify a different device, the log is created as part of the database.

    To create a database

1. From the Server Manager window, select a server; then from the toolbar, choose the Manage Databases button.

   

   The Manage Databases window appears.

   

2. Choose the New Database button.

   

   The New Database window appears. A default device is automatically listed in the Data Device box. A graph lists the devices for that server and shows the size and available space for each.

   

3. In the Name box, type a name for the database.

   The name must comply with the SQL Server rules for identifiers. For information about these rules, see the Microsoft SQL Server Transact-SQL Reference.

4. From the list in the Data Device box, select a database device on which to place the data. Or to create a new device for this database, select <new> and complete the New Database Device dialog box that appears.

   When you open the New Database window, the Data Device box is automatically filled with the name of the first (sorted alphabetically) device. If you do not change the entry, that device is used.

   Refer to the graph at the bottom of the dialog box for a display of the devices on the server and the available space on each.

5. In the Data Device Size box, type the amount of space, in megabytes, to allocate on that device for the database.

   By default, the maximum space available on the selected device is entered in this box. The legal values for Data Device Size range from 1 MB through 2 (23) MB. If the size you request is unavailable, SQL Enterprise Manager displays a message showing how much space is available.

6. From the list in the Log Device box, select a device on which to place the transaction log. Or to create a new device for this transaction log, select <new> and complete the New Database Device dialog box that appears.

   When you open the New Database window, the Log Device box shows (none) selected. Unless you are creating very small, noncritical databases, you should always place the transaction log on a separate device from its database. This improves performance and allows you to keep backups of the log.

   Refer to the graph at the bottom of the dialog box for a display of the devices on the server and the available space on each.

7. In the Log Device Size box, type the amount of space, in megabytes, to allocate on that device for the transaction log.

   By default, the maximum space available on the selected device is entered in this box. In general, you should allocate from 10 to 25 percent of your database size for the transaction log. If the size you request is unavailable, SQL Enterprise Manager displays a message telling you how much space is available.

8. Optionally, if you will be loading a backup into this database before using it, choose the Create for Load option.

   This results in more rapid database creation. With this option the pages are not zero-initialized when the database is created. However, users other than DBO cannot access the database until a backup is loaded into the database using SQL Enterprise Manager or the LOAD statement, and the database option DBO Use Only is cleared (off). For more information about this option, see the discussion of CREATE DATABASE FOR LOAD in the Microsoft SQL Server Transact-SQL Reference.

9. Choose OK.

   The database is added.

Note Each time you create a database, you should back up the master database. This makes recovery easier and safer in case master is later damaged. For information, see Chapter 12, Backing Up and Restoring.

When a database is created and the space allocation is specified for the database devices, SQL Server initializes every bit in the database devices to 0 (except for the allocation page for each 256-page block). However, if a device has not previously contained data, then the device will not be reinitialized, and database creation time is significantly reduced.

A database can occupy different amounts of space in each of several database devices. For information about extending the database across other devices, see Expanding or Shrinking Databases, later in this chapter.

Databases can also be created by using the CREATE DATABASE statement. The sp_helpdevice system procedure can be used to display a list of available devices that a new database can be placed on. For information, see the Microsoft SQL Server Transact-SQL Reference.

The creation of a new database is recorded in the sysdatabases table. Entries are also made in sysusages, which keeps track of the space allocated to databases.