Lesson 1: Creating a User Database

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).


After this lesson, you will be able to

  • Understand the process of creating a user database
  • Create a user database using SQL Server Enterprise Manager
  • Create a user database using the CREATE DATABASE Transact-SQL statement in SQL Query Analyzer
  • Create a Transact-SQL script of a database object using SQL Server Enterprise Manager

Estimated lesson time: 45 minutes


Creating a User Database

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

  • The first character must begin with a letter, the underscore (_), the "at" sign (@), which signifies a local variable or parameter, or the number sign (#), which signifies a temporary table or procedure.
  • Subsequent characters in the name can also include numbers and the dollar sign ($).
  • Embedded spaces and special characters cannot be included.
  • A SQL Server 2000 reserved keyword in uppercase or lowercase (examples: BACKUP or PLAN) cannot be used.

Note


If an identifier does not follow these rules, you must reference the identifier using double quotation marks or brackets (for example, sp_helpdb "My Database" or sp_helpdb [My Database] if you use a database name that includes a space).

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


The tempdb database is re-created each time you start SQL Server 2000 (using the model database meta data).

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 must be a member of the sysadmin or dbcreator role (or be specifically granted the permission) to create a user database. Permissions are covered in Chapters 10 and 11.

Using SQL Server Enterprise Manager to Create a User Database

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.

Using 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.

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.

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.

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.

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.

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.

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.

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.

Practice: Creating a Database Using the Create Database Wizard in SQL Server Enterprise Manager

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

  1. Ensure that you are logged on to the SelfPacedSQL.MSFT domain controller as Administrator.
  2. Click Start, point to Programs, point to Microsoft SQL Server, and then click Enterprise Manager.

    SQL Server Enterprise Manager appears displaying the Microsoft SQL Servers and the Event Viewer (Local) console trees in the console root.

  3. In the console tree, expand the Microsoft SQL Servers container, expand the SQL Server Group container, and then click the default instance.
  4. On the Tools menu, click Wizards.

    The Select Wizard dialog box appears.

  5. Expand Database and then double-click Create Database Wizard.

    The Welcome To The Create Database Wizard page appears.

  6. Click Next.

    The Name The Database And Specify Its Location page appears.

  7. In the Database Name text box, type WizardDB.
  8. Review the default locations for the database file and the transaction log file, and then click Next.

    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.

  9. In the Initial Size (MB) text box, type 10, and then click Next.

    The Define The Database File Growth page appears. Notice the default settings.

  10. In the Grow The Files By Percent: spin box, type 25.
  11. Click the Restrict File Growth To MB: option button, and then in the Restrict File Growth To MB: spin box, type 30.
  12. Click Next.

    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.

  13. In the Initial Size (MB) text box, type 4 and then click Next.

    The Define The Transaction Log File Growth page appears.

  14. Click the Grow The Files In Megabytes (MB): option button, and then in the Grow The Files In Megabytes (MB): spin box, type 3.
  15. Click the Restrict File Growth To MB: option button, and then in the Restrict File Growth To MB: spin box, type 15.
  16. Click Next.

    The Completing The Create Database Wizard page appears.

  17. Click the Finish button.

    A Create Database Wizard message box appears letting you know that the database was successfully completed.

  18. Click OK.

    A Create Database Wizard dialog box appears asking whether you want to create a maintenance plan for this database.

  19. Click the No button.
  20. In the SQL Server Enterprise Manager console tree, expand your default instance and then expand Databases.

    Notice that the WizardDB database appears in the list of databases.

  21. Right-click WizardDB, and then click Properties.

    The WizardDB Properties dialog box appears, with the General tab selected.

  22. Click the Data Files tab and review the properties of the data file for the WizardDB database.
  23. Click the Transaction Log tab and review the properties of the transaction log file for WizardDB database.
  24. Click Cancel to close the WizardDB Properties dialog box.
  25. Do not close SQL Server Enterprise Manager.

Using SQL Server Enterprise Manager Directly

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.8

The General tab of the Database Properties dialog box.

 figure 6.9 - the data files 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.

Figure 6.10

The Transaction Log tab of the Database Properties dialog box.

Practice: Creating a Database Directly Using SQL Server Enterprise Manager

In this practice you create a database directly using SQL Server Enterprise Manager.

To create a database directly using SQL Server Enterprise Manager

  1. In the SQL Server Enterprise Manager console tree, expand the Microsoft SQL Servers container, expand the SQL Server Group container, and then expand the default instance.
  2. Right-click Databases and then click New Database.

    The Database Properties dialog box appears with the General tab selected.

  3. In the Name text box, type SSEMDB and then click the Data Files tab.

    Notice the default logical filename, physical name and location, initial size, filegroup, and growth properties.

  4. In the File Name column, overwrite the default primary filename by typing SSEMDB_Data1 in the first cell of the column.

    Notice that the physical filename in the corresponding Location cell changes to SSEMDB_Data1_Data.mdf.

  5. In the corresponding Initial Size (MB) cell, type 50.
  6. In the File Growth group box, type 15 in the By Percent: spin box.
  7. In the Maximum File Size group box, click the Restrict File Growth (MB): option button, and then in the Restrict File Growth (MB): spin box, type 150.
  8. In the File Name column, click the second line and then type SSEMDB_Data2.

    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.

  9. Change the physical location for this secondary data file to C:\SSEMDB_Data2_Data.ndf.

    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.

  10. In the corresponding Initial Size (MB) cell, type 50.
  11. In the File Growth group box, type 15 in the By Percent: box.
  12. In the Maximum File Size group box, click the Restrict File Growth (MB): option button, and then in the Restrict File Growth (MB): spin box, type 100.
  13. Click the Transaction Log tab.

    Notice the default parameters for this new transaction log file.

  14. In the Initial Size (MB) cell, type 20 to replace the default parameter.
  15. In the File Growth group box, type 25 in the By Percent: spin box.
  16. In the Maximum File Size group box, click the Restrict File Growth (MB): option button, and then in the Restrict File Growth (MB): spin box, type 75.
  17. Click OK to create the new database.

    In the console tree, notice that the SSEMDB database appears in the list of databases.

  18. Right-click SSEMDB, and then click Properties.

    The SSEMDB Properties dialog box appears, with the General tab selected.

  19. Click the Data Files tab and review the properties of the data files for the SSEMDB database.
  20. Click the Transaction Log tab and review the properties of the transaction log file for the SSEMDB database.
  21. Click Cancel to close the SSEMDB Properties dialog box.
  22. Do not close SQL Server Enterprise Manager.

Using the CREATE DATABASE Transact-SQL Statement to Create a User Database

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


When you are first learning a new Transact-SQL command, use the examples in SQL Server Books Online to help decipher the syntax. This is frequently the easiest way to learn a new command.

Creating a Simple Database Using Transact-SQL

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 

Creating a Multiple File Database Using Transact-SQL

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% ) 

