Page 66 - DCAP508_DATABASE_ADMINISTRATION

Database Administration

Notes Before creating a database, consider that:
Permission to create a database defaults to members of the sysadmin and dbcreator fixed
server roles, although permissions can be granted to other users.
The user who creates the database becomes the owner of the database.
A maximum of 32,767 databases can be created on a server.

The name of the database must follow the rules for identifiers.
Three types of files are used to store a database:

Primary files: These files contain the startup information for the database. The primary
files are also used to store data. Every database has one primary file.

Secondary files: These files hold all the data that does not fit in the primary data file.
Databases do not need secondary data files if the primary file is large enough to hold all
the data in the database. Some databases may be large enough to need multiple secondary
data files, or they may use secondary files on separate disk drives to spread the data across
multiple disks.

Transaction log: These files hold the log information used to recover the database. There
must be at least one transaction log file for each database, although there may be more
than one. The minimum size for a log file is 512 kilobytes (KB).

!
Caution Microsoft® SQL Server™ 2000 data and transaction log files must not be placed on
compressed file systems or a remote network drive, such as a shared network directory.
When a database is created, all the files that comprise the database are filled with zeros to
overwrite any existing data left on the disk by previously deleted files. Although this means
that the files take longer to create, this action prevents the operating system from having to fill
the files with zeros when data is written to the files for the first time during usual database
operations. This improves the performance of day-to-day operations.

It is recommended that you specify a maximum size to which the file is permitted to grow. This
prevents the file from growing, as data is added, until disk space is exhausted. To specify a
maximum size for the file, use the MAXSIZE parameter of the CREATE DATABASE statement or
the Restrict filegrowth (MB) option when using the Properties dialog box in SQL Server Enterprise
Manager to create the database.
After you create a database, it is recommended that you create a backup of the master database.

Creating a Database

To create a database in SQL server you use either of the following tools:

1. Transact-SQL
2. Enterprise Manager
3. SQL-DMO

To create a database using the Create Database Wizard, you have to use the Enterprise
Manager

60 LOVELY PROFESSIONAL UNIVERSITY