[T-SQL] データベースを作成する。構成情報を設定、表示する。

handcraft 5/24/2009 14430 3 SQL Server

データベースの作成や構成情報の設定方法などを掲載します

動作確認は、SQL Server 2005 Developer Edition で行っています。

1. データベースを作成する

1.1 データベースを作成する方法を掲載します。

データベースを作成するには、CREATE DATABASE 文を使用します。削除する場合は、DROP DABASE 文を使用します。既定では、データベースに対して1つのデータファイルと1つのログファイルが作成されますが、複数のファイルをデータファイル、ログファイルとしてデータベースを作成することもできます。ファイルグループを指定しないとPRIMARY で指定したファイルグループに論理ファイルが含まれます。サンプルでは掲載していませんが、ファイルグループをPRIMARY以外に作成して、論理ファイルを別々のファイルグループに含めるようにすることもできます。

-- データベースを作成する
USE master
GO
-- データベースを既定のオプションで作成
CREATE DATABASE SampleDB
GO

DROP DATABASE SampleDB
GO

-- データベースファイルを複数にして作成
CREATE DATABASE SampleDB
ON PRIMARY
( NAME = 'SampleDB1',
  FILENAME = 'C:\work\SampleDB1.mdf',
  SIZE = 3MB,
  MAXSIZE = UNLIMITED,
  FILEGROWTH = 1MB
),
( NAME = 'SampleDB2',
  FILENAME = 'C:\work\SampleDB2.mdf',
  SIZE = 2MB,
  MAXSIZE = UNLIMITED,
  FILEGROWTH = 10%
)
LOG ON
(
  NAME = 'SampleDB_log',
  FILENAME = 'C:\work\SampleDB2.ldf',
  SIZE = 1MB,
  MAXSIZE = 2GB,
  FILEGROWTH = 10%
)
GO

DROP DATABASE SampleDB
GO

1.2 データベース情報を表示する

データベースの構成情報を表示するにはsys.databaseカタログビューを使用します。sp_helpdb ストアドプロシージャでもデータベースのステータス情報、設定情報や、ファイル情報を調べることができます。データベースの互換性レベルを設定するには、sp_dbcmptlevel ストアドプロシージャを使用します。sp_dbcmptlevel ストアドプロシージャで互換性レベルの表示、設定を行えます。

-- データベース情報を表示する
-- ステータスや、互換性レベル、サイズ
-- データベースファイルの情報を表示できる
EXEC sp_helpdb 'SampleDB'
GO

-- データベースの互換性レベルの設定、変更を行う
EXEC sp_dbcmptlevel 'SampleDB' 

-- データベースのステータス情報を表示
select *
 from  sys.databases

1.3 データベースのアクセスモードを変更する

データベースレベルの変更を加える場合に、シングルユーザのみや特定の権限を持つユーザにのみデータベースにアクセスできるようにするには、ALTER DATABASE 文で SINGLE_USER, RESTRICTED_USER, MULTI_USERを指定します。WITH 句でROLLBACK IMMEDIATE, NOWAIT等を指定して、実行中のトランザクションをロールバックする方法を指定できます。

-- データベースへのアクセスモードを変更する
-- シングルユーザ、制限ユーザ、マルチユーザ
-- に設定できる。

-- データベースをシングルユーザモードにする
-- 実行中のトランザクションはすぐにロールバックされる
ALTER DATABASE SampleDB
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE

-- アクセスモード確認
select name, user_access_desc
 from sys.databases

-- 待機時間無しでRESTRICTED_USER
-- に変更。ほかに接続中にセッション
-- があれば、処理に失敗する
ALTER DATABASE SampleDB
SET RESTRICTED_USER
WITH NO_WAIT

-- 10秒後に実行中のトランザクション
-- がロールバックされ,マルチユーザ
-- モードになります
ALTER DATABASE SampleDB
SET MULTI_USER
WITH ROLLBACK AFTER 10

-- データベース名をリネームする
ALTER DATABASE SampleDB
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE

ALTER DATABASE SampleDB
MODIFY NAME = SampleDB2

ALTER DATABASE SampleDB2
SET MULTI_USER
WITH ROLLBACK IMMEDIATE

1.4 データベースをデタッチ/アタッチする

データベースをデタッチするにはsp_detach_db プロシージャを使用し、アタッチする場合にはCREATE DATABASE文を使用します。アタッチした場合、SQL Server ログイン情報は復元しないので、データベースユーザとログインユーザをマッピングする処理をしたり、再作成する必要があります。

-- データベースをデタッチ/アタッチする
EXEC sp_detach_db @dbname='SampleDB2', @skipchecks='false', @KeepFulltextIndexFile='false'

-- データベースをアタッチします
-- ATTACH_REBUILD_LOGを指定すると1 つ以上のトランザクションログ
-- ファイルが見つからない場合、ログ ファイルは再構築されます
CREATE DATABASE SampleDB
 ON (FILENAME = 'C:\work\SampleDB1.mdf'),
    (FILENAME = 'C:\work\SampleDB2.mdf'),
    (FILENAME = 'C:\work\SampleDB2.ldf')
FOR ATTACH_REBUILD_LOG

DROP DATABASE SampleDB

アタッチすると、データベースユーザとログインユーザのマッピングは失われるので、 sp_change_users_login ストアドを利用してマッピングを復元してください。詳細は、[T-SQL] SQLサーバログインユーザ,データベースユーザを作成するの"4. データベースユーザを作成する"の内容を参照してください。

2. データベースの構成情報を表示、設定する

データベースの設定されているオプションを参照するには、sys.databases カタログビューを使用します。オプション設定する場合は、ALTER DATABASE SET 文を使用します。

設定できるオプション項目はたくさんあります。詳細はBooks OnlineのALTER DATABASE の SET オプションを参照してください。

-- データベースオプションを表示する
-- 各列の詳細場Books Online 参照
SELECT name, database_id, source_database_id, compatibility_level, collation_name, user_access_desc, is_read_only, 
  FROM sys.databases

-- データベース オプションの設定を変更する
-- 指定可能なSQLオプションはたくさんあります。次のリンクを参照
-- [ALTER DATABASE の SET オプション<http://msdn.microsoft.com/ja-jp/library/bb522682.aspx>]参照
--   SETで指定できるSQLオプション例
-- ANSI SQL オプション
--   ANSI_NULL_DEFAULT { ON | OFF } 
--   ANSI_NULLS { ON | OFF } 
--   ANSI_PADDING { ON | OFF } 
--   ANSI_WARNINGS { ON | OFF } 
--   ARITHABORT { ON | OFF } 
--   COMPATIBILITY_LEVEL = { 80 | 90 | 100 }
--   CONCAT_NULL_YIELDS_NULL { ON | OFF } 
--   NUMERIC_ROUNDABORT { ON | OFF } 
--   QUOTED_IDENTIFIER { ON | OFF } 
--   RECURSIVE_TRIGGERS { ON | OFF } 
-- 
--   AUTO_CLOSE { ON | OFF } 
--   AUTO_CREATE_STATISTICS { ON | OFF } 
--   AUTO_SHRINK { ON | OFF } 
--   AUTO_UPDATE_STATISTICS { ON | OFF } 
--   AUTO_UPDATE_STATISTICS_ASYNC { ON | OFF }
ALTER DATABASE SampleDB
  SET ANSI_NULLS ON

-- リカバリモデルをシンプルにする
ALTER DATABASE SampleDB
SET RECOVERY SIMPLE

GO

3. まとめ

説明は以上です。

// Net planet-es V2