How to Create a Database in SQL Server (CREATE DATABASE and SSMS)

One statement does the job. To create database in SQL Server, connect to the instance and run CREATE DATABASE with a name; every setting you skip is copied from the model database:

CREATE DATABASE CreateDbDemoSales;

That gave me an 8 MB data file, an 8 MB log, FULL recovery and Query Store already switched on. The rest of this page is what I learned pushing past the one-liner.

CREATE DATABASE With Defaults: What You Get From model

My test setup: SQL Server 2025 (17.0.1000.7) Standard Developer edition on Windows 11, Windows authentication, sqlcmd in VS Code. I ran each script connected to master, which is where CREATE DATABASE belongs.

The first script creates the database and lines it up against model in sys.databases:

CREATE DATABASE CreateDbDemoSales;
GO
SET NOCOUNT ON;
SELECT CAST(name AS varchar(17)) AS name,
       CAST(recovery_model_desc AS varchar(8)) AS recovery,
       compatibility_level AS compat,
       CAST(collation_name AS varchar(28)) AS collation,
       is_query_store_on AS qs_on,
       CAST(CASE WHEN owner_sid = SUSER_SID() THEN 'me'
                 ELSE SUSER_SNAME(owner_sid) END AS varchar(5)) AS owner
FROM sys.databases
WHERE name IN (N'model', N'CreateDbDemoSales');

sqlcmd printed:

name              recovery compat collation                    qs_on owner
----------------- -------- ------ ---------------------------- ----- -----
model             FULL        170 SQL_Latin1_General_CP1_CI_AS     1 sa   
CreateDbDemoSales FULL        170 SQL_Latin1_General_CP1_CI_AS     1 me
CREATE DATABASE in SQL Server 2025 copies FULL recovery, compatibility level 170, collation and Query Store from model
The new database matches model column for column, except the owner

What this tells me: the new row is a copy of model. Compatibility level 170 is the SQL Server 2025 level, and qs_on = 1 means Query Store is on by default, which started with SQL Server 2022.

The owner is the one difference. It’s whoever ran the statement (my login, printed as me here), while model belongs to sa.

FULL recovery is the trap. Once you take the first full backup, the log keeps growing until you back it up too. For a dev database, switch to SIMPLE; for production, plan the log backups in SQL Server.

Where Did SQL Server Put the Files? Default Location and Size

Nothing in the short statement says where the files go. The instance’s default paths decide, and sys.master_files shows the result:

