Share via

CREATE DATABASE (Transact-SQL)

  • Article

Creates a new database and the files used to store the database, creates a database snapshot, or attaches a database from the detached files of a previously created database.

Topic link iconTransact-SQL Syntax Conventions

Syntax

CREATE DATABASE database_name 
    [ ON 
        [ PRIMARY ] [ <filespec> [ ,...n ] 
        [ , <filegroup> [ ,...n ] ] 
    [ LOG ON { <filespec> [ ,...n ] } ] 
    ] 
    [ COLLATE collation_name ]
    [ WITH <external_access_option> ]
]
[;]

To attach a database
CREATE DATABASE database_name 
    ON <filespec> [ ,...n ] 
    FOR { ATTACH [ WITH <service_broker_option> ]
        | ATTACH_REBUILD_LOG }
[;]

<filespec> ::= 
{
(
    NAME = logical_file_name ,
    FILENAME = 'os_file_name' 
        [ , SIZE = size [ KB | MB | GB | TB ] ] 
        [ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ] 
        [ , FILEGROWTH = growth_increment [ KB | MB | GB | TB | % ] ]
) [ ,...n ]
}

<filegroup> ::= 
{
FILEGROUP filegroup_name [ DEFAULT ]
    <filespec> [ ,...n ]
}

<external_access_option> ::=
{
    [ DB_CHAINING { ON | OFF } ]
    [ , TRUSTWORTHY { ON | OFF } ]
}
<service_broker_option> ::=
{
    ENABLE_BROKER
  | NEW_BROKER
  | ERROR_BROKER_CONVERSATIONS
}

Create a database snapshot
CREATE DATABASE database_snapshot_name 
    ON 
        (
        NAME = logical_file_name,
        FILENAME = 'os_file_name' 
        ) [ ,...n ] 
    AS SNAPSHOT OF source_database_name
[;]

