Create Database in SQL Server


Jun 12, 2012

Introduction

In this article we will take a look at how to Create Database in SQL Server using TSQL code and by using SQL Server Management Studio.

Permissions to Create Database in SQL Server

You need to be a member of DBCREATOR role to create database in SQL Server. Else you will get the below mentioned error.

Error Message

Create failed for Database ''. (Microsoft.SqlServer.Smo)
CREATE DATABASE permission denied in database 'master'. (Microsoft SQL Server, Error: 262)

Script to Grant Permission to Create Database in SQL Server

EXEC master..sp_addsrvrolemember @loginame = N'SQLServerLoginName', @rolename = N'dbcreator'
GO

Create Database in SQL Server Using SQL Server Management Studio

1. Connect to SQL Server Management Studio; right click Databases Node -> Select New Database… from the drop down menu as shown in the snippet below.

Create Database in SQL Server Using SQL Server Management Studio

2. In New Database Dialog box
   a. Enter Database Name
   b. Specify Owner as System Administrator (sa)
   c. Specify Initial Size for Data and Log file
   d. Click on “…” button to change Auto growth for Database as shown in the snippet below

Set Initial Size for Database and Enable Auto Growth SQL Server Database

3. In Change Autogrowth for MyTechMantra Database dialog box
   a. Select Enable Autogrowth check box
   b. Specify File Growth in Megabytes
   c. Leave Unrestricted File Growth option and click OK to save changes to Autogrowth settings
       for the database

Enable Auto Growth SQL Server Database

4. Finally to create database in SQL Server click OK in New Database Dialog box. Don’t make any change to Options and Filegroups pages within the New Database Dialog box.

5. In the snippet below you can see that MyTechMantra database is created successfully.

Successfully Create Database in SQL Server

Create Database in SQL Server Using TSQL Code

You need to be a member of DBCREATOR role to create a database in SQL Server.

TSQL Script to Create SQL Server Database with Default Options

Use master
GO

CREATE DATABASE MyTechMantra
GO

TSQL Script to Create SQL Server Database with Auto Growth Options for Data and Log Files

Execute the below mentioned TSQL script to create a Sample Database named MyTechMantra with Data and Log files located in “D:\MSSQL\Data\” location, Data File Growth is specified to be 100 MB and Log File Growth is specified to be 10 MB.

USE [master]
GO

CREATE DATABASE [MyTechMantra] ON  PRIMARY 
( 
	NAME = N'MyTechMantra', 
	FILENAME = N'D:\MSSQL\Data\MyTechMantra.mdf', 
	SIZE = 20480KB, 
	MAXSIZE = UNLIMITED,
	FILEGROWTH = 102400KB 
)
 LOG ON 
( 
	NAME = N'MyTechMantra_log', 
	FILENAME = N'D:\MSSQL\Data\MyTechMantra_log.ldf', 
	SIZE = 10240KB, 
	MAXSIZE = 2048GB, 
	FILEGROWTH = 10240KB
)
GO

Before executing the above script you need to manually create the following folder structure “D:\MSSQL\Data\” on your machine. Else the script will fail with the below mentioned error message.

Error Message

Msg 5133, Level 16, State 1, Line 2 Directory lookup for the file "" failed with the operating system error 3(The system cannot find the path specified.). Msg 1802, Level 16, State 1, Line 2 CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

Conclusion

In this article you have seen how to Create Database in SQL Server using SQL Server Management Studio and by using TSQL code.

Continue Free Learning...

  • Please leave below your valuable feedback for this article.
  • Feel Free to refer this article to your friends and colleagues using the below “Share this Article” option.
  • Do subscriber to our News Letter to continue your free learning.
  • Don’t forget to Like Us on Facebook and do follow us on Twitter for latest updates.

Share this Article

Geeks who read this article also read…



Follow @MyTechMantra on Twitter
We're on Facebook
Bookmark and Share

"Receive newsletters and special offers about SQL Server, BizTalk and SharePoint from MyTechMantra. We respect your privacy and you can unsubscribe at any time."