データベースの作成、変更、削除

１．目次

１．目次
２．データベースの作成
３．データベース情報の取得
４．データベースの変更
５．データベースの削除

２．データベースの作成

sysadmin, dbcreatorサーバーロールのみがデータベースを作成できる。

sysadmin, securityadminロールのメンバーは他のログインアカウントをdbcreatorロールに追加できる。

データベースのサイズをmodelデータベースより小さくすることはできない。

２．１　データベースの作成例

CREATE DATABASE Test
ON
(NAME = test,
     FILENAME = 'c:\tmp\test.mdf'
    , SIZE = 20
    , MAXSIZE = 100
    , FILEGROWTH = 5 )
LOG ON
(NAME = test_log,
     FILENAME = 'c:\tmp\testlog.ldf'
    , SIZE = 5
    , MAXSIZE = 25
    , FILEGROWTH = 5 )

デフォルトでは、トランザクションファイルサイズは、データファイルサイズの２５％。

２．２　データベースの作成構文

CREATE DATABASE database_name
[ ON
    [ < filespec > [ ,...n ] ]
    [ , < filegroup > [ ,...n ] ]
]
[ LOG ON { < filespec > [ ,...n ] } ]
[ COLLATE collation_name ]
[ FOR LOAD | FOR ATTACH ]

<filespec> ::=

[ PRIMARY ]
( [ NAME = logical_file_name , ]
    FILENAME = 'os_file_name'
    [ , SIZE = size ]
    [ , MAXSIZE = { max_size | UNLIMITED } ]
    [ , FILEGROWTH = growth_increment ] ) [ ,...n ]

<filegroup> ::=

FILEGROUP filegroup_name < filespec > [ ,...n ]

　

２．３　使用するファイル

　

ファイル	拡張子	説明
プライマリデータ ファイル	*.mdf	データベースの起動情報。SQL Server 2000では、最大３２TB。
セカンダリデータ ファイル	*.ndf	プライマリデータファイルに格納できない場合や、特定のオブジェクトを別のディスクにストアしたい場合。
トランザクションログ	*.ldf	データベースの復旧に必要となるすべての情報。データベースごとに１つ作られる。ログファイルの推奨サイズはデータベースの２５％。 物理的な障害に備えて、ログファイルはプライマリまたはセカンダリファイルとは別の物理ディスクに作成すべき。 ログファイルのサイズが上限まで達し、それ以上拡張できなくなると、トランザクションログが空になるまですべての変更が停止される。

ファイルグループを使用して、特定のオブジェクトを別の物理ディスクに分散させることができます。書くデータベースには最大２５６個のファイルグループを作成できる。ファイルグループには、プライマリファイルグループとユーザ定義（セカンダリ）の２種類がある。トランザクションログはファイルグループへ入れることができない。通常はプライマリデータファイルとプライマリファイルグループ、トランザクションログだけで十分。

２．４　データベースオプション設定

sp_dboption は、今後使われなくなる可能性があるので使わない。データベース オプションを設定するには、ALTER DATABASE を使用します。

設定	設定例
自動終了
バルクコピー
ログ切り捨て
破損ページ検出
読み取り専用
TRUEでデータベース所有者以外のユーザーは使用ができない。
シングルユーザ
自動圧縮
ANSI_NULL_DFLT_ON ANSI_NULL_DFLT_OFF
再起トリガ
連結の結果のNULL として取り扱い
カーソルの自動クローズ
カーソルの有効範囲が、ローカル
引用符で囲まれた識別子
ANSI_WARNINGS
自動的統計情報作成
自動的統計情報更新

３．データベース情報の取得

例 sp_helpdb 'pubs'

sp_helpdb [ [ @dbname= ] 'name' ]

name を指定しない場合、sp_helpdb は master.dbo.sysdatabases にあるすべてのデータベースをレポートします。

例 sp_helpfile 'pubs'

sp_helpfile [ [ @filename = ] 'name' ]

　

４．データベースの変更

ALTER DATABASE により、データベースを変更することができる。

新たに 5MB のデータ ファイルを追加する例。

ALTER DATABASE Test1
ADD FILE
(
 NAME = Test1dat2,
 FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\t1dat2.ndf',
 SIZE = 5MB,
 MAXSIZE = 100MB,
 FILEGROWTH = 5MB
)
GO
　

４．１　データベースの変更構文

ALTER DATABASE database
{ ADD FILE < filespec > [ ,...n ] [ TO FILEGROUP filegroup_name ]
| ADD LOG FILE < filespec > [ ,...n ]
| REMOVE FILE logical_file_name
| ADD FILEGROUP filegroup_name
| REMOVE FILEGROUP filegroup_name
| MODIFY FILE < filespec >
| MODIFY NAME = new_dbname
| MODIFY FILEGROUP filegroup_name {filegroup_property | NAME = new_filegroup_name }
| SET < optionspec > [ ,...n ] [ WITH < termination > ]
| COLLATE < collation_name >
}

４．２　データベースの所有者と権限の変更

EXEC sp_changedbowner 'SQLログインユーザ名' [, remap_alias_flag ]

remap_alias_flag	動作
true（規定値）	以前のデータベースの所有者の別名が割り当てられる。
false

４．３　データベース名の変更

EXEC sp_renamedb 'old_name', 'new_name'

４．４　データベースのアタッチ

EXEC sp_attach_db 'dbname', 'filename_n', ...

例1

1> sp_attach_db 'Pubs', 'F:\Data\Pubs.mdf', 'F:\Data\Pubs_log.ldf'
2> go

例２

 sp_attach_db @dbname = N'test',
     @filename1= 'c:\tmp\test.mdf',
     @filename2= 'c:\tmp\test.mdf'so

４．５　データベースのデタッチ

４．６　データベース名の変更

sp_renamedb は将来のバージョンで削除される。

ALTER DATABASE MODIFY NAME を使用する。

例

ALTER DATABASE Pubs MODIFY NAME=abc

４．７　データベースオブジェクトの表示

sp_help [ [ @objname = ] name ]

データベース オブジェクト (sys.sysobjects 互換性ビューに一覧表示されるすべてのオブジェクト)、ユーザー定義データ型、または、SQL Server 2005 で提供されるデータ型についての情報をレポートします。

例 sp_help 'authers'

４．８　データベース情報の表示

sp_helpdb [ [ @dbname= ] 'name' ]

name を指定しない場合、sp_helpdb は master.dbo.sysdatabases にあるすべてのデータベースをレポートします。

例 sp_helpdb 'pubs'

４．９　データベースに関連付けられたファイルの物理名と属性の取得

５．データベースの削除

例 DROP DATABASE test

構文

DROP DATABASE database_name [,...n]