Arguments

  • database_name
    Is the name of the new database. Database names must be unique within an instance of SQL Server and comply with the rules for identifiers.

    database_name can be a maximum of 128 characters, unless a logical name is not specified for the log file. If a logical log file name is not specified, SQL Server generates the logical_file_name and the os_file_name for the log by appending a suffix to database_name. This limits database_name to 123 characters so that the generated logical file name is no more than 128 characters.

    If data file name is not specified, SQL Server uses database_name as both the logical_file_name and as the os_file_name.

  • ON
    Specifies that the disk files used to store the data sections of the database, data files, are explicitly defined. ON is required when followed by a comma-separated list of <filespec> items that define the data files for the primary filegroup. The list of files in the primary filegroup can be followed by an optional, comma-separated list of <filegroup> items that define user filegroups and their files.

  • PRIMARY
    Specifies that the associated <filespec> list defines the primary file. The first file specified in the <filespec> entry in the primary filegroup becomes the primary file. A database can have only one primary file. For more information, see Physical Database Files and Filegroups.

    If PRIMARY is not specified, the first file listed in the CREATE DATABASE statement becomes the primary file.

  • LOG ON
    Specifies that the disk files used to store the database log, log files, are explicitly defined. LOG ON is followed by a comma-separated list of <filespec> items that define the log files. If LOG ON is not specified, one log file is automatically created that has a size that is 25 percent of the sum of the sizes of all the data files for the database or 512 KB, whichever is larger. LOG ON cannot be specified on a database snapshot.

  • COLLATE collation_name
    Specifies the default collation for the database. Collation name can be either a Windows collation name or a SQL collation name. If not specified, the database is assigned the default collation of the instance of SQL Server. A collation name cannot be specified on a database snapshot.

    A collation name cannot be specified with the FOR ATTACH or FOR ATTACH_REBUILD_LOG clauses. For information about how to change the collation of an attached database, visit this Microsoft Web site.

    For more information about the Windows and SQL collation names, see COLLATE (Transact-SQL).

  • FOR ATTACH
    Specifies that the database is created by attaching an existing set of operating system files. There must be a <filespec> entry that specifies the primary file. The only other <filespec> entries required are those for any files that have a different path from when the database was first created or last attached. A <filespec> entry must be specified for these files.

    FOR ATTACH requires the following:

    • All data files (MDF and NDF) must be available.
    • If multiple log files exist, they must all be available.

    If a read/write database has a single log file that is currently unavailable, and if the database was shut down with no users or open transactions before the attach operation, FOR ATTACH automatically rebuilds the log file and updates the primary file. In contrast, for a read-only database, the log cannot be rebuilt because the primary file cannot be updated. Therefore, when you attach a read-only database whose log is unavailable, you must provide the log files or files in the FOR ATTACH clause.

    Note

    A database created by a more recent version of SQL Server cannot be attached in earlier versions.

    In SQL Server 2005, any full-text files that are part of the database that is being attached will be attached with the database. To specify a new path of the full-text catalog, specify the new location without the full-text operating system file name. For more information, see the Examples section.

    FOR ATTACH cannot be specified on a database snapshot.

    ms176061.security(en-US,SQL.90).gifSecurity Note:
    We recommend that you do not attach databases from unknown or untrusted sources. Such databases could contain malicious code that might execute unintended Transact-SQL code or cause errors by modifying the schema or the physical database structure. Before you use a database from an unknown or untrusted source, run DBCC CHECKDB on the database on a nonproduction server and also examine the code, such as stored procedures or other user-defined code, in the database.

    For more information about attaching and detaching databases, see Detaching and Attaching Databases.

    Note

    If the database uses Service Broker, also see <service_broker_option>.

    For information about file permissions that are set whenever a database is detached and attached, see Securing Data and Log Files.

    When you attach a replicated database that was copied instead of being detached, consider the following:

    • If you attach the database to the same server instance and version as the original database, no additional steps are required.
    • If you attach the database to the same server instance but with an upgraded version, you must execute sp_vupgrade_replication to upgrade replication after the attach operation is complete.
    • If you attach the database to a different server instance, regardless of version, you must execute sp_removedbreplication to remove replication after the attach operation is complete.

    Note

    Attach works with the vardecimal storage format, but the SQL Server Database Engine must be upgraded to at least SQL Server 2005 Service Pack 2. You cannot attach a compressed Service Pack 2 database to an earlier version of SQL Server. For more information about the vardecimal storage format, see Storing Decimal Data As Variable Length.

  • FOR ATTACH_REBUILD_LOG
    Specifies that the database is created by attaching an existing set of operating system files. This option is limited to read/write databases. If one or more transaction log files are missing, the log file is rebuilt. There must be a <filespec> entry specifying the primary file.

    Note

    If the log files are available, the Database Engine will use those files instead of rebuilding the log files.

    FOR ATTACH_REBUILD_LOG requires the following:

    • A clean shutdown of the database.
    • All data files (MDF and NDF) must be available.

    Important

    This operation breaks the log backup chain. We recommend that a full database backup be performed after the operation is completed. For more information, see BACKUP (Transact-SQL).

    Typically, FOR ATTACH_REBUILD_LOG is used when you copy a read/write database with a large log to another server where the copy will be used mostly, or only, for read operations, and will therefore require less log space than the original database.

    FOR ATTACH_REBUILD_LOG cannot be specified on a database snapshot.

    For more information about attaching and detaching databases, see Detaching and Attaching Databases.

  • <filespec>
    Controls the file properties.

  • NAME logical_file_name
    Specifies the logical name for the file. NAME is required when FILENAME is specified, except when specifying one of the FOR ATTACH clauses.

    • logical_file_name
      Is the logical name used in SQL Server when referencing the file. logical_file_name must be unique in the database and comply with the rules for identifiers. The name can be a character or Unicode constant, or a regular or delimited identifier.

  • FILENAME 'os_file_name'
    Specifies the operating system (physical) file name.

    • 'os_file_name'
      Is the path and file name used by the operating system when you create the file. The file must reside on one of the following devices: the local server on which SQL Server is installed, a Storage Area Network [SAN], or an iSCSI-based network. The specified path must exist before executing the CREATE DATABASE statement. For more information, see "Database Files and Filegroups" in the Remarks section.

      SIZE, MAXSIZE, and FILEGROWTH parameters cannot be set when a UNC path is specified for the file.

      If the file is on a raw partition, os_file_name must specify only the drive letter of an existing raw partition. Only one data file can be created on each raw partition.

      Data files should not be put on compressed file systems unless the files are read-only secondary files, or the database is read-only. Log files should never be put on compressed file systems. For more information, see Read-Only Filegroups and Compression.

  • SIZE size
    Specifies the size of the file.

    SIZE cannot be specified when the os_file_name is specified as a UNC path.

    • size
      Is the initial size of the file.

      When size is not supplied for the primary file, the Database Engine uses the size of the primary file in the model database. When a secondary data file or log file is specified but size is not specified for the file, the Database Engine makes the file 1 MB. The size specified for the primary file must be at least as large as the primary file of the model database.

      The kilobyte (KB), megabyte (MB), gigabyte (GB), or terabyte (TB) suffixes can be used. The default is MB. Specify a whole number; do not include a decimal. size is an integer value. For values greater than 2147483647, use larger units.

  • MAXSIZE max_size
    Specifies the maximum size to which the file can grow. MAXSIZE cannot be specified when the os_file_name is specified as a UNC path.

    • max_size
      Is the maximum file size. The KB, MB, GB, and TB suffixes can be used. The default is MB. Specify a whole number; do not include a decimal. If max_size is not specified, the file grows until the disk is full. max_size is an integer value. For values greater than 2147483647, use larger units.
  • UNLIMITED
    Specifies that the file grows until the disk is full. In SQL Server 2005, a log file specified with unlimited growth has a maximum size of 2 TB, and a data file has a maximum size of 16 TB.

  • FILEGROWTH growth_increment
    Specifies the automatic growth increment of the file. The FILEGROWTH setting for a file cannot exceed the MAXSIZE setting. FILEGROWTH cannot be specified when the os_file_name is specified as a UNC path.

    • growth_increment
      Is the amount of space added to the file every time new space is required.

      The value can be specified in MB, KB, GB, TB, or percent (%). If a number is specified without an MB, KB, or % suffix, the default is MB. When % is specified, the growth increment size is the specified percentage of the size of the file at the time the increment occurs. The size specified is rounded to the nearest 64 KB.

      A value of 0 indicates that automatic growth is off and no additional space is allowed.

      If FILEGROWTH is not specified, the default value is 1 MB for data files and 10% for log files, and the minimum value is 64 KB.

      Note

      In SQL Server 2005, the default growth increment for data files has changed from 10% to 1 MB. The log file default of 10% remains unchanged.

  • <filegroup>
    Controls the filegroup properties. Filegroup cannot be specified on a database snapshot.

  • FILEGROUP filegroup_name
    Is the logical name of the filegroup.

    • filegroup_name
      filegroup_name must be unique in the database and cannot be the system-provided names PRIMARY and PRIMARY_LOG. The name can be a character or Unicode constant, or a regular or delimited identifier. The name must comply with the rules for identifiers.
    • DEFAULT
      Specifies the named filegroup is the default filegroup in the database.

  • <external_access_option>
    Controls external access to and from the database.

    • DB_CHAINING { ON | OFF }
      When ON is specified, the database can be the source or target of a cross-database ownership chain.

      When OFF, the database cannot participate in cross-database ownership chaining. The default is OFF.

      Important

      The instance of SQL Server will recognize this setting when the cross db ownership chaining server option is 0 (OFF). When cross db ownership chaining is 1 (ON), all user databases can participate in cross-database ownership chains, regardless of the value of this option. This option is set by using sp_configure.

      To set this option, requires membership in the sysadmin fixed server role. The DB_CHAINING option cannot be set on these system databases: master, model, tempdb.

      For more information, see Ownership Chains.

    • TRUSTWORTHY { ON | OFF }
      When ON is specified, database modules (for example, views, user-defined functions, or stored procedures) that use an impersonation context can access resources outside the database.

      When OFF, database modules in an impersonation context cannot access resources outside the database. The default is OFF.

      TRUSTWORTHY is set to OFF whenever the database is attached.

      By default, all system databases except the msdb database have TRUSTWORTHY set to OFF. The value cannot be changed for the model and tempdb databases. We recommend that you never set the TRUSTWORTHY option to ON for the master database.

      To set this option, requires membership in the sysadmin fixed server role .

  • <service_broker_option>
    Controls Service Broker options on the database.

    Service Broker options can only be specified when the FOR ATTACH clause is used.

    • ENABLE_BROKER
      Specifies that Service Broker is enabled for the specified database. That is, is_broker_enabled is set to true in the sys.databases catalog view and message delivery is started.
    • NEW_BROKER
      Creates a new service_broker_guid value in both sys.databases and the restored database and ends all conversation endpoints with clean up. The broker is enabled, but no message is sent to the remote conversation endpoints.
    • ERROR_BROKER_CONVERSATIONS
      Ends all conversations with an error stating that the database is attached or restored. The broker is disabled until this operation is completed and then enabled.
  • database_snapshot_name
    Is the name of the new database snapshot. Database snapshot names must be unique within an instance of SQL Server and comply with the rules for identifiers. database_snapshot_name can be a maximum of 128 characters.

  • ON ( NAME =logical_file_name, FILENAME ='os_file_name') [ ,... n ]
    For creating a database snapshot, specifies a list of files in the source database. For the snapshot to work, all the data files must be specified individually. However, log files are not allowed for database snapshots.

    For descriptions of NAME and FILENAME and their values see the descriptions of the equivalent <filespec> values.

    Note

    When you create a database snapshot, the other <filespec> options and the keyword PRIMARY are disallowed.

  • AS SNAPSHOT OF source_database_name
    Specifies that the database being created is a database snapshot of the source database specified by source_database_name. The snapshot and source database must be on the same instance.

    For more information, see "Database Snapshots" in the Remarks section.

