Create a database in SQL Server by using SQL Server Management Studio or Transact-SQL
Method 1 –
To create a database, using: Using SQL Server Management Studio
- In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.
- 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.
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,
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.
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.
- Method 2 –
To create a database, Using Transact-SQL
1. Connect to the Database Engine.
2. From the Standard bar, click New Query.
3.
Copy and paste the following example into the query window and
click Execute. This example creates
the database Sales. Because the keyword
PRIMARY is not used, the first file (Sales_dat) becomes the primary file. Because neither MB nor KB is
specified in the SIZE parameter for the Sales_dat file, it uses MB and is allocated in megabytes. 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\Sales_dat.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH =
5 )
LOG ON
( NAME = Sales_log,
FILENAME = 'C:\Program Files\Microsoft SQL
Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\Sales_log.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH =
5MB ) ;
GO
Limitations
and Restrictions
- A
maximum of 32,767 databases can be specified on an instance of SQL Server.
Recommendations
- 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.
Permissions
- Requires CREATE DATABASE permission in the master database, or
requires CREATE ANY DATABASE, or ALTER ANY DATABASE permission.
- To maintain control over disk use on an instance of SQL Server,
permission to create databases is typically limited to a few login accounts.