Using the MS-SQL Server Enterprise Manager to Create a Database and Transaction Log

In Tip 41, "Using the CREATE DATABASE Statement to Create an MS-SQL Server Database and Transaction Log," you learned that MS-SQL Server lets you create multiple databases on a single server, and you also learned how to use the CREATE DATABASE statement. Like most database management tools, MS-SQL Server gives you not only a command line (SQL or Transact-SQL) statement, but also a graphical user interface (GUI) tool to perform the same function. To create a database using the MS-SQL Server Enterprise Manager, perform the following steps:

1. Click your mouse on the Start button. Windows will display the Start menu.

2. Move your mouse pointer to Programs on the Start menu, select the Microsoft SQL Server 7.0 option, and click your mouse on Enterprise Manager. Windows will start the Enterprise Manager in the SQL Server Enterprise Manager application window.

3. Click your mouse on the plus (+) to the left of SQL Server Group to display the list of MS-SQL Servers available on your network.

4. Click your mouse on the plus (+) to the left of the SQL Server on which you wish to create a database. Enterprise Manager, in turn, will display a Database Properties dialog box similar to that shown in Figure 42.2

5. Click your mouse on the Databases folder to display the list of databases currently on the server, similar to that shown in Figure 42.1.

   Figure 42.1: The SQL Server Enterprise Manager application window

6. Select the Action menu New Database option. Enterprise Manager displays a Database Properties dialog box similar to that shown in Figure 42.2.

   Figure 42.2: The Enterprise Manager Database Properties dialog box

7. Enter the name of the database in the Name field. For the current project, enter MARKETING. The Enterprise Manager will automatically fill in the pathname and initial database size in the Database Files section of the Database Properties dialog box.

   Note

   If you want to put the database in a folder other than the default folder or change the physical file name, click your mouse on the Search button in the Location field in the Database Files area of the Database Properties dialog box. Enterprise Manager will display the Locate Database File dialog box so you can select a folder or change the database's physical file name.

8. Click on the Initial size (MB) field and enter the initial size of the database file. For the current project, enter 10.

9. Set the database File Growth and Maximum File Size options. For the current project, accept the defaults, which allow the database file to grow by 10 percent each time it fills up and place no restriction on its maximum size.

10. Click on the Transaction Log tab.

11. If you want to change the pathname (the file name and physical location) of the transaction log file, click on the Search button in the Location field to work with the Locate Transaction Log File dialog box. For the current project, accept the default pathname for the transaction log.

12. Click on the Initial size (MB) field, and enter the initial size of the transaction log. For the current project, enter 3.

13. Set the database File Growth and Maximum File Size options. For the current project, accept the defaults, which allow the transaction log to grow by 10 percent each time it fills up and place no restriction on its maximum size.

14. Click on the OK button.

After you complete Step 14, the Enterprise Manager will create the database (MARKETING, in the current example) according to the options you selected and return to the SQL Server Enterprise Manager application window.

The important thing to know now is that MS-SQL Server gives you two ways to create a database. You can use the CREATE DATABASE statement or use the Enterprise Manager's Action menu New Database option. Whether you use CREATE DATABASE or the Enterprise Manager, you can set database and transaction log options that specify:

• The physical locations (pathnames) of the database and transaction log file(s)

• The initial size of the database and transaction log

• The increment by which the database and transaction log will grow

• The maximum size to which the database and transaction log file(s) can grow

If you are using CREATE DATABASE, you specify the database and transaction log properties in separate clauses within the statement. When you use the Enterprise Manager to create a database, you can still specify different properties for the database and transaction log file(s) by using the Database Properties dialog box General tab to specify database options and using the Transaction Log tab to select transaction log options.