Remarks

The master database should be backed up whenever a user database is created, modified, or dropped.

The CREATE DATABASE statement must run in autocommit mode (the default transaction management mode) and is not allowed in an explicit or implicit transaction. For more information, see Autocommit Transactions.

You can use one CREATE DATABASE statement to create a database and the files that store the database. SQL Server implements the CREATE DATABASE statement by using the following steps:

  1. The SQL Server 2005 Database Engine uses a copy of the model database to initialize the database and its metadata.
  2. A service broker GUID is assigned to the database.
  3. The Database Engine then fills the rest of the database with empty pages, except for pages that have internal data that records how the space is used in the database. For more information, see Database File Initialization.

A maximum of 32,767 databases can be specified on an instance of SQL Server.

Each database has an owner that can perform special activities in the database. The owner is the user that creates the database. The database owner can be changed by using sp_changedbowner (Transact-SQL).

Database Files and Filegroups

Every database has at least 2 files, a primary file and a transaction log file, and at least one filegroup. A maximum of 32,767 files and 32,767 filegroups can be specified for each database. For more information, see Physical Database Files and Filegroups.

When you create a database, make the data files as large as possible based on the maximum amount of data you expect in the database. For more information, see Using Files and Filegroups to Manage Database Growth.

We recommend that you use a Storage Area Network (SAN), iSCSI-based network, or locally attached disk for the storage of your SQL Server database files, because this configuration optimizes SQL Server performance and reliability. By default, using network database files (stored on a networked server or network-attached storage) is not enabled for SQL Server. However, you can create a database that has network-based database files by using trace flag 1807. For information about this trace flag and important performance and maintenance considerations, see this Microsoft Web site.

