create database

Description

Creates a new database.

Syntax

create database database_name 
	[on {default | database_device} [= size] 
		[, database_device [= size]]...] 
	[log on database_device [= size] 
		[, database_device [= size]]...]
	[with {override | default_location = "pathname"}]
	[for {load | proxy_update}]

Parameters

database_name: is the name of the new database. It must conform to the rules for identifiers and cannot be a variable.

on: indicates a location and size for the database.

default

indicates that create database can put the new database on any default database device(s), as shown in sysdevices.status. To specify a size for the database without specifying a location, use this command:

on default = size

To change a database device’s status to “default,” use sp_diskdefault.

database_device: is the logical name of the device on which to locate the database. A database can occupy different amounts of space on each of several database devices. To add database devices to Adaptive Server, use disk init.

size: is the amount of space to allocate to the database extension. Size can be in the following unit specifiers: ‘k’ or ‘K’ (kilobytes), ‘m’ or ‘M’ (megabytes), and ‘g’ or ‘G’ (gigabytes). Sybase recommends that you always include a unit specifier. Quotes are optional if you do not include a unit specifier. However, you must use quotes if you include a unit specifier.

log on: specifies the logical name of the device for the database logs. You can specify more than one device in the log on clause.

with override: forces Adaptive Server to accept your device specifications, even if they mix data and transaction logs on the same device, thereby endangering up-to-the-minute recoverability for your database. If you attempt to mix log and data on the same device without using this clause, the create database command fails. If you mix log and data, and use with override, you are warned, but the command succeeds.

for load: invokes a streamlined version of create database that can be used only for loading a database dump. See “Using the for load option” for more information.

with default_location: specifies the storage location of new tables. If you also specify the for proxy_update clause, one proxy table for each remote table or view is automatically created from the specified location.

for proxy_update: automatically gets metadata from the remote location and creates proxy tables. You cannot use for proxy_update unless you also specify with default_location.

Examples

Example 1

Creates a database named pubs:

create database pubs

Example 2

Creates a 4MB database named pubs:

create database pubs 
on default = 4

Example 3

Creates a database named pubs with 3MB on the datadev segment and 2MB on the moredatadev segment:

create database pubs 
on datadev = 3, moredatadev = 2

Example 4

Creates a database named pubs with 3MB of data on the datadev segment and a 1MB log on the logdev segment:

create database pubs 
on datadev = 3 
log on logdev = 1

Example 5

Creates a proxy database named proxydb but does not automatically create proxy tables:

create database proxydb
with default_location
"UNITEST.pubs.dbo."

Example 6

Creates a proxy database named proxydb and automatically creates proxy tables:

create database proxydb
on default = 4
with default_location
"UNITEST.pubs2.dbo."
for proxy_update

Usage

Use create database from the master database.
You can specify the size as a float datatype, however, the size is rounded down to the nearest multiple of the allocation unit.
If the size of the database is not explicitly stated, the size is determined by the size of the model database. The minimum size that you can create a database is four allocation units.
Because Adaptive Server allocates space for databases for create database and alter database in chunks of 256 logical pages, these commands round the specified size down to the nearest multiple of allocation units.
If you do not include a unit specifier, Adaptive Server interprets the size in terms of megabytes of disk space, and this number is converted to the logical page size the server uses.
If you do not specify a location and size for a database, the default location is any default database device(s) indicated in master..sysdevices. The default size is the larger of the size of the model database or the default database size parameter in sysconfigures.

System Administrators can increase the default size by using sp_configure to change the value of default database size and restarting Adaptive Server. The default database size parameter must be at least as large as the model database. If you increase the size of the model database, the default size must also be increased.

If Adaptive Server cannot give you as much space as you want where you have requested it, it comes as close as possible, on a per-device basis, and prints a message telling how much space was allocated and where it was allocated. The maximum size of a database is system-dependent.
If a proxy database is created using:
```
create database mydb on my_device 
with default_location = "pathname" for proxy_update
```
The presence of the device name is enough to bypass size calculation, and this command may fail if the default database size (the size of the model database) isn’t large enough to contain all of the proxy tables.

