Use Transact-SQL to create a basic database in SQL Server 2025
Use Transact-SQL to Create a Database in SQL Server 2025
The previous lesson surveyed what a database is actually made of, the files behind it and the objects built on top of those files. This lesson puts that knowledge to work: using the Transact-SQL CREATE DATABASE statement, hands-on, to actually build one.
The Simplest Possible CREATE DATABASE
The only thing CREATE DATABASE strictly requires is a name:
CREATE DATABASE employee;
Run exactly as written, SQL Server fills in everything else from its own defaults:
The primary data file is named employee.mdf, stored in the instance's default data directory.
The transaction log file is named employee_log.ldf, stored in the instance's default log directory.
Both files start out at the same initial size as the corresponding file in the model system database, SQL Server's template database for every new database: 8 MB each on a default installation.
Both files are set to grow automatically as they fill up. The exact growth increment and any maximum size follow the instance's own configured defaults rather than a single fixed rule, so it's worth confirming the actual settings on a given server with sys.master_files rather than assuming a specific number.
That one-line statement is genuinely all it takes to get a working database. Everything from here on is about taking back control over the details SQL Server would otherwise decide for you.
The minimal form isn't just a teaching shortcut, either; it's a reasonable choice for a quick scratch database you'll drop again shortly, a local development database, or anywhere disk layout and growth behavior genuinely don't matter yet. It stops being the right choice the moment a database is going into production, where an administrator usually wants to decide in advance which drive the data file lives on, which (often separate, faster) drive the log file lives on, and how aggressively each one should be allowed to grow, rather than leaving all three to whatever the instance happens to be configured for.
Specifying Data and Log Files Explicitly
For real control over where a database's files live, how large they start, and how they grow, specify the file details directly rather than accepting the defaults:
1 | Create a Database
SQL Server 2025, explicit data and log files
A database isn't limited to a single data file. Additional, optional filegroups beyond PRIMARY can hold secondary files, useful for spreading data across physical disks for performance or for separating older, rarely-touched data from active data that's queried constantly.
FILEGROWTH deserves a closer look, since it can be specified two different ways. Given as a plain number with a size unit, FILEGROWTH = 64MB above, the file grows by that exact fixed amount every time it fills up. Given instead as a percentage, FILEGROWTH = 10%, the file grows by that percentage of its current size each time, meaning the actual growth increment gets larger as the file itself gets larger. A fixed-size increment is generally the more predictable of the two for planning disk capacity, which is part of why the explicit examples in this lesson all use one.
Create, Restore, or Attach: Three Different Starting Points
CREATE DATABASE isn't the only way a database ends up on an instance, and it's worth being clear about which situation calls for which statement before writing any of them against a real server.
3 | Create, Restore, or Attach?
SQL Server 2025, choose the operation for your input
New database
CREATE DATABASE SalesDB;
Creates new files using instance defaults.
Backup file (.bak)
RESTORE DATABASE SalesDB
FROM DISK = N'C:\Backups\SalesDB.bak';
Restores a database backup. Use WITH MOVE when file paths must change.
Existing database files
CREATE DATABASE SalesDB
ON (FILENAME = N'C:\SQLData\SalesDB.mdf'),
(FILENAME = N'C:\SQLLogs\SalesDB_log.ldf')
FOR ATTACH;
Registers a detached database's existing files. All data files must be available.
A brand-new database lets SQL Server create fresh files, either from instance defaults or from the explicit file specification covered above. Restoring from a backup file is RESTORE DATABASE's job, not CREATE DATABASE's, and it can relocate the restored files with WITH MOVE if they need to land somewhere other than where the backup originally recorded them. Attaching registers a database's existing, detached data and log files as a database again without restoring from a backup at all, but every one of those files has to actually be present and reachable on disk for the attach to succeed.
A Syntax Detail Worth Correcting
Older material covering CREATE DATABASE sometimes shows its full syntax something like this:
CREATE DATABASE database_name
ON [PRIMARY]
[ <filespec> [,...n] ]
[, <filegroup> [,...n] ]
[ LOG ON { <filespec> } ]
[ FOR ATTACH ]
ON [PRIMARY] specifies the primary data file along with any additional secondary files and filegroups; FOR ATTACH is still a current, supported option for attaching existing .mdf and .ldf files, exactly as shown above.
One thing worth flagging if you encounter it in older documentation or scripts: some older material also lists FOR LOAD alongside FOR ATTACH as a trailing option. FOR LOAD is not part of current CREATE DATABASE syntax. It was a holdover from versions of SQL Server old enough to predate a proper RESTORE DATABASE statement; restoring a backup today is handled entirely by RESTORE DATABASE, not by a trailing keyword on CREATE DATABASE. If you see FOR LOAD referenced anywhere, treat it as a sign the material predates modern restore syntax rather than something to actually use.
A Working FOR ATTACH Example
Putting the attach path from above to use directly:
CREATE DATABASE SalesDB
ON (FILENAME = N'C:\SQLData\SalesDB.mdf')
FOR ATTACH;
This successfully attaches an existing .mdf file, along with the log file SQL Server locates alongside it, provided two things hold: the file actually exists and isn't corrupted, and the SQL Server service account has the file system permissions needed to read it.
That last point assumes the log file is actually present and findable. If it isn't, perhaps it was deleted, or the database was shut down uncleanly and the log is no longer trustworthy, an ordinary FOR ATTACH fails. FOR ATTACH_REBUILD_LOG handles that situation specifically, rebuilding a new transaction log from just the data file alone:
CREATE DATABASE SalesDB
ON (FILENAME = N'C:\SQLData\SalesDB.mdf')
FOR ATTACH_REBUILD_LOG;
This should be treated as a last resort rather than a routine option: rebuilding the log discards whatever transactions it would otherwise have preserved, so it only belongs in a genuine recovery scenario, not as a shortcut for an everyday attach.
Confirming the Database Exists
Once a CREATE DATABASE statement completes, it's worth actually confirming the result rather than assuming it worked exactly as intended, especially after specifying files and sizes by hand. The sys.databases catalog view, already used elsewhere in this course, reports both the database itself and the state SQL Server considers it to be in:
SELECT name, create_date, state_desc
FROM sys.databases
WHERE name = 'SalesDB';
A state_desc of ONLINE means the database came up cleanly and is ready to use. For a look at the actual files behind it rather than just the database as a whole, sys.master_files reports every data and log file on the instance, including the size, growth setting, and physical path actually in effect for each one, which is the more reliable way to confirm what a database's files are really configured to do rather than assuming the CREATE DATABASE statement was interpreted the way you intended:
SELECT name, physical_name, size, growth, is_percent_growth
FROM sys.master_files
WHERE database_id = DB_ID('SalesDB');
In SQL Server Management Studio, the same confirmation is a right-click away: refreshing the Databases node in Object Explorer and finding SalesDB listed there, or opening its Properties dialog from the previous lesson, confirms the same thing without writing a query at all.
Setting a Collation at Creation Time
A database's collation controls how it compares and sorts character data: whether 'smith' and 'Smith' are treated as equal, and in what order accented characters sort relative to unaccented ones. Left unspecified, a new database inherits the instance's own default collation, but CREATE DATABASE accepts a COLLATE clause to set one explicitly instead:
SQL_Latin1_General_CP1_CI_AS is a common default in English-language installations; the CI and AS suffixes mean case-insensitive and accent-sensitive comparisons, respectively. Setting this explicitly at creation time matters most when a database needs to match the collation of another system it exchanges data with, or when the instance's own default isn't the right fit for the data a particular database will hold.
Common Errors Worth Recognizing
A few errors come up often enough when running CREATE DATABASE that it's worth recognizing them on sight rather than treating each one as a mystery:
Permission denied: the login running the statement lacks CREATE ANY DATABASE, dbcreator, or sysadmin, exactly the permissions the earlier lesson in this module covered. The fix is granting the right permission, not changing anything about the statement itself.
Cannot create file because the directory does not exist: a FILENAME path points at a folder that isn't actually there on the server. SQL Server won't create missing folders on your behalf; the folder has to exist first.
Operating system error 5 (Access is denied): the folder exists, but the SQL Server service account doesn't have write permission to it. This is a file-system permission, granted to a Windows account, not a SQL Server login permission, and the two are easy to conflate when troubleshooting.
Database already exists: the name is already in use elsewhere on the instance. Database names have to be unique per instance, even though table names only need to be unique within their own database.
From Statement to Working Database
It's worth seeing the whole sequence once, start to finish, the way you'd actually run it against a real server. Creating the database is only the first step; a USE statement switches your connection into it, and only then does a CREATE TABLE statement, the subject of the module this one preceded, have somewhere to put the table it builds:
CREATE DATABASE SalesDB
ON PRIMARY
( NAME = SalesData, FILENAME = 'C:\SQLData\SalesDB.mdf',
SIZE = 128MB, FILEGROWTH = 64MB )
LOG ON
( NAME = SalesLog, FILENAME = 'C:\SQLLogs\SalesDB_log.ldf',
SIZE = 64MB, FILEGROWTH = 64MB );
GO
USE SalesDB;
GO
SELECT name, create_date, state_desc
FROM sys.databases
WHERE name = 'SalesDB';
The GO batch separator after CREATE DATABASE isn't strictly required by every tool, but it's good practice to include it here specifically: some tools won't let a later statement in the same batch reference a database that the batch itself just created, since the database doesn't fully exist from that batch's point of view until the batch finishes. Running the three pieces in order, create, switch, verify, is the complete pattern this lesson has been building toward, and the one you'll reach for every time a new database needs to exist before the real work in it can begin.
What's Ahead
The next lesson turns to control-of-flow logic: testing the value of a variable and taking a different action depending on what it holds.