Database Snapshots

You can use the CREATE DATABASE statement to create a read-only, static view, a database snapshot, of an existing database, the source database. A database snapshot is transactionally consistent with the source database as it existed at the time when the snapshot was created. A source database can have multiple snapshots.

Note

When you create a database snapshot, the CREATE DATABASE statement cannot reference log files, offline files, restoring files, and defunct files.

If creating a database snapshot fails, the snapshot becomes suspect and must be deleted. For more information, see DROP DATABASE (Transact-SQL).

Each snapshot persists until it is deleted by using DROP DATABASE.

For more information, see Database Snapshots.

Database Options

Several database options are automatically set whenever you create a database. For a list of these options and their default settings, see Setting Database Options. These options can be modified using the ALTER DATABASE statement.

The model Database and Creating New Databases

All user-defined objects in the model database are copied to all newly created databases. You can add any objects, such as tables, views, stored procedures, data types, and so on, to the model database to be included in all newly created databases.

When a CREATE DATABASE database_name statement is specified without additional size parameters, the primary data file is made the same size as the primary file in the model database.

Unless FOR ATTACH is specified, each new database inherits the database option settings from the model database. For example, the database option auto shrink is set to true in model and in any new databases you create. If you change the options in the model database, these new option settings are used in any new databases you create. Changing operations in the model database does not affect existing databases. If FOR ATTACH is specified on the CREATE DATABASE statement, the new database inherits the database option settings of the original database.

