By: Joe Gavin | Updated: 2019-12-11 | Comments (2) | Related: > Database Design
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.
- Click File
- Choose SaveSQLQueryx.sql As…
- Choose a directory to store script in
- Give the file a meaningful name
- Save the file
- To run the script, either click the Execute button or press the press F5 key
- Click the minus on the Databases folder to collapse the dropdown
- Click the Refresh button so Management Studio will build a database list again
- Click the plus on the Databases folder to expand the dropdown and it appears
- To view the new database's properties, right click on the database name
- Click the Properties tab
- The Files tab will show us the database name, owner, logical and physical file names, file sizes and autogrowth increments we chose.
- 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
About the author
Joe Gavin is from the Greater Boston area. He started working with SQL Server and Sybase in 1998 in the financial services industry and has been a SQL Server Database Administrator for a dairy cooperative since 2011. He graduated from Northeastern University in Boston with a Bachelor of Science in Engineering Technology (BSET) degree in Computer Technology. Joe has spoken at the Boston and Providence SQL Saturday events. This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2019-12-11