SET NOCOUNT ON;
SELECT CAST(SERVERPROPERTY('InstanceDefaultDataPath') AS varchar(70)) AS default_data_path;
SELECT CAST(DB_NAME(database_id) AS varchar(17)) AS db,
       CAST(RIGHT(physical_name, CHARINDEX('\', REVERSE(physical_name)) - 1)
            AS varchar(25)) AS file_name,
       size / 128 AS size_mb,
       growth / 128 AS growth_mb,
       max_size
FROM sys.master_files
WHERE database_id IN (DB_ID(N'model'), DB_ID(N'CreateDbDemoSales'));

Output:

default_data_path                                                     
----------------------------------------------------------------------
C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\ 
db                file_name                 size_mb     growth_mb   max_size   
----------------- ------------------------- ----------- ----------- -----------
model             model.mdf                           8          64          -1
model             modellog.ldf                        8          64          -1
CreateDbDemoSales CreateDbDemoSales.mdf               8          64          -1
CreateDbDemoSales CreateDbDemoSales_log.ldf           8          64   268435456

Both files landed in the instance’s DATA folder at 8 MB with 64 MB autogrowth. The data file grows until the disk is full (max_size = -1). The new log stops at 268,435,456 pages (2 TB), though model’s own log shows -1.

One surprise: the CREATE DATABASE reference says a missing LOG ON gets 25% of the data files or 512 KB. I got 8 MB, the size of model’s log, so model wins when it’s bigger.

Once the database fills up, here’s how to check database size in SQL Server.

Create a Database With a File Location, Initial Size and Autogrowth

For anything real, I name the files, pick the folder and size them for the data I expect (Microsoft’s advice is to make data files as large as you can predict). ON PRIMARY describes the data file, LOG ON the log:

CREATE DATABASE CreateDbDemoApp
ON PRIMARY
    (NAME = CreateDbDemoApp_data,
     FILENAME = 'C:\DatabaseFAQsDemo\sscreatedb\data\CreateDbDemoApp.mdf',
     SIZE = 128MB, MAXSIZE = 10GB, FILEGROWTH = 64MB)
LOG ON
    (NAME = CreateDbDemoApp_log,
     FILENAME = 'C:\DatabaseFAQsDemo\sscreatedb\data\CreateDbDemoApp_log.ldf',
     SIZE = 32MB, FILEGROWTH = 64MB);

My first run failed:

Msg 5123, Level 16, State 1, Server YOUR-SERVER, Line 1
CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'C:\DatabaseFAQsDemo\sscreatedb\data\CreateDbDemoApp.mdf'.
Msg 1802, Level 16, State 4, Server YOUR-SERVER, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
SQL Server CREATE DATABASE fails with Msg 5123 operating system error 5 Access is denied on a custom folder
Msg 5123: the SQL Server service can’t write to the folder

I’d made C:\DatabaseFAQsDemo\sscreatedb\data private to my Windows account, the way folders under C:\Users are. That’s the catch: SQL Server creates the file, not you, so its service account needs rights on the folder. The folder must also exist already.

Grant the SQL Server Service Account Access to the Folder

First find the account the engine runs as:

SET NOCOUNT ON;
SELECT CAST(servicename AS varchar(30)) AS servicename,
       CAST(service_account AS varchar(25)) AS service_account
FROM sys.dm_server_services;

Output:

servicename                    service_account          
------------------------------ -------------------------
SQL Server (MSSQLSERVER)       NT Service\MSSQLSERVER   
SQL Server Agent (MSSQLSERVER) NT Service\SQLSERVERAGENT

Then grant it full control on that one folder from a PowerShell prompt you own:

icacls 'C:\DatabaseFAQsDemo\sscreatedb\data' /grant 'NT SERVICE\MSSQLSERVER:(OI)(CI)F'

icacls replied:

processed file: C:\DatabaseFAQsDemo\sscreatedb\data
Successfully processed 1 files; Failed processing 0 files

The same create-database-file-location.sql ran cleanly on the second try. Here’s where the files ended up:

SET NOCOUNT ON;
SELECT CAST(type_desc AS varchar(4)) AS type,
       CAST(physical_name AS varchar(59)) AS physical_name,
       CAST(size / 128 AS varchar(4)) AS size,
       CAST(growth / 128 AS varchar(6)) AS growth,
       CAST(CASE max_size WHEN -1 THEN -1 ELSE max_size / 128 END
            AS varchar(7)) AS max_mb
FROM sys.master_files
WHERE database_id = DB_ID(N'CreateDbDemoApp');

Output:

type physical_name                                               size growth max_mb 
---- ----------------------------------------------------------- ---- ------ -------
ROWS C:\DatabaseFAQsDemo\sscreatedb\data\CreateDbDemoApp.mdf     128  64     10240  
LOG  C:\DatabaseFAQsDemo\sscreatedb\data\CreateDbDemoApp_log.ldf 32   64     2097152
sys.master_files shows the SQL Server data file at 128 MB with 64 MB growth and a 10 GB max in a custom folder
The files sit in my folder with the sizes I asked for

SIZE, FILEGROWTH and MAXSIZE came through exactly (all in MB): 128 MB data capped at 10,240 MB, a 32 MB log. Units default to MB; need to relocate them later? See how to move SQL Server database files.

Set the Database Collation With COLLATE

Leave COLLATE out and the database takes the instance collation, SQL_Latin1_General_CP1_CI_AS on my US-English install. Writing it out pins the behavior, even on a server set up differently:

CREATE DATABASE CreateDbDemoUS
    COLLATE SQL_Latin1_General_CP1_CI_AS;
CREATE DATABASE CreateDbDemoUtf8
    COLLATE Latin1_General_100_CI_AS_SC_UTF8;
GO
SET NOCOUNT ON;
SELECT CAST(name AS varchar(17)) AS name,
       CAST(collation_name AS varchar(34)) AS collation_name
FROM sys.databases
WHERE name LIKE N'CreateDbDemo%';

Output:

name              collation_name                    
----------------- ----------------------------------
CreateDbDemoUS    SQL_Latin1_General_CP1_CI_AS      
CreateDbDemoUtf8  Latin1_General_100_CI_AS_SC_UTF8

The second database uses a UTF-8 collation, which needs SQL Server 2019 or later. Pick collation at create time; changing it later doesn’t convert columns you’ve already created.

How Do You Create a Database Only If It Doesn’t Exist?

SQL Server has no CREATE DATABASE IF NOT EXISTS, unlike MySQL. Run the plain statement twice and you get Msg 1801, so I check DB_ID() first:

SET NOCOUNT ON;
BEGIN TRY
    CREATE DATABASE CreateDbDemoSales;
END TRY
BEGIN CATCH
    SELECT CAST(ERROR_NUMBER() AS varchar(5)) AS error,
           CAST(ERROR_MESSAGE() AS varchar(78)) AS message;
END CATCH;
IF DB_ID(N'CreateDbDemoSales') IS NULL
    CREATE DATABASE CreateDbDemoSales;
ELSE
    PRINT 'CreateDbDemoSales already exists, skipped.';

Result:

error message                                                                       
----- ------------------------------------------------------------------------------
1801  Database 'CreateDbDemoSales' already exists. Choose a different database name.
CreateDbDemoSales already exists, skipped.
SQL Server CREATE DATABASE raises error 1801 already exists while the IF DB_ID IS NULL check skips it
Plain CREATE DATABASE fails with 1801; the DB_ID check just skips

DB_ID(N'name') returns NULL when no database has that name, so the IF works as a create-if-missing guard in deployment scripts. The reverse, DROP DATABASE IF EXISTS, does exist (SQL Server 2016+), same as DROP TABLE IF EXISTS.

Database Names With Spaces or Brackets

Names follow identifier rules and can be up to 128 characters. Anything with a space or a symbol needs square brackets, and a closing bracket inside the name is doubled:

CREATE DATABASE [CreateDbDemo Sales 2026];
CREATE DATABASE [CreateDbDemo[West]]];
GO
SET NOCOUNT ON;
SELECT CAST(name AS varchar(23)) AS name,
       CAST(QUOTENAME(name) AS varchar(26)) AS quoted
FROM sys.databases
WHERE name LIKE N'CreateDbDemo %' OR name LIKE N'CreateDbDemo[[]%';

Output:

name                    quoted                    
----------------------- --------------------------
CreateDbDemo Sales 2026 [CreateDbDemo Sales 2026] 
CreateDbDemo[West]      [CreateDbDemo[West]]]