Viewing Database Information

You can use catalog views, system functions, and system stored procedures to return information about databases, files, and filegroups. For more information, see Viewing Database Metadata.

Permissions

Requires CREATE DATABASE, CREATE ANY DATABASE, or ALTER ANY DATABASE permission.

To maintain control over disk use on an instance of SQL Server, permission to create databases is typically limited to a few login accounts.

Permissions on Data and Log Files

In SQL Server 2005, certain permissions are set on the data and log files of each database. The following permissions are set whenever the following operations are applied to a database:

Created

Modified to add a new file

Attached

Backed up

Detached

Restored

The permissions prevent the files from being accidentally tampered with if they reside in a directory that has open permissions. For more information, see Securing Data and Log Files.

Note

Microsoft SQL Server 2005 Express Edition does not set data and log file permissions.

Examples

A. Creating a database without specifying files

The following example creates the database mytest and creates a corresponding primary and transaction log file. Because the statement has no <filespec> items, the primary database file is the size of the model database primary file. The transaction log is set to the larger of these values: 512KB or 25% the size of the primary data file. Because MAXSIZE is not specified, the files can grow to fill all available disk space.

USE master;
GO
IF DB_ID (N'mytest') IS NOT NULL
DROP DATABASE mytest;
GO
CREATE DATABASE mytest;
GO
-- Verify the database files and sizes
SELECT name, size, size*1.0/128 AS [Size in MBs] 
FROM sys.master_files
WHERE name = N'mytest';
GO

B. Creating a database that specifies the data and transaction log files

The following example creates the database Sales. Because the keyword PRIMARY is not used, the first file (Sales_dat) becomes the primary file. Because neither MB nor KB is specified in the SIZE parameter for the Sales_dat file, it uses MB and is allocated in megabytes. The Sales_log file is allocated in megabytes because the MB suffix is explicitly stated in the SIZE parameter.

USE master;
GO
IF DB_ID (N'Sales') IS NOT NULL
DROP DATABASE Sales;
GO
-- Get the SQL Server data path
DECLARE @data_path nvarchar(256);
SET @data_path = (SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
                  FROM master.sys.master_files
                  WHERE database_id = 1 AND file_id = 1);

-- execute the CREATE DATABASE statement 
EXECUTE ('CREATE DATABASE Sales
ON 
( NAME = Sales_dat,
    FILENAME = '''+ @data_path + 'saledat.mdf'',
    SIZE = 10,
    MAXSIZE = 50,
    FILEGROWTH = 5 )
LOG ON
( NAME = Sales_log,
    FILENAME = '''+ @data_path + 'salelog.ldf'',
    SIZE = 5MB,
    MAXSIZE = 25MB,
    FILEGROWTH = 5MB )'
);
GO

C. Creating a database by specifying multiple data and transaction log files

The following example creates the database Archive that has three 100-MB data files and two 100-MB transaction log files. The primary file is the first file in the list and is explicitly specified with the PRIMARY keyword. The transaction log files are specified following the LOG ON keywords. Note the extensions used for the files in the FILENAME option: .mdf is used for primary data files, .ndf is used for the secondary data files, and .ldf is used for transaction log files.

