建立資料庫Create a Database

適用範圍:Applies to: 是SQL ServerSQL Server (所有支援的版本) yesSQL ServerSQL Server (all supported versions) 適用範圍:Applies to: 是SQL ServerSQL Server (所有支援的版本) yesSQL ServerSQL Server (all supported versions)

此主題描述如何使用 SQL Server 2019 (15.x)SQL Server 2019 (15.x)SQL Server Management StudioSQL Server Management Studio ,在 Transact-SQLTransact-SQL中建立資料庫。This topic describes how to create a database in SQL Server 2019 (15.x)SQL Server 2019 (15.x) by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL.

注意

若要使用 T-SQL 在 Azure SQL Database 中建立資料庫,請參閱在 Azure SQL Database 中建立資料庫To create a database in Azure SQL Database using T-SQL, see Create database in Azure SQL Database.

本主題內容In This Topic

開始之前Before You Begin

限制事項Limitations and Restrictions

  • SQL ServerSQL Server的一個執行個體上,最多可以指定 32,767 個資料庫。A maximum of 32,767 databases can be specified on an instance of SQL ServerSQL Server.

必要條件Prerequisites

  • CREATE DATABASE 陳述式必須在自動認可模式 (預設交易管理模式) 下執行,而且不能用於明確或隱含的交易。The CREATE DATABASE statement must run in autocommit mode (the default transaction management mode) and is not allowed in an explicit or implicit transaction.

建議Recommendations

  • 每當建立、修改或卸除使用者資料庫時,都應該備份 master 資料庫。The master database should be backed up whenever a user database is created, modified, or dropped.

  • 當您建立資料庫時,請根據您預期之資料庫中的資料量上限,盡量使資料檔案有足夠的空間。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.

SecuritySecurity

權限Permissions

需要 master 資料庫的 CREATE DATABASE 權限,或需要 CREATE ANY DATABASE 或 ALTER ANY DATABASE 權限。Requires CREATE DATABASE permission in the master database, or requires CREATE ANY DATABASE, or ALTER ANY DATABASE permission.

為了維護 SQL ServerSQL Server執行個體的磁碟控制,通常只有少數登入帳戶有建立資料庫的權限。To maintain control over disk use on an instance of SQL ServerSQL Server, permission to create databases is typically limited to a few login accounts.

使用 SQL Server Management StudioUsing SQL Server Management Studio

若要建立資料庫To create a database

  1. [物件總管] 中,連接到 SQL Server Database EngineSQL Server Database Engine 的執行個體,然後展開該執行個體。In Object Explorer, connect to an instance of the SQL Server Database EngineSQL Server Database Engine and then expand that instance.

  2. 以滑鼠右鍵按一下 [資料庫],然後按一下 [新增資料庫]。Right-click Databases, and then click New Database.

  3. [新增資料庫] 中,輸入資料庫名稱。In New Database, enter a database name.

  4. 若要使用所有預設值來建立資料庫,請按一下 [確定] ,否則繼續執行下列選擇性步驟。To create the database by accepting all default values, click OK; otherwise, continue with the following optional steps.

  5. 若要變更擁有者名稱,請按一下 ( ) 來選取其他擁有者。To change the owner name, click (...) to select another owner.

    注意

    [使用全文檢索索引] 選項一定是核取狀態而且呈暗灰色,因為從 SQL Server 2008SQL Server 2008 開始,所有使用者資料庫都會啟用全文檢索。The Use full-text indexing option is always checked and dimmed because, beginning in SQL Server 2008SQL Server 2008, all user databases are full-text enabled.

  6. 若要變更主要資料與交易記錄檔的預設值,請在 [資料庫檔案] 方格中按一下適當的資料格,並輸入新的值。To change the default values of the primary data and transaction log files, in the Database files grid, click the appropriate cell and enter the new value. 如需詳細資訊,請參閱 將資料或記錄檔加入資料庫For more information, see Add Data or Log Files to a Database.

  7. 若要變更資料庫的定序,請選取 [選項] 頁面,然後從清單中選取定序。To change the collation of the database, select the Options page, and then select a collation from the list.

  8. 若要變更復原模式,請選取 [選項] 頁面,並從清單中選取復原模式。To change the recovery model, select the Options page and select a recovery model from the list.

  9. 若要變更資料庫選項,請選取 [選項] 頁面,然後修改資料庫選項。To change database options, select the Options page, and then modify the database options. 如需每個選項的說明,請參閱 ALTER DATABASE SET Options (Transact-SQL)For a description of each option, see ALTER DATABASE SET Options (Transact-SQL).

  10. 若要加入新的檔案群組,請按一下 [檔案群組] 頁面。To add a new filegroup, click the Filegroups page. 按一下 [加入] ,然後輸入檔案群組的值。Click Add and then enter the values for the filegroup.

  11. 若要將擴充屬性加入至資料庫,請選取 [擴充屬性] 頁面。To add an extended property to the database, select the Extended Properties page.

    1. [名稱] 資料行中,輸入擴充屬性的名稱。In the Name column, enter a name for the extended property.

    2. [值] 資料行中,輸入擴充屬性文字。In the Value column, enter the extended property text. 例如,輸入一個或多個可說明資料庫的陳述。For example, enter one or more statements that describe the database.

  12. 若要建立資料庫,請按一下 [確定]To create the database, click OK.

使用 Transact-SQLUsing Transact-SQL

若要建立資料庫To create a database

  1. 連接到 Database EngineDatabase EngineConnect to the Database EngineDatabase Engine.

  2. 在標準列中,按一下 [新增查詢]From the Standard bar, click New Query.

  3. 複製下列範例並將其貼到查詢視窗中,然後按一下 [執行]Copy and paste the following example into the query window and click Execute. 這個範例會建立 Sales資料庫。This example creates the database Sales. 因為未使用關鍵字 PRIMARY,所以第一個檔案 (Sales_dat) 會成為主要檔案。Because the keyword PRIMARY is not used, the first file (Sales_dat) becomes the primary file. 因為 Sales_dat 檔的 SIZE 參數中沒有指定 MB 或 KB,所以它會使用 MB 並 MB 來配置。Because neither MB nor KB is specified in the SIZE parameter for the Sales_dat file, it uses MB and is allocated in megabytes. 每當建立、修改或卸除使用者資料庫時,都應該備份 Sales_log 檔會以 MB 為單位配置,因為 MB 參數中明確陳述 SIZE 後置詞。The Sales_log file is allocated in megabytes because the MB suffix is explicitly stated in the SIZE parameter.

USE master ;  
GO  
CREATE DATABASE Sales  
ON   
( NAME = Sales_dat,  
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\saledat.mdf',  
    SIZE = 10,  
    MAXSIZE = 50,  
    FILEGROWTH = 5 )  
LOG ON  
( NAME = Sales_log,  
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\salelog.ldf',  
    SIZE = 5MB,  
    MAXSIZE = 25MB,  
    FILEGROWTH = 5MB ) ;  
GO  

如需範例,請參閱 CREATE DATABASE (SQL Server Transact-SQL)For more examples, see CREATE DATABASE (SQL Server Transact-SQL).

另請參閱See Also

Database Files and Filegroups Database Files and Filegroups
資料庫卸離和附加 (SQL Server) Database Detach and Attach (SQL Server)
ALTER DATABASE (Transact-SQL) ALTER DATABASE (Transact-SQL)
將資料或記錄檔加入資料庫Add Data or Log Files to a Database