Sql Server系列：資料庫操作

libingql發表於2014-11-07
SQLServer資料庫

1 建立資料庫

1.1 CREATE DATABASE語法

CREATE DATABASE database_name 
[ ON 
      [ PRIMARY ] <filespec> [ ,...n ] 
      [ , <filegroup> [ ,...n ] ] 
      [ LOG ON <filespec> [ ,...n ] ] 
] 
[ COLLATE collation_name ]
<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 | % ] ]
)
}

　　ON：用來定義資料庫的資料檔案。PRIMARY指出其後所定義的檔案是主資料檔案，如果省略，則第一個定義的檔案是主資料檔案。

　　LOG ON：用來定義資料庫的日誌檔案。如果沒有LOG ON，SQL Server將自動建立一個日誌檔案。

　　資料庫中的檔案型別與推薦副檔名：主要資料檔案.mdf ，次要資料檔案.ndf ，事務日誌.ldf 。

1.2 建立未指定檔案的資料庫

-- Drop the database if it already exists
IF  EXISTS (
    SELECT name 
        FROM sys.databases 
        WHERE name = N'Portal'
)
DROP DATABASE Portal
GO

CREATE DATABASE Portal
GO

1.3 建立指定資料檔案和事務日誌檔案的資料庫

CREATE DATABASE [Portal]
 ON  PRIMARY 
(
    NAME = N'Portal',
    FILENAME = N'F:\Database\Portal.mdf' ,
    SIZE = 5MB ,
    FILEGROWTH = 1MB
 )
 LOG ON
(
    NAME = N'Portal_log',
    FILENAME = N'F:\Database\Portal_log.ldf' ,
    SIZE = 2MB ,
    FILEGROWTH = 10%
)

1.4 建立資料庫指定多個資料及事務日誌檔案

CREATE DATABASE [Portal]
 ON  PRIMARY 
(
    NAME = N'Portal',
    FILENAME = N'F:\Database\Portal.mdf' ,
    SIZE = 5MB ,
    FILEGROWTH = 1MB
 ),
 (
    NAME = N'Portal_Data_2014',
    FILENAME = N'F:\Database\Portal_Data_2014.ndf' ,
    SIZE = 5MB ,
    FILEGROWTH = 1MB
 )
 LOG ON
(
    NAME = N'Portal_log',
    FILENAME = N'F:\Database\Portal_log.ldf' ,
    SIZE = 2MB ,
    FILEGROWTH = 10%
),
(
    NAME = N'Portal_log_2014',
    FILENAME = N'F:\Database\Portal_log_2014.ldf' ,
    SIZE = 2MB ,
    FILEGROWTH = 10%
)

1.5 建立具有檔案組的資料庫

CREATE DATABASE [Portal]
 ON  PRIMARY 
(
    NAME = N'Portal',
    FILENAME = N'F:\Database\Portal.mdf' ,
    SIZE = 10MB ,
    FILEGROWTH = 1MB
 ),
 FILEGROUP [SECTION2014] 
 (
    NAME = N'Portal_Data_2014',
    FILENAME = N'F:\Database\Portal_Data_2014.ndf' ,
    SIZE = 5MB ,
    FILEGROWTH = 1MB
 )
 LOG ON
(
    NAME = N'Portal_log',
    FILENAME = N'F:\Database\Portal_log.ldf' ,
    SIZE = 2MB ,
    FILEGROWTH = 10%
)

2 修改資料庫

2.1 修改資料庫語法

ALTER DATABASE database_name 
{
    <add_or_modify_files>
  | <add_or_modify_filegroups>
}
[;]

<add_or_modify_files>::=
{
    ADD FILE <filespec> [ ,...n ] 
        [ TO FILEGROUP { filegroup_name } ]
  | ADD LOG FILE <filespec> [ ,...n ] 
  | REMOVE FILE logical_file_name 
  | MODIFY FILE <filespec>
}

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