QUOTENAME() produces that escaped form for you, handy in dynamic SQL. I still avoid spaces; every script after that has to remember the brackets. To list what’s there, find the database name in SQL Server.

Create a New Database in SSMS (New Database Dialog)

SSMS gets you to the same statement through a dialog. These steps follow Microsoft Learn and match SSMS 21:

  1. In Object Explorer, connect to the instance and expand it.
  2. Right-click Databases and select New Database….
  3. On the General page, type the database name. Select OK now to take every default.
  4. Still on General, select (…) next to Owner to change it, and edit the Initial Size, Autogrowth and Path cells in the Database files grid.
  5. On the Options page, choose the collation, recovery model and compatibility level.
  6. On the Filegroups page, select Add to create another filegroup.
  7. Select OK, then right-click Databases and choose Refresh to see it.

Before you select OK, open the Script drop-down at the top of the dialog. SSMS writes the CREATE DATABASE it would run into a query window, which is a quick way to learn the T-SQL above.

On SQL Server 2019, the dialog and steps are the same. You’ll see compatibility level 150, and Query Store isn’t on by default there. Next, build your first table with SSMS Table Designer.

Who Owns the New Database, and Who Can Create One?

Creating one takes CREATE DATABASE permission in master, or CREATE ANY DATABASE, or ALTER ANY DATABASE (the dbcreator role has it). Since the creator becomes the owner, many teams hand ownership to sa:

SET NOCOUNT ON;
SELECT CASE WHEN owner_sid = SUSER_SID() THEN 'my login' END AS owner_before
FROM sys.databases WHERE name = N'CreateDbDemoSales';
ALTER AUTHORIZATION ON DATABASE::CreateDbDemoSales TO sa;
SELECT CAST(SUSER_SNAME(owner_sid) AS varchar(11)) AS owner_after
FROM sys.databases WHERE name = N'CreateDbDemoSales';

Output:

owner_before
------------
my login    
owner_after
-----------
sa

Ownership changed without touching any data. The old keeper post also set up a login, user and db_owner membership right after this step; read up on SQL Server user permissions before you grant db_owner, then list the users in the database to check.

Common CREATE DATABASE Errors I Hit

Two more came from this script, caught with TRY…CATCH in SQL Server:

SET NOCOUNT ON;
BEGIN TRY
    BEGIN TRANSACTION;
    CREATE DATABASE CreateDbDemoTran;
    COMMIT;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK;
    SELECT ERROR_NUMBER() AS error,
           CAST(ERROR_MESSAGE() AS varchar(75)) AS message;
END CATCH;
CREATE DATABASE CreateDbDemoTiny
    ON (NAME = CreateDbDemoTiny_data,
        FILENAME = 'C:\DatabaseFAQsDemo\sscreatedb\data\CreateDbDemoTiny.mdf',
        SIZE = 4MB);
SELECT size / 128 AS size_mb FROM sys.master_files
WHERE database_id = DB_ID(N'CreateDbDemoTiny') AND type = 0;

Output:

error       message                                                                    
----------- ---------------------------------------------------------------------------
        226 CREATE DATABASE statement not allowed within multi-statement transaction.  
size_mb    
-----------
          8

The first is a hard rule: CREATE DATABASE only runs in autocommit mode. The second surprised me. I asked for 4 MB, the docs say the primary file can’t be smaller than model’s, and SQL Server quietly gave me 8 MB, no error.

What I didWhat came backFix
Custom folder without rightsMsg 5123, OS error 5, then Msg 1802Grant the service account on the folder
Ran the CREATE twiceMsg 1801 already existsIF DB_ID(N’…’) IS NULL
Wrapped it in BEGIN TRANSACTIONMsg 226Run it on its own
SIZE = 12.5MBMsg 102 Incorrect syntax near ‘12.5’Whole numbers only (use KB)
SIZE = 4MBNo error, 8 MB fileSize at least model’s 8 MB

My takeaways:

  1. Write the full statement with file names, sizes and growth for any database you’ll keep.
  2. Check model first; every default you skip comes from it.
  3. Grant the service account on custom folders before you run CREATE DATABASE.
  4. Guard deployment scripts with DB_ID() and keep the CREATE out of transactions.

Results differ if someone changed model on your server, or on Express, where the recovery model default depends on the edition. Doing this in PostgreSQL? Here’s how to create a database in PostgreSQL.

Same job on other platforms, plus one SQL Server extra:

Frequently Asked Questions

How do I create a database in SQL Server Express?

Use the same CREATE DATABASE statement or SSMS dialog. Express caps each database at 50 GB in SQL Server 2025 (per the SQL Server 2025 editions page), up from 10 GB in earlier versions.

How many databases can I create on one SQL Server instance?

Up to 32,767 per instance, and each database can hold up to 32,767 files and 32,767 filegroups, per the CREATE DATABASE reference.

How do I rename a database after creating it?

Run ALTER DATABASE CreateDbDemoSales MODIFY NAME = SalesDemo; from master. Only the name changes: the .mdf and .ldf file names and the logical file names stay as they were.

Can I create a database from a .bak backup file?

Yes. RESTORE DATABASE creates the database from the backup, and WITH MOVE puts its files in new folders when the original paths don’t exist on your server.