Microsoft SQL Server 7.0 System Administration Training Kit
ISBN: 1572318279
EAN: 2147483647
EAN: 2147483647
Year: 1999
Pages: 100
Pages: 100
Authors: Microsoft Press
The database is the container that holds all of the database objects, including tables, views, stored procedures, rules, and defaults. As was discussed in the previous lesson, the database may consist of primary and secondary files as well as a transaction log. This lesson describes how to create a database and how to manage an existing one.
After this lesson, you will be able to
- Create a database, specifying size and automatic growth options.
- Drop databases that are no longer needed.
Estimated lesson time: 45 minutes
You can create a database using the Database Creation Wizard, SQL Server Enterprise Manager, or the CREATE DATABASE statement. In the course of creating a database you will also create a transaction log for that database.
Using sample exercises, this lesson describes these three procedures step by step and shows you how to specify the name of the database and designate the size and location of the database files. When a new database is created, it is a duplicate of the model database. Any options or settings in the model database are copied to the new database. This lesson also shows you how to drop, or delete, a database.
CAUTION
Information about each database in SQL Server is stored in the sysdatabases system table in the master database. Therefore, you should back up the master database each time you create, modify, or drop a database.
Those of you who are familiar with SQL Server 6.5 CREATE DATABASE syntax will notice significant changes in SQL Server 7. For instance, the new version does not use devices, so it is no longer necessary to create a device using the DISK INIT statement. You now specify file information as part of the CREATE DATABASE statement.
CREATE DATABASE database_name [ON { [PRIMARY] (NAME = logical_file_name, FILENAME = 'os_file_name' [, SIZE = size] [, MAXSIZE = max_size] [, FILEGROWTH = growth_increment] ) } [,...n] ] [LOG ON { ( NAME = logical_file_name, FILENAME = 'os_file_name' [, SIZE = size] ) } [,...n] ] [FOR RESTORE] |
The PRIMARY option specifies the files in the primary filegroup. The primary filegroup contains all of the database system tables. It also contains all objects not assigned to user-defined filegroups (covered later in this chapter). A database has only one primary data file. The primary data file is the starting point of the database and points to the rest of the files in the database. The recommended file extension for a primary data file is .MDF. If the PRIMARY keyword is not specified, the first file listed in the statement becomes the primary data file.
The FILENAME option specifies the operating system filename and file path. The path in os_file_name must specify a folder on a local hard drive of the server on which SQL Server is installed.
TIP
If your computer has more than one disk and you are not using Redundant Array of Inexpensive Disks (RAID), place the data file and transaction log file on separate physical disks. This increases performance and can be used, in conjunction with disk mirroring, to decrease the likelihood of data loss in case of media failure.
The SIZE option specifies the initial size of each file. You can specify sizes in megabytes using the MB suffix (the default), or in kilobytes using the KB suffix. The minimum value you can assign a file is 512 KB. If a size is not specified for the primary data file, it defaults to the size of the primary data file in the model database. If no log file is specified, a default log file is created that is either 25 percent of the total size of the data files or 512 KB, whichever is larger. If the size is not specified for secondary data files or secondary log files, they default to a size of 1 MB.
The SIZE option sets the minimum size of the file. The file can grow but cannot shrink to less than its designated minimum size. To reduce the minimum size of a file, use the DBCC SHRINKFILE statement.
The MAXSIZE option specifies the maximum size to which a file can grow. You can specify sizes in megabytes, using the MB suffix (the default), or in kilobytes using the KB suffix. If no size is specified, the file grows until the disk is full.
The FILEGROWTH option specifies the growth increment of the file. As required, SQL Server will increase a file's size by the amount specified in the FILEGROWTH option. A value of 0 indicates no growth. The value can be specified in megabytes (the default), in kilobytes, or as a percentage (%). A 10 percent default value is assigned if the FILEGROWTH option is not specified. The minimum value that can be assigned is 64 KB, and the specified size is automatically rounded to the nearest 64 KB.
The following example creates a database called sample, which has a 10-MB primary data file and a 3-MB log file. The primary data file can grow to a maximum of 15 MB in 20 percent increments. In other words, the first time it needed more space, its size would increase by 2 MB. The log file can grow to a maximum of 5 MB in 1-MB increments.
CREATE DATABASE sample ON PRIMARY ( NAME=sample_data, FILENAME='c:\mssql7\data\sample.mdf', SIZE=10MB, MAXSIZE=15MB, FILEGROWTH=20%) LOG ON ( NAME=sample_log, FILENAME='c:\mssql7\data\sample.ldf', SIZE=3MB, MAXSIZE=5MB, FILEGROWTH=1MB) |
In this exercise, you will create a database using the Create Database wizard.
| File | Initial size | Automatically grow the files | Growth size | Unrestricted file growth |
|---|---|---|---|---|
| Database | 2 MB | Yes | 2 MB | Yes |
| Log | 2 MB | Yes | 1 MB | Yes |
In this exercise, you will create a database using SQL Server Enterprise Manager.
| File | Initial size | File growth | Maximum file size |
|---|---|---|---|
| Database | 2 MB | 1 MB | 15 MB |
| Log | 1 MB | 1 MB | 5 MB |
In this exercise, you will use the CREATE DATABASE statement to create a database having the characteristics shown in the following table.
| File | Name | Filename | Initial size | File growth | Maximum file size |
|---|---|---|---|---|---|
| Database | sample_sql_data | c:\mssql7\data\sample_sql.mdf | 2 MB | 1 MB | 15 MB |
| Log | sample_sql_log | c:\mssql7\data\sample_sql.ldf | 1 MB | 1 MB | 5 MB |
CREATE DATABASE sample_sql ON PRIMARY (NAME=sample_sql_data, FILENAME='c:\mssql7\data\sample_sql.mdf', SIZE=2MB, FILEGROWTH=2MB) LOG ON (NAME=sample_sql_log, FILENAME='c:\mssql7\data\sample_sql.ldf', SIZE=1MB, MAXSIZE=10MB, FILEGROWTH=1MB) |
EXEC sp_helpdb sample_sql |
You can drop, or delete, a database by using SQL Server Enterprise Manager or by executing the DROP DATABASE statement. Note that dropping a database means that the database and the disk files used by the database are permanently deleted.
The syntax for the DROP DATABASE command is as follows:
DROP DATABASE database_name [,...n] |
The following example drops multiple databases using one statement.
DROP DATABASE mydb1, mydb2 |
Before dropping a database, consider the following facts and guidelines:
Due to restrictions, you cannot drop
Although SQL Server allows you to drop the msdb system database, you should not drop it if you use, or intend to use, any of the following:
In this exercise, you will use SQL Server Enterprise Manager to delete the sample_wizard database that you created previously.
In this exercise, you will use Transact-SQL statements to drop the sample_sql database that you created previously. You will find the script for this exercise in C:\Sqladmin\Exercise\Ch05\Dropdb.sql.
DROP DATABASE sample_sql |
EXEC sp_helpdb |
If sample_sql is still listed in the Databases folder in SQL Server Enterprise Manager, right-click the Databases folder and click Refresh. The sample_sql database will be removed from the list.
TIP
SQL Server Enterprise Manager does not automatically refresh information that has been changed outside of SQL Server Enterprise Manager. To see updated information, use the Refresh option, which is available from the right-click shortcut menu on the various folders.
Creating a database can be accomplished by using the Enterprise Manager or a script in the SQL Query Analyzer. One of the most exciting features of SQL Server 7 is the ability to let the database files grow. While this doesn't eliminate the administrator's responsibility for monitoring space usage, it does give much added flexibility in this area.