<add_or_modify_filegroups>::=
{
    | ADD FILEGROUP filegroup_name 
        [ CONTAINS FILESTREAM | CONTAINS MEMORY_OPTIMIZED_DATA ]
    | REMOVE FILEGROUP filegroup_name 
    | MODIFY FILEGROUP filegroup_name
        { <filegroup_updatability_option>
        | DEFAULT
        | NAME = new_filegroup_name 
        }
}
<filegroup_updatability_option>::=
{
    { READONLY | READWRITE } 
    | { READ_ONLY | READ_WRITE }
}

2.2 新增檔案組

ALTER DATABASE [Portal] ADD FILEGROUP [SECTION2014]

2.3 新增檔案指定檔案組

ALTER DATABASE [Portal] ADD FILE
(
    NAME = N'Portal_Data_2014',
    FILENAME = N'F:\Database\Portal_Data_2014.ndf' ,
    SIZE = 5MB ,
    FILEGROWTH = 1MB
)
TO FILEGROUP [SECTION2014]

2.4 刪除資料庫檔案

ALTER DATABASE [Portal] REMOVE FILE Portal_Data_2014

2.5 修改資料名稱

ALTER DATABASE [Portal] MODIFY NAME = [Portal_2014]
EXEC sp_renamedb [Portal], [Portal_2014]

2.6 修改設定預設檔案組

ALTER DATABASE [Portal] MODIFY FILEGROUP [PRIMARY] DEFAULT

3. 刪除資料庫

3.1 刪除資料庫語法

DROP DATABASE { database_name | database_snapshot_name } [ ,...n ] [;]

　　示例

DROP DATABASE [Portal]

4. 分離資料庫

使用系統儲存過程sp_detach_db分離資料庫

sp_detach_db [ @dbname= ] 'database_name' 
    [ , [ @skipchecks= ] 'skipchecks' ] 
    [ , [ @keepfulltextindexfile = ] 'KeepFulltextIndexFile' ] 
EXEC sp_detach_db [Portal]

　　直接執行分離資料庫的SQL語句，可能會提示有程式（使用者）正在使用，分離失敗。要解決這個問題，先檢視哪些程式（使用者）正在使用該資料庫。

　　檢視使用者和程式

USE [master]
sp_who

　　先結束佔用資料庫的程式，再分離資料庫

USE [master]

KILL 55
KILL 56
KILL 57

EXEC sp_detach_db [Portal]

5 附加資料庫

5.1 使用CREATE DATABASE附加資料庫

CREATE DATABASE [Portal]
ON
(
    FILENAME = 'F:\Database\Portal.mdf'
)
FOR ATTACH
CREATE DATABASE [Portal]
ON
(
    FILENAME = 'F:\Database\Portal.mdf'
),
(
    FILENAME = 'F:\Database\Portal_log.ldf'
)
FOR ATTACH

5.2 使用系統儲存過程附加資料庫

EXEC sp_attach_db [Portal], 'F:\Database\Portal.mdf'
EXEC sp_attach_db [Portal], 'F:\Database\Portal.mdf', 'F:\Database\Portal_log.ldf'

6 檢視資料庫資訊

　　SQL Server中可以使用多種方式檢視資料庫資訊，例如使用目錄檢視、函式、儲存過程等。

6.1 使用目錄檢視

　　使用目錄檢視檢視資料庫基本資訊：

　　◊ sys.databse_files：檢視資料庫檔案資訊；

　　◊ sys.filegroups：檢視資料庫組資訊；

　　◊ sys.master_files：檢視資料庫檔案的基本資訊和狀態資訊；

　　◊ sys.database：資料庫和檔案目錄檢視檢視資料庫的基本資訊。

SELECT * FROM sys.databases WHERE name = 'Northwind'

6.2 sp_helpdb

　　語法

sp_helpdb [ [ @dbname= ] 'name' ]

　　示例

EXEC sp_helpdb [Portal]

相關文章