USE master;
GO
IF DB_ID (N'Archive') IS NOT NULL
DROP DATABASE Archive;
GO
-- Get the SQL Server data path
DECLARE @data_path nvarchar(256);
SET @data_path = (SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
                  FROM master.sys.master_files
                  WHERE database_id = 1 AND file_id = 1);

-- execute the CREATE DATABASE statement 
EXECUTE ('CREATE DATABASE Archive 
ON
PRIMARY  
    (NAME = Arch1,
    FILENAME = '''+ @data_path + 'archdat1.mdf'',
    SIZE = 100MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Arch2,
    FILENAME = '''+ @data_path + 'archdat2.ndf'',
    SIZE = 100MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = Arch3,
    FILENAME = '''+ @data_path + 'archdat3.ndf'',
    SIZE = 100MB,
    MAXSIZE = 200,
    FILEGROWTH = 20)
LOG ON 
   (NAME = Archlog1,
    FILENAME = '''+ @data_path + 'archlog1.ldf'',
    SIZE = 100MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
   (NAME = Archlog2,
    FILENAME = '''+ @data_path + 'archlog2.ldf'',
    SIZE = 100MB,
    MAXSIZE = 200,
    FILEGROWTH = 20)'
);
GO

D. Creating a database that has filegroups

The following example creates the database Sales that has the following filegroups:

  • The primary filegroup with the files Spri1_dat and Spri2_dat. The FILEGROWTH increments for these files is specified as 15%.
  • A filegroup named SalesGroup1 with the files SGrp1Fi1 and SGrp1Fi2.
  • A filegroup named SalesGroup2 with the files SGrp2Fi1 and SGrp2Fi2.
USE master;
GO
IF DB_ID (N'Sales') IS NOT NULL
DROP DATABASE Sales;
GO
-- Get the SQL Server data path
DECLARE @data_path nvarchar(256);
SET @data_path = (SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
                  FROM master.sys.master_files
                  WHERE database_id = 1 AND file_id = 1);

-- execute the CREATE DATABASE statement 
EXECUTE ('CREATE DATABASE  Sales
ON PRIMARY
( NAME = SPri1_dat,
    FILENAME = '''+ @data_path + 'SPri1dat.mdf'',
    SIZE = 10,
    MAXSIZE = 50,
    FILEGROWTH = 15% ),
( NAME = SPri2_dat,
    FILENAME = '''+ @data_path + 'SPri2dt.ndf'',
    SIZE = 10,
    MAXSIZE = 50,
    FILEGROWTH = 15% ),
FILEGROUP SalesGroup1
( NAME = SGrp1Fi1_dat,
    FILENAME = '''+ @data_path + 'SG1Fi1dt.ndf'',
    SIZE = 10,
    MAXSIZE = 50,
    FILEGROWTH = 5 ),
( NAME = SGrp1Fi2_dat,
    FILENAME = '''+ @data_path + 'SG1Fi2dt.ndf'',
    SIZE = 10,
    MAXSIZE = 50,
    FILEGROWTH = 5 ),
FILEGROUP SalesGroup2
( NAME = SGrp2Fi1_dat,
    FILENAME = '''+ @data_path + 'SG2Fi1dt.ndf'',
    SIZE = 10,
    MAXSIZE = 50,
    FILEGROWTH = 5 ),
( NAME = SGrp2Fi2_dat,
    FILENAME = '''+ @data_path + 'SG2Fi2dt.ndf'',
    SIZE = 10,
    MAXSIZE = 50,
    FILEGROWTH = 5 )
LOG ON
( NAME = Sales_log,
    FILENAME = '''+ @data_path + 'salelog.ldf'',
    SIZE = 5MB,
    MAXSIZE = 25MB,
    FILEGROWTH = 5MB )'
);
GO

E. Attaching a database

The following example detaches the database Archive created in example C, and then attaches it by using the FOR ATTACH clause. Archive was defined to have multiple data and log files. However, because the location of the files has not changed since they were created, only the primary file has to be specified in the FOR ATTACH clause. In SQL Server 2005, any full-text files that are part of the database that is being attached will be attached with the database.

USE master;
GO
sp_detach_db Archive;
GO
-- Get the SQL Server data path
DECLARE @data_path nvarchar(256);
SET @data_path = (SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
                  FROM master.sys.master_files
                  WHERE database_id = 1 AND file_id = 1);
-- Execute CREATE DATABASE FOR ATTACH statement
EXEC ('CREATE DATABASE Archive
      ON (FILENAME = '''+ @data_path + 'archdat1.mdf'')
      FOR ATTACH');
GO

F. Creating a database snapshot

The following example creates the database snapshot sales_snapshot0600. Because a database snapshot is read-only, a log file cannot be specified. In conformance with the syntax, every file in the source database is specified, and filegroups are not specified.

The source database for this example is the Sales database created in example D.

USE master;
GO
-- Get the SQL Server data path
DECLARE @data_path nvarchar(256);
SET @data_path = (SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
                  FROM master.sys.master_files
                  WHERE database_id = 1 AND file_id = 1);
-- execute the CREATE DATABASE statement 
EXECUTE (
'CREATE DATABASE sales_snapshot0600 ON
    ( NAME = SPri1_dat, FILENAME = '''+ @data_path + 'SPri1dat_0600.ss''),
    ( NAME = SPri2_dat, FILENAME = '''+ @data_path + 'SPri2dt_0600.ss''),
    ( NAME = SGrp1Fi1_dat, FILENAME = '''+ @data_path + 'SG1Fi1dt_0600.ss''),
    ( NAME = SGrp1Fi2_dat, FILENAME = '''+ @data_path + 'SG1Fi2dt_0600.ss''),
    ( NAME = SGrp2Fi1_dat, FILENAME = '''+ @data_path + 'SG2Fi1dt_0600.ss''),
    ( NAME = SGrp2Fi2_dat, FILENAME = '''+ @data_path + 'SG2Fi2dt_0600.ss'')
AS SNAPSHOT OF Sales');
GO

G. Creating a database and specifying a collation name and options

The following example creates the database MyOptionsTest. A collation name is specified and the TRUSTYWORTHY and DB_CHAINING options are set to ON.

USE master;
GO
IF DB_ID (N'MyOptionsTest') IS NOT NULL
DROP DATABASE MyOptionsTest;
GO
CREATE DATABASE MyOptionsTest
COLLATE French_CI_AI
WITH TRUSTWORTHY ON, DB_CHAINING ON;
GO
--Verifying collation and option settings.
SELECT name, collation_name, is_trustworthy_on, is_db_chaining_on
FROM sys.databases
WHERE name = N'MyOptionsTest';
GO

H. Attaching a full-text catalog that has been moved

The following example attaches the full-text catalog AdvWksFtCat along with the AdventureWorks data and log files. In this example, the full-text catalog is moved from its default location to a new location c:\myFTCatalogs. The data and log files remain in their default locations.

USE master;
GO
--Detach the AdventureWorks database
sp_detach_db AdventureWorks;
GO
-- Physically move the full text catalog to the new location.
--Attach the AdventureWorks database and specify the new location of the full-text catalog.
CREATE DATABASE AdventureWorks ON 
    (FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf'), 
    (FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_log.ldf'),
    (FILENAME = 'c:\myFTCatalogs\AdvWksFtCat')
FOR ATTACH;
GO

See Also

Reference

ALTER DATABASE (Transact-SQL)
DROP DATABASE (Transact-SQL)
EVENTDATA (Transact-SQL)
sp_changedbowner (Transact-SQL)
sp_detach_db (Transact-SQL)
sp_removedbreplication (Transact-SQL)

Other Resources

Detaching and Attaching Databases
Understanding Databases
Database Snapshots
Moving Database Files

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

12 December 2006
New content:
  • Added a Note in the FOR ATTACH description about attaching a SQL Server 2005 Service Pack 2 database that is enabled for vardecimal storage format.
  • Added a Note to the FOR ATTACH description stating that a database created by a more recent version of SQL Server cannot be attached in earlier versions.

5 December 2005
New content:
  • Added the security note in the definition of FOR ATTACH.
Changed content:
  • Corrected information on the TRUSTWORTHY option in system databases.