SQL Server Create Database Examples

Problem

We’ve already seen how to create a SQL Server database using SQL Server Management Studio (SSMS), but what if you need a script to do it?

Solution

We’ll look at a few examples starting with the simplest and working up slightly in complexity.

Versions used here are:

• SQL Server 2017 RTM – CU17 Developer Edition installed on my laptop
• SQL Server Management Studio version 18.4

Simple Command to Create a SQL Server Database

Let’s start with the simplest possible CREATE DATABASE statement we can do. This is simply ‘CREATE DATABASE database_name’.

The following will create a database called MyDatabase with the physical files in the default file location, the login you’re using will be the owner and with all the defaults that are configured in the model database.

-- create database MyDatabase
CREATE DATABASE [MyDatabase]

That was awfully easy, but we’re probably going to want a bit more control over our configuration.

Create SQL Server Database and Specify File Locations

So next, we’ll build on the simplest statement by adding where we want our physical files located and initially sized, and the increments we want to automatically grow the files by.  We’re not specifying anything for the logical file names so they will be the defaults, the logical data file is the same as the database name and the logical log file is the database name with a ‘_log’ appended to it unless. The initial physical data file size is 1024 MB and it will auto grow in increments of 256 MB. The initial log file size is 512MB and it will grow in increments of 125 MB.

