Creating a Database

To create a database you must determine the name of the database, its owner (the user who creates the database), its size, and the files and filegroups that will be used to store it.

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

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

• Secondary

  These hold all of the data that does not fit in the primary data file. Databases need not have secondary data files if the primary file is large enough to hold all of 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 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).

Important Microsoft® SQL Server™ 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 normal 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 a property page within SQL Server Enterprise Manager to create the database.

You can create databases using Transact-SQL, SQL Server Enterprise Manager, the Create Database Wizard, or by using SQL-DMO programmatically.

After you create a database, it is recommended that you create a backup of the master database.

To create a database

To create a database using the Create Database Wizard

See Also