Practice: Creating a Database in SQL Query Analyzer Using the CREATE DATABASE Transact-SQL Statement

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

  1. On the SQL Server Enterprise Manager Tools menu, click SQL Query Analyzer.

    SQL Query Analyzer appears.

  2. On the toolbar, click the Load SQL Script button or press Ctrl+Shift+P.

    The Open Query File dialog box appears.

  3. Browse to the C:\SelfPacedSQL\CH_6 folder and open the CreateDB.sql script.
  4. Review this script. The script is shown below.
    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% ) 
  5. Execute the CreateDB.sql script.
  6. Notice that the results pane displays the results of the creation of the TSQLDB database. The TSQLDB_DATA file was allocated 10 MB and the TSQLDB_LOG file was allocated 4 MB.
  7. On the toolbar, click the Clear Window button.
  8. In the query pane, type EXEC sp_helpdb TSQLDB and then press Ctrl+E (to execute the query) on your keyboard.
  9. Review the output in the results pane to verify the properties of the TSQLDB database.
  10. On the toolbar, click the Clear Window button.
  11. Minimize SQL Query Analyzer.

Scripting Databases and Database Objects Using SQL Server Enterprise Manager

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.

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


You can script the database schema and all database objects into a single script or create multiple scripts for multiple objects.

 figure 6.12 - formatting tab in the generate sql scripts dialog box.

Figure 6.12

Formatting tab in the Generate SQL Scripts dialog box.

 figure 6.13 - options tab in the generate sql scripts dialog box.

Figure 6.13

Options tab in the Generate SQL Scripts dialog box.

Practice: Generating a Transact-SQL Script to Re-create the TSQLDB Database

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

  1. In the SQL Server Enterprise Manager console tree, expand the Microsoft SQL Servers container, expand the SQL Server Group container, expand the default instance, and then expand Databases.

    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.

  2. Right-click Databases and then click Refresh.

    Notice that the TSQLDB database now appears in the database list.

  3. Right-click TSQLDB, point to All Tasks, and then click Generate SQL Script.

    The Generate SQL Scripts - SelfPacedSQL\TSQLDB dialog box appears, with the General tab selected.

  4. Click the Options tab.
  5. In the Security Scripting Options group box, select the Script Database check box and then click OK.

    The Save As dialog box appears.

  6. In the Save In drop-down list, change the folder to C:\SelfPacedSQL\CH_6.
  7. In the File Name text box, type TSQLDB and then click the Save button.
  8. Click OK to close the Scripting message box.
  9. Switch to SQL Query Analyzer.
  10. On the toolbar, click the Load SQL Script button.

    The Open Query File dialog box appears.

  11. Browse to the C:\SelfPacedSQL\CH_6 folder and open the TSQLDB.sql script.

    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.

  12. Close SQL Query Analyzer.

Lesson Summary

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.



MCSE Microsoft SQL Server 2000 System Administration(c) Training Kit (c) Exam 70-228
MCSE Microsoft SQL Server 2000 System Administration(c) Training Kit (c) Exam 70-228
ISBN: N/A
EAN: N/A
Year: 2001
Pages: 126

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net