Create Database in SQL Server
Jun 12, 2012
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.
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.
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
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
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.
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.
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.
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...
Geeks who read this article also read…
- When SQL Server was last restarted?
- Change SA Password in SQL Server
- Troubleshooting SYSPOLICY_PURGE_HISTORY Job Failure in Stand Alone Instance SQL Server 2008
- Troubleshooting OLE DB Provider Microsoft.ACE.OLEDB.12.0 is not registered Error
- Troubleshooting SQL Server blocked access to procedure sp_send_dbmail
- Performance Dashboard Reports in SQL Server 2012
- Tips to Avoid Account Lockout Issues
- Encrypt Database Backups in SQL Server Using MEDIAPASSWORD Option
- Using SP_SERVER_DIAGNOSTICS Stored Procedure Quickly Gather Diagnostic Data and Health Information in SQL Server 2012
- New Features in Microsoft SQL Server 2012 for Database Administrators
- New Features in SQL Server 2012 for Database Developers
- Why an SQL Server Database from a higher version cannot be restored onto a lower version?
- How to identify if the database was upgraded from a previous version of SQL Server
- Installing SQL Server 2008 R2 on Windows Server 2008 R2
- Using Transparent Data Encryption Feature of SQL Server 2008
- New Date and Time Data Types in SQL Server 2008
- Date and Time Functions in SQL Server 2008
- Configuring Database Instant File Initialization Feature of SQL Server
- Changing the default location of SQL Server Data and Log files
- Changing Default SQL Server Backup Folder in SQL Server 2008
- How to repair a Suspect Database in SQL Server
- Steps to Rebuild System Databases in SQL Server
- How to Get Exclusive Access to SQL Server Database
- Read More SQL Server Articles…