The SQL Server database structure is defined by files and filegroups.

Files

Files are objects on the filesystem with the following file extensions:

• mdf - primary data file
• ndf - secondary data file
• ldf - transaction log file

Files used by SQL Server can be specified as

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

'NAME' is the logical name used by SQL Server. 'FILENAME' is the fullname and path to the file in the filesystem. 'SIZE' is the initial size for the physical file. 'MAXSIZE' lets you set an upperlimit for the size of the file. 'FILEGROWTH' is the value used by SQL Server to extend the file if it starts to fill-up. Internally, SQL server initialises the physical file in 8KB segments, called pages. These are combined into 64KB extents which is the smallest unit of data used for SQL Server I/O (reads and writes).

Filegroups

A filegroup is a name for a collection of files making up a database, and provides an abstraction layer from the physical details of the individual files used for the storage. There are three types of filegroups:

1. Data
2. Full-Text
3. FILESTREAM

Filegroups are specified as:

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

Transaction logs are not associated to filegroups as these are created and managed entirely by SQL Server storage engine. Filegroups can be optionally defined as either PRIMARY or DEFAULT. A PRIMARY filegroup will be used to store all system objects for the database. A DEFAULT filegroup will hold tables and indexes for tables and indexes that are not explicitly assigned to a filegroup. Defining a DEFAULT filegroup, ensures that tables and indexes don't end up being stored in the PRIMARY filegroup.

Database Definition

The simplest way to create a new database is to issue a 'CREATE DATABASE' statement via sqlcmd or as a new query in Management Studio:

CREATE DATABASE MyFirstDB
GO

This will create a new database with a primary data file and log file in the \MSSQL\DATA directory.

The CREATE DATABASE command naturally allows you to specify additional options for the creation of the database. Typically, you'll want to specify a PRIMARY and DEFAULT filegroup and specify parameters for the log file:

CREATE DATABASE MyFirstDB ON PRIMARY
(NAME = 'MyFirstDB', SIZE = 4MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10%,
	FILENAME = 'C:\DATA\MyFirstDB.mdf'),
FILEGROUP FG1 DEFAULT 
(NAME = 'MyFirstDB_DAT1', SIZE = 2MB, MAXSIZE = UNLIMITED, FILEGROWTH = 2MB,
	FILENAME = 'C:\DATA\MyFirstDB_1.ndf'), 
(NAME = 'MyFirstDB_DAT2', SIZE = 2MB, MAXSIZE = UNLIMITED, FILEGROWTH = 2MB,
	FILENAME = 'C:\DATA\MyFirstDB_2.ndf')
LOG ON 
 (NAME = 'MyFirstDB_LOG', SIZE = 2MB, MAXSIZE = UNLIMITED, FILEGROWTH = 2MB,
	FILENAME = 'C:\DATA\MyFirstDB.ldf')
GO

CREATE DATABASE MyFirstDBFS ON PRIMARY
(NAME = 'MyFirstDBFS', SIZE = 4MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10%,
	FILENAME = 'C:\DATA\MyFirstDBFS.mdf'),
FILEGROUP DocumentFilestreamGroup CONTAINS FILESTREAM
(NAME = 'FilestreamDocs', 
	FILENAME = 'C:\DATA\MyFirstDB_FS')
LOG ON 
  (NAME = 'MyFirstDBFS_LOG', SIZE = 2MB, MAXSIZE = UNLIMITED, FILEGROWTH = 2MB,
	FILENAME = 'C:\DATA\MyFirstDBFS.ldf')
GO

FILESTREAM data should be stored in a seperate database, because FILESTREAM data in incompatible with mirroring and snapshots

Right-click a database in Management Studio and select 'Properties' to review or modify the database structure.

The documented syntax for the CREATE DATABASE statement is:

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

Moving a Database

The 'ALTER DATABASE' statement can be used to move database files to different directories on the same server. Alternatively the 'Attach/Detach' stored procedures can be used to relocate database files to different directories or servers.

Detach is available from the context menu for a database in Management Studio. Detach removes the database entry from the SQL Server instance and releases all locks on the databases' files. Detach cannot be used on:

• Databases participating in replication or mirroring
• Databases that have had a Snapshot created
• System databases
• Damaged databases

The Attach command is available via the context menu for 'Databases' in the Management Studio object tree. Running the command, opens a dialogue box. To attach a database, simply click the 'Add' button and browse to the PRIMARY filegroup (.mdf file) for the database.