3 4
Now that you have installed SQL Server 2000, you are ready to create user databases to hold your data. In this lesson, you will learn the mechanics of creating a user database using SQL Server Enterprise Manager and using the CREATE DATABASE Transact-SQL statement in SQL Query Analyzer. You will also learn how to use SQL Server Enterprise Manager to generate a Transact-SQL script that will re-create a database object (for example, in a disaster recovery situation).
When you create a new user database, you define it by selecting a database name that is unique to the current instance of SQL Server 2000. No other properties of a user database need be defined explicitly. The database name you choose should conform to SQL Server 2000 rules for identifiers (although this is not required). These rules state that for identifier names
Note
Additional properties that you will define frequently are the size, physical and logical filename, and physical location of both the primary data file and the initial transaction log file. You can specify multiple data files and multiple transaction log files (generally on separate disks) when you create the database (or you can add them later). You can also group data files into filegroups and change the default filegroup. Lesson 4 of this chapter covers placing database files on multiple disks, using multiple files, and creating user-defined filegroups for a database. Next, for each data file and transaction log file you create, you can specify whether the file autogrows when it runs out of space, how large each growth increment is, and the maximum size to which it can grow. Lesson 3 of this chapter covers managing database growth.
If you do not define these additional file properties, SQL Server 2000 uses default values. Table 6.1 lists the default properties for a database (in the default instance) with a database name of SelfPaced.
Table 6.1 Example Default Database Properties for Database Name SelfPaced
Database Property | Value |
Logical primary data filename | SelfPaced_Data |
Physical primary data filename | SelfPaced_Data.mdf |
Physical primary data file location | C:\Program Files\Microsoft SQL Server\Mssql\Data |
Physical size of the primary data file | The actual size of the model data file (640 KB by default) if created with a Transact-SQL script or the actual size of the model data file rounded up to the nearest whole number (1 MB) if created with SQL Server Enterprise Manager |
Physical primary data file growth properties | Autogrowth enabled, with a growth increment of 10%, and no maximum file growth size |
Logical transaction log filename | SelfPaced_Log |
Physical transaction log filename | SelfPaced_Log.ldf |
Physical transaction log file location | C:\Program Files\Microsoft SQL Server\Mssql\Data |
Physical size of the transaction logfile | The actual size of the model transaction log file (512 KB by default) if created with a Transact-SQL script or the actual size of the model transaction log file rounded up to the nearest whole number (1 MB) if created with SQL Server Enterprise Manager |
Physical transaction log file growth properties | Autogrowth enabled, with a growth increment of 10%, and no maximum file growth size |
When SQL Server 2000 creates a database, it performs this task in two steps. First it copies the model database to the primary data file to initialize the new user database and its meta data. The objects that are copied include system database objects and any user-defined database objects that have been placed in the model database (such as tables, views, stored procedures, and user-defined data types). Next SQL Server 2000 fills the rest of each data file with empty pages, except those specialized pages used to track allocation of space (such as GAMs, SGAMs, and IAMs). This primary data file must always have room to add new catalog information to the system tables. System tables are always contained in the primary data file.
Note
In addition to inheriting database objects from the model database, each new user database inherits database option settings from the model database (tempdb does not inherit database option settings). Change these settings for the model database to change the database option settings for all new databases. You can also change these settings after a user database has been created. Setting and changing database options are covered in Lesson 2 of this chapter.
Finally, by default, each new database inherits the default collation setting from the model database. The default collation for the model database is the same as for all system databases (the default collation is defined during setup). The default collation for system databases cannot be changed easily—you must have access to all scripts and information needed to recreate the user databases and their objects, all user data must be exported, all user databases must be dropped, the system databases must be rebuilt, and all user data must be reloaded. In international environments, having user databases with collation settings that are different from each other's settings and from the system databases' settings can be quite useful. To change the default collation for a new user database, specify a different collation when you create the new database. It is also possible to change the default collation after you have created a user database, loaded data, and created objects, but this is not an easy task. To change the default collation at that stage, you must first export all user data, recreate all database objects, and reload all user data.
Note
You can create a user database in two separate ways with SQL Server Enterprise Manager. The first way is by using the Create Database Wizard. The second way is to create a database directly by right-clicking Databases and then selecting New Database in the console tree. The Create Database Wizard is useful for novices, but limits the complexity of your physical database design. If you plan to use multiple disks and multiple files, you cannot use the Create Database Wizard.
The SQL Server Enterprise Manager wizards are available from the Tools menu, and are also available from any taskpad view. Figure 6.1 displays the Select Wizard dialog box, from which you can select a variety of wizards, including the Create Database Wizard.
Figure 6.1
The Select Wizard dialog box.
After you start the Create Database Wizard, you are asked to select a name for your new database in the Name The Database And Specify Its Location page. You can also choose to change the default location for the data file and the transaction log file (when you are using the Create Database Wizard, both files must be in the same location). See Figure 6.2.
Figure 6.2
Selecting a name and location for the database.
Next, in the Name The Database Files page, you define both the logical filename and the physical filename for each data file (when you are using the Create Database Wizard, both names must be the same). You can also add additional data files in the default location and can specify the initial size for each data file. The default is 1 MB. See Figure 6.3.
Figure 6.3
Naming the database files.
Next, in the Define The Database File Growth page, you specify the growth characteristics of all data files. Notice that the default is to grow each data file in increments of 10 percent and to allow unlimited growth. Notice that you cannot specify different growth characteristics for each data file using the Create Database Wizard. Lesson 3 of this chapter covers setting appropriate growth characteristics. See Figure 6.4.
Figure 6.4
Specifying the growth characteristics of data files.
Next, in the Name The Transaction Log Files page, you define both the logical filename and the physical filename for the transaction log file (when you are using the Create Database Wizard, both names must be the same). You can also add additional transaction log files in the default location and can specify the initial size for each transaction log file. The default is 1 MB. See Figure 6.5.
Figure 6.5
Naming the transaction log file.
Next, in the Define The Transaction Log File Growth page, you specify the growth characteristics of all transaction log files. Notice that the default is to grow each transaction log file in increments of 10 percent and to allow unlimited growth. Notice also that you cannot specify different growth characteristics for each transaction log file when you use the Create Database Wizard. Lesson 3 of this chapter covers setting appropriate growth characteristics. See Figure 6.6.
Figure 6.6
Specifying the growth characteristics of transaction log files.
Next, in the Completing The Create Database Wizard page, you are given the opportunity to review the selections you have made before you actually create the new database. You can click the Back button to change any parameter you want to change. Click the Finish button to create the new database. See Figure 6.7.
Figure 6.7
Reviewing the database parameters that you have selected.
After the new database is successfully created, a Create Database Wizard dialog box appears to give you the opportunity to create a maintenance plan for this new database. Click the No button. Creating a maintenance plan for a database is covered in Chapter 13.
In this practice you create a database using the Create Database Wizard in the SQL Server Enterprise Manager.
To create a database using the Create Database Wizard in SQL Server Enterprise Manager
SQL Server Enterprise Manager appears displaying the Microsoft SQL Servers and the Event Viewer (Local) console trees in the console root.
The Select Wizard dialog box appears.
The Welcome To The Create Database Wizard page appears.
The Name The Database And Specify Its Location page appears.
The Name The Database Files page appears. Notice the default logical database primary data file name and default initial size for the primary data file. Also notice that you can add additional data files, but only in the default location.
The Define The Database File Growth page appears. Notice the default settings.
The Name The Transaction Log Files page appears. Notice the default name and initial size. The default initial size is the same size as the initial data file size.
The Define The Transaction Log File Growth page appears.
The Completing The Create Database Wizard page appears.
A Create Database Wizard message box appears letting you know that the database was successfully completed.
A Create Database Wizard dialog box appears asking whether you want to create a maintenance plan for this database.
Notice that the WizardDB database appears in the list of databases.
The WizardDB Properties dialog box appears, with the General tab selected.
To create a database directly using SQL Server Enterprise Manager, right-click the Databases container in the console tree and then click New Database. The Database properties dialog box appears with the General tab selected, as in Figure 6.8. Notice that you can change the collation for this new database.
On the Data Files tab, you can create multiple data files, each with differing properties. You can also create user-defined filegroups and place secondary data files in specific filegroups. Placing database files is covered in Lesson 4 of this chapter. See Figure 6.9.
Figure 6.8
The General tab of the Database Properties dialog box.
Figure 6.9
The Data Files tab of the Database Properties dialog box.
On the Transaction Log tab, you can create multiple transaction log files, each with differing properties. See Figure 6.10.
Figure 6.10
The Transaction Log tab of the Database Properties dialog box.
In this practice you create a database directly using SQL Server Enterprise Manager.
To create a database directly using SQL Server Enterprise Manager
The Database Properties dialog box appears with the General tab selected.
Notice the default logical filename, physical name and location, initial size, filegroup, and growth properties.
Notice that the physical filename in the corresponding Location cell changes to SSEMDB_Data1_Data.mdf.
Notice the default parameters for this new data file. In particular, notice that the default name for this secondary data file ends with the .NDF suffix.
In a production system, you would not place a secondary data file on the same physical drive as the primary data file. If you have a second physical drive in your practice system, place this secondary file on that drive.
Notice the default parameters for this new transaction log file.
In the console tree, notice that the SSEMDB database appears in the list of databases.
The SSEMDB Properties dialog box appears, with the General tab selected.
The CREATE DATABASE Transact-SQL syntax for creating a user database requires only a database name. All other parameters are optional. The Transact-SQL syntax allows you to define the properties of multiple data files, create user-defined filegroups, place secondary data files in specific filegroups, and define the properties of multiple transaction log files. The CREATE DATABASE topic in SQL Server Books Online provides the complete syntax with a number of examples.
TIP
The following example, run in SQL Query Analyzer, creates a database called TSQLDB without specifying any data files or transaction log files.
CREATE DATABASE TSQLDB
The following example creates a database called TSQLDB, which consists of a 100-MB primary data file, a 500-MB secondary file, and a 250-MB transaction log file. The properties of the primary data file in this example include an initial size of 100 MB, a growth increment of 25 MB, and a maximum data file size of 250 MB. The properties of the secondary data file include an initial size of 500 MB, a growth increment of 50 MB, and a maximum data file size of 1000 MB. The properties of the transaction log file in this example include an initial size of 250 MB, a growth increment of 40 percent, and a maximum transaction log size of 500 MB.
CREATE DATABASE TSQLDB ON ( NAME = TSQLDB1 , FILENAME = 'D:\SQL_Data\TSQLDB1.mdf' , SIZE = 100 , MAXSIZE = 250 , FILEGROWTH = 25 ) , ( NAME = TSQLDB2 , FILENAME = 'E:\SQL_Data\TSQLDB2.ndf' , SIZE = 500 , MAXSIZE = 1000 , FILEGROWTH = 50 ) LOG ON ( NAME = TSQLDB_Log , FILENAME = 'F:\SQL_Log\TSQLDB2.ledf' , SIZE = 250, MAXSIZE = 500 , FILEGROWTH = 40% )
In this practice you create a database in SQL Query Analyzer using the CREATE DATABASE Transact-SQL statement in SQL Query Analyzer.
To create a database in SQL Query Analyzer using the CREATE DATABASE Transact-SQL statement
SQL Query Analyzer appears.
The Open Query File dialog box appears.
CREATE DATABASE TSQLDB ON ( NAME = TSQLDB_DATA , FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\TSQLDB.mdf' , SIZE = 10 , MAXSIZE = 25 , FILEGROWTH = 5 ) LOG ON ( NAME = TSQLDB_LOG , FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\TSQLDB.ldf' , SIZE = 4 , MAXSIZE = 12 , FILEGROWTH = 40% )
Using SQL Server Enterprise Manager, you can generate a Transact-SQL script to document existing database objects (such as databases, tables, indexes, views, users, groups, and logins). You can use these scripts to re-create any of these database objects at a later time (for disaster recovery) in the same or different database. You can generate a single script that will re-create all database objects, or create separate scripts for each object.
To generate a Transact-SQL script, right-click the object for which you want to generate a script, point to All Tasks, and then click Generate SQL Script. See Figure 6.11.
Figure 6.11
General tab in the Generate SQL Scripts dialog box.
You can select objects to be scripted. If no objects have been created within the database, no objects will appear.
Click the Formatting tab to display formatting options. Depending on the object you are scripting, you will have a variety of scripting options. By default, the script will contain the CREATE and DROP statement for each object being scripted. See Figure 6.12.
Click the Options tab to display security scripting options, table scripting options, and file options. See Figure 6.13.
Note
Figure 6.12
Formatting tab in the Generate SQL Scripts dialog box.
Figure 6.13
Options tab in the Generate SQL Scripts dialog box.
In this practice you use SQL Server Enterprise Manager to generate a Transact-SQL script to re-create the TSQLDB Database.
To generate a Transact-SQL script to re-create the TSQLDB database
Notice that the TSQLDB database does not appear in the database list. The reason is that this database was created by using a different client management tool.
Notice that the TSQLDB database now appears in the database list.
The Generate SQL Scripts - SelfPacedSQL\TSQLDB dialog box appears, with the General tab selected.
The Save As dialog box appears.
The Open Query File dialog box appears.
Notice that the script begins with a DROP DATABASE Transact-SQL statement and a CREATE DATABASE Transact-SQL statement. Notice also that it contains the database option settings for the TSQLDB database. These settings will be covered in Lesson 2 of this chapter.
You can create user databases using either SQL Server Enterprise Manager or using Transact-SQL scripts in SQL Query Analyzer. If you are a novice, you can use the Create Database Wizard in SQL Server Enterprise Manager to create simple databases. To create more complex databases, use either the direct method in SQL Server Enterprise Manager or write a Transact-SQL script for execution in SQL Query Analyzer. When you create a user database, SQL Server 2000 will use default values for all data file and transaction log properties that you do not specifically define. Finally, when you create a new user database, it inherits the system and user database objects in the model database, as well as its database option settings.