-- create database MyDatabase and specify physical file locations, initial physical file sizes, and autogrowth increments
CREATE DATABASE [MyDatabase] 
    ON (NAME = N'MyDatabase', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\MyDatabase.mdf', SIZE = 1024MB, FILEGROWTH = 256MB)
LOG ON (NAME = N'MyDatabase_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\MyDatabase_log.ldf', SIZE = 512MB, FILEGROWTH = 125MB)
GO

Create a SQL Server Database and Change Database Owner

By default, unless otherwise specified, the database owner will be the login you’re executing the CREATE DATABASE statement as.

It easy to change the owner of the database to another login using an ‘ALTER AUTHORIZATION’ after the database has been created.

-- create database MyDatabase and specify physical file locations, initial physical file sizes, and autogrowth increments, change owner to sa
CREATE DATABASE [MyDatabase] 
    ON (NAME = N'MyDatabase', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\MyDatabase.mdf', SIZE = 1024MB, FILEGROWTH = 256MB)
LOG ON (NAME = N'MyDatabase_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\MyDatabase_log.ldf', SIZE = 512MB, FILEGROWTH = 125MB)
GO
 
-- change owner to sa
USE [MyDatabase]
GO
ALTER AUTHORIZATION ON DATABASE::[MyDatabase] TO [sa]
GO

Create a SQL Server Database and Recovery Model

The default recovery model for the model database is full, therefore, unless it’s been changed, any database created without the recovery model specified will also be configured for ‘full recovery’. In full recovery we would be able to back up the transaction log for point in time recovery.

In our example, MyDatabase is purely for testing so we’re not concerned with transaction log backups and we’ll want to set the recovery model to ‘simple recovery’ with an ‘ALTER DATABASE’ statement. This means the transaction log will be truncated approximately every 60 seconds when the SQL Server issues a checkpoint command in the database and won’t grow out of control. 

-- create database MyDatabase and specify physical file locations, initial physical file sizes, and autogrowth increments, change owner to sa, and set recovery model to simple
 
CREATE DATABASE [MyDatabase] 
    ON (NAME = N'MyDatabase', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\MyDatabase.mdf', SIZE = 1024MB, FILEGROWTH = 256MB)
LOG ON (NAME = N'MyDatabase_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\MyDatabase_log.ldf', SIZE = 512MB, FILEGROWTH = 125MB)
GO
 
-- change owner to sa
USE [MyDatabase]
GO
ALTER AUTHORIZATION ON DATABASE::[MyDatabase] TO [sa]
GO
 
-- set recovery model to simple 
ALTER DATABASE [MyDatabase] SET RECOVERY SIMPLE 
GO

Create a SQL Server Database and Change Compatibility Level

We can also set the database compatibility level to a lower version of SQL Server. Here, we’ll set the database compatibility level to 110 (SQL Server 2012).

-- create database MyDatabase and specify physical file locations, initial physical file sizes, and autogrowth increments, change owner to sa, and set compatibility level to lower version
CREATE DATABASE [MyDatabase] 
    ON (NAME = N'MyDatabase', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\MyDatabase.mdf', SIZE = 1024MB, FILEGROWTH = 256MB)
LOG ON (NAME = N'MyDatabase_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\MyDatabase_log.ldf', SIZE = 512MB, FILEGROWTH = 125MB)
GO
 
-- change owner to sa
ALTER AUTHORIZATION ON DATABASE::[MyDatabase] TO [sa]
GO
 
-- set recovery model to simple 
ALTER DATABASE [MyDatabase] SET RECOVERY SIMPLE 
GO
 
-- change compatibility level
ALTER DATABASE [MyDatabase] SET COMPATIBILITY_LEVEL = 110
GO

SQL Server Create Database Script with All Options

Just to make this a bit handier, we’ve added and commented out the rest of the database options so we can use this script as a template by just uncommenting any other ALTER DATABASE statements we need.

-- create database MyDatabase and specify physical file locations, initial physical file sizes, and autogrowth increments, change owner to sa, and set compatibility level to lower version
CREATE DATABASE [MyDatabase] 
    ON (NAME = N'MyDatabase', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\MyDatabase.mdf', SIZE = 1024MB, FILEGROWTH = 256MB)
LOG ON (NAME = N'MyDatabase_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\MyDatabase_log.ldf', SIZE = 512MB, FILEGROWTH = 125MB)
GO
 
-- change owner to sa
ALTER AUTHORIZATION ON DATABASE::[MyDatabase] TO [sa]
GO
 
-- set recovery model to simple 
ALTER DATABASE [MyDatabase] SET RECOVERY SIMPLE 
GO
 
-- change compatibility level
ALTER DATABASE [MyDatabase] SET COMPATIBILITY_LEVEL = 130
GO
 
/*
ALTER DATABASE [MyDatabase] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [MyDatabase] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [MyDatabase] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [MyDatabase] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [MyDatabase] SET ARITHABORT OFF 
GO
ALTER DATABASE [MyDatabase] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [MyDatabase] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [MyDatabase] SET AUTO_CREATE_STATISTICS ON(INCREMENTAL = OFF)
GO
ALTER DATABASE [MyDatabase] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [MyDatabase] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [MyDatabase] SET CURSOR_DEFAULT GLOBAL 
GO
ALTER DATABASE [MyDatabase] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [MyDatabase] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [MyDatabase] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [MyDatabase] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [MyDatabase] SET DISABLE_BROKER 
GO
ALTER DATABASE [MyDatabase] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [MyDatabase] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [MyDatabase] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [MyDatabase] SET READ_COMMITTED_SNAPSHOT OFF 
GO
ALTER DATABASE [MyDatabase] SET READ_WRITE 
GO
ALTER DATABASE [MyDatabase] SET RECOVERY FULL 
GO
ALTER DATABASE [MyDatabase] SET MULTI_USER 
GO
ALTER DATABASE [MyDatabase] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [MyDatabase] SET TARGET_RECOVERY_TIME = 60 SECONDS 
GO
ALTER DATABASE [MyDatabase] SET DELAYED_DURABILITY = DISABLED 
GO
USE [MyDatabase]
GO
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = Off;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = Primary;
GO
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = PRIMARY;
GO
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = On;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = Primary;
GO
ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = Off;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET QUERY_OPTIMIZER_HOTFIXES = Primary;
GO
*/ 

Saving Create Database Script in SQL Server

Finally, we’ll copy and paste our SQL into a new SQL Server Management Studio Query, save it to a file, and execute it.

1. Click File
2. Choose SaveSQLQueryx.sql As…

1. Choose a directory to store script in
2. Give the file a meaningful name
3. Save the file

1. To run the script, either click the Execute button or press the press F5 key

1. Click the minus on the Databases folder to collapse the dropdown
2. Click the Refresh button so Management Studio will build a database list again

1. Click the plus on the Databases folder to expand the dropdown and it appears

1. To view the new database’s properties, right click on the database name
2. Click the Properties tab

1. The Files tab will show us the database name, owner, logical and physical file names, file sizes and autogrowth increments we chose.

1. And the Options tab shows where we changed the recovery model to Simple and Compatibility level

Next Steps

Following are some links to more detailed information on SSMS, creating and configuring databases, and recovery models:

• Create SQL Server Database using SQL Server Management Studio
• Create SQL Server Database with PowerShell
• Learn SQL Server from Microsoft’s Own Code – MSSQLTips
• SQL Server Recovery Models – MSSQLTips
• Create a Database – Microsoft Docs
• ALTER DATABASE – Microsoft Docs
• ALTER AUTHORIZATIONALTER DATABASE – Microsoft Docs