To allow CIS to estimate database size, do not include any device name or other option with the command:
```
create database mydb
with default_location = "pathname" for proxy_update
```

Restrictions

Adaptive Server can manage as many as 32,767 databases.
Adaptive Server can create only one database at a time. If two database creation requests collide, one user sees this message:
```
model database in use: cannot create new database
```
Each time you allocate space on a database device with create database or alter database, that allocation represents a device fragment, and the allocation is entered as a row in sysusages.
The maximum number of named segments for a database is 32. Segments are named subsets of database devices available to a particular Adaptive Server. For more information on segments, see the System Administration Guide.

New databases are created from model

Adaptive Server creates a new database by copying the model database.
You can customize model by adding tables, stored procedures, user-defined datatypes, and other objects, and by changing database option settings. New databases inherit these objects and settings from model.
To guarantee recoverability, create database must clear every page that was not initialized when the model database was copied. This may take several minutes, depending on the size of the database and the speed of your system.

If you are creating a database to load a database dump into it, you can use the for load option to skip the page-clearing step. This makes database creation considerably faster.

Ensuring database recoverability

Back up the master database each time you create a new database. This makes recovery easier and safer if master is damaged.

If you create a database and fail to back up master, you may be able to recover the changes with disk reinit.
The with override clause allows you to mix log and data segments on a single device. However, for full recoverability, the device or devices specified in log on should be different from the physical device that stores the data. In the event of a hard disk crash, the database can be recovered from database dumps and transaction logs.

You can create a small database on a single device that is used to store both the transaction log and the data, but you must rely on the dump database command for backups.
The size of the device required for the transaction log varies according to the amount of update activity and the frequency of transaction log dumps. As a rule of thumb, allocate to the log device 10 – 25 percent of the space you allocate to the database itself. It is best to start small, since space allocated to a transaction log device cannot be reclaimed and cannot be used for storing data.

Using the for load option

You can use the for load option for recovering from media failure or for moving a database from one machine to another, if you have not added to the database with sp_addsegment. Use alter database for load to create a new database in the image of the database from which the database dump to be loaded was made. For a discussion of duplicating space allocation when loading a dump into a new database, see the System Administration Guide.

When you create a database using the for load option, you can run only the following commands in the new database before loading a database dump:
- alter database for load
- drop database
- load database
After you load the database dump into the new database, you can also use some dbcc diagnostic commands in the databases. After you issue the online database command, there are no restrictions on the commands you can use.
A database created with the for load option has a status of “don’t recover” in the output from sp_helpdb.

Getting information about databases

To get a report on a database, execute the system procedure sp_helpdb.
For a report on the space used in a database, use sp_spaceused.

Using with default_location and for proxy_update

Without the for proxy_update clause, the behavior of the with default_location clause is the same as that provided by the stored procedure sp_defaultloc — a default storage location is established for new and existing table creation, but automatic import of proxy table definitions is not done during the processing of the create database command.

If for proxy_update is specified with no default_location, an error is reported.
When a proxy database is created (using the for proxy_update option), Component Integration Services will be called upon to:
- Provide an estimate of the database size required to contain all proxy tables representing the actual tables and views found in the primary server’s database. This estimate is the number of database pages needed to contain all proxy tables and indexes. The estimate is used if no size is specified, and no database devices are specified.
- Create all proxy tables representing the actual tables and views found in the companion server’s database.
- Grant all permissions on proxy tables to public.
- Add the guest user to the proxy database.
- The database status will be set to indicate that this database ‘Is_A_Proxy’. This status is contained in master.dbo.sysdatabases.status4.

Standards

SQL92 – Compliance level: Transact-SQL extension.

Permissions

create database permission defaults to System Administrators, who can transfer it to users listed in the sysusers table of the master database. However, create database permission is often centralized in order to maintain control over database storage allocation.

If you are creating the sybsecurity database, you must be a System Security Officer.

create database permission is not included in the grant all command.