Tools for Creating a SQL Server Database

The previous lesson showed CREATE DATABASE issued directly as a T-SQL statement, run against a query window. That's the most direct way to create a database, but it isn't the only one, and it isn't even the most common one once a real team is involved. This lesson surveys the tools SQL Server 2025 actually gives you for the job, from the graphical designer most newcomers reach for first to the scripted, automatable paths a production deployment pipeline depends on. Nearly all of them, under the hood, end up running the same CREATE DATABASE statement on your behalf; what differs is how much of that statement you type yourself, and how repeatable the result is.

Before getting to the tools themselves, it's worth clearing up a related question: does every object in SQL Server belong to a parent database? Mostly, yes. Tables, views, stored procedures, and the indexes built on them all live inside exactly one database, forming a parent-child hierarchy: a database contains tables, and a table contains the indexes built against it. But a few categories of object sit outside that hierarchy entirely:

• Server-level objects. Logins, server roles, and linked servers belong to the SQL Server instance itself, not to any one database; a login can connect to the instance and then be granted access to several different databases on it.
• Temporary objects. A temporary table or temporary stored procedure exists only for the life of the session or transaction that created it and isn't stored inside any permanent database.
• System metadata. Every database, including the ones you create yourself, exposes catalog views like sys.tables and sys.columns through a built-in sys schema present in that same database; there's no separate database literally named sys. Instance-wide information, such as the list of every database on the server, comes from sys.databases, one of the few catalog views that applies across the whole instance rather than being scoped to just one database.

Outside those exceptions, the ordinary case is a clean parent-child hierarchy: a database contains tables, and a table contains the indexes built against it. The course project's own Timesheets database is a concrete example. It contains four tables, Employees, Clients, Tasks, and Timesheets, and each of those tables has its own index built to speed up queries against it: EmpIndex, ClientIndex, TaskIndex, and TimeIndex, respectively. The database is the parent; each table is a child of the database; each index is in turn a child of its own table.

There's actually one more layer between a database and its tables worth knowing about before you create your first one: the schema. A schema is a named container within a database that groups related objects together and qualifies their names, which is why a table is more precisely referred to as dbo.Employees rather than just Employees, dbo being the default schema every database starts with. A single database can hold several schemas, letting you organize tables by subject area or by which team owns them, but that's a design decision for later in this course; for now, it's enough to recognize dbo. when you see it in front of a table name and know it isn't a typo.

SSMS is still the primary, full-featured tool for SQL Server administration, and the one most lessons in this course assume you have open. Its Object Explorer lets you right-click the Databases node and choose New Database to create one through a dialog, filling in the same data file, log file, size, and growth settings covered in the previous lesson without typing a single line of T-SQL. Alternatively, you can open a query window inside SSMS and run CREATE DATABASE directly, the same statement the dialog builds behind the scenes. Either path reaches the same result; the dialog is really just a GUI wrapped around the statement, convenient for a one-off database but less practical once you need to create the same database structure repeatedly, or reproduce it exactly on a second server.

The dialog itself is quick to work through:

1. In Object Explorer, right-click the Databases node and choose New Database...
2. On the General page, enter a database name; SSMS fills in default file names and locations for you automatically.
3. Adjust the initial size, autogrowth, and file path for the data and log files if the defaults don't fit, the same settings the previous lesson's CREATE DATABASE statement specified explicitly.
4. On the Options page, set the collation or recovery model if they need to differ from the server default.
5. Click OK to create the database.

One feature worth knowing about before you click OK: the dialog's Script dropdown at the top of the window can generate the equivalent T-SQL for whatever you've configured, without actually running it, letting you capture the statement for a script file or source control instead of, or in addition to, creating the database right then. That's a useful bridge between the two approaches covered so far in this lesson: build it visually, then keep the generated script as the repeatable record of what you built.

Running CREATE DATABASE directly, whether in SSMS's query window or from the command line with sqlcmd, is the most direct route of all, and the one the previous lesson used. sqlcmd is useful specifically because it doesn't require a GUI at all, which matters for automated deployment scripts or a server with no desktop environment installed, including SQL Server running on Linux:

sqlcmd -S localhost -d master -Q "CREATE DATABASE TrainingDB;"

A script file works just as well as an inline query, which is the usual pattern in a real deployment: a .sql file containing the full CREATE DATABASE statement, with its file paths, sizing, and WITH options, checked into source control and run with sqlcmd -i scriptfile.sql whenever a new environment needs to be stood up:

sqlcmd -S myserver.example.com -U appuser -P "********" -d master -i create-trainingdb.sql

That same command runs identically whether the target is a local instance on a developer's laptop, a server down the hall, or a SQL Server instance running on Linux; sqlcmd doesn't care which operating system is hosting the database it's connecting to, only that it can reach it over the network.

The MSSQL extension for Visual Studio Code gives you a lightweight, cross-platform alternative to SSMS: a connection to a SQL Server instance, a query editor with IntelliSense, and basic database-management operations including creating a new database, all inside an editor many developers already have open for other work. It's a natural fit for a developer who's writing application code alongside their database changes and doesn't want to switch to a separate, heavier administration tool just to run a handful of statements. The same extension also connects to Azure SQL Database and Azure SQL Managed Instance using the identical workflow, so the skill transfers directly if a project later moves, or already runs, in the cloud rather than on a server you manage yourself.

SQL Server Data Tools (SSDT), an add-on for Visual Studio, and the SQL Database Projects extension for Visual Studio Code take a different approach entirely. Instead of running CREATE DATABASE interactively against a live server, you define a database's entire schema, tables, columns, constraints, and all, as a project of source files, the same way you'd define an application's source code. Both rely on DacFx, the Data-Tier Application Framework, to compare that project's definition against whatever already exists on a target server and generate the statements needed to bring the server in line with it.

This project-based approach matters most once a database's schema changes over time across multiple environments, development, test, and production, since the project itself becomes the single source of truth for what the schema should look like, rather than a server's live state being the only record. Change a column's definition in the project, and the next publish figures out, and generates, whatever ALTER TABLE statement is needed to bring a given target server in line, without anyone having to remember to write that statement by hand or track down which servers already received it.

PowerShell reaches SQL Server two ways. The Invoke-Sqlcmd cmdlet runs a T-SQL script much like sqlcmd does, but from inside a PowerShell session where its output can be captured, filtered, and chained into the rest of a larger script:

Invoke-Sqlcmd -ServerInstance "localhost" -Query "CREATE DATABASE TrainingDB;"

The second path, SQL Server Management Objects (SMO), is a .NET object model that lets a script create and configure a database programmatically, setting properties on a Database object and calling a Create() method, without writing the CREATE DATABASE statement out by hand at all. SMO is the heavier of the two options, but it gives a script full, structured access to nearly everything SSMS itself can do.

Both approaches work from PowerShell 7 (pwsh), which runs on Windows, Linux, and macOS alike, so a PowerShell automation script written on one platform generally runs unchanged on another, the same cross-platform reach sqlcmd offers for simpler, non-scripted statements.

SqlPackage is the command-line counterpart to SSDT's publish step: it deploys a DACPAC, a packaged, compiled version of a database project's schema, to a target server, creating the database if it doesn't already exist and updating it to match the DACPAC's definition if it does:

SqlPackage /Action:Publish /SourceFile:"TrainingDB.dacpac" /TargetServerName:"localhost" /TargetDatabaseName:"TrainingDB"

This is the path most commonly automated into a deployment pipeline: a build process compiles a database project into a DACPAC, and SqlPackage, invoked from a build server rather than a person's own machine, publishes it to whichever environment the pipeline is targeting next. SqlPackage can also work in reverse, with its /Action:Extract option, pulling a DACPAC out of a database that already exists. That's a practical way to bring an older database that predates any of these tools under the same project-based management SSDT and SQL Database Projects offer, without hand-transcribing its entire schema from scratch.

Older material sometimes still lists Azure Data Studio alongside these tools. Microsoft retired it; support ended February 28, 2026, and its database-management and query capabilities have moved into the MSSQL extension for Visual Studio Code covered above. It shouldn't appear in a current tools list, and if you come across a tutorial or reference still recommending it, that's a reliable sign the material itself hasn't been updated recently.

With six current options on the table, SSMS, T-SQL and sqlcmd, VS Code with the MSSQL extension, SSDT and SQL Database Projects, PowerShell, and SqlPackage, it helps to group them by what they're actually good at rather than treat them as interchangeable. A new table or two for a training database calls for a very different tool than a schema that a dozen developers need to keep synchronized across five environments:

Situation	Reach For
Interactive, day-to-day administration	SSMS
A quick script, no GUI, or a Linux server	T-SQL with sqlcmd
Working alongside application code	VS Code with the MSSQL extension
A schema that needs to stay in source control across environments	SSDT or SQL Database Projects
Scripted administration as part of a larger automation task	PowerShell (Invoke-Sqlcmd or SMO)
Automated deployment to production as part of a build pipeline	SqlPackage with a DACPAC

None of these are mutually exclusive in practice. It's entirely normal to design and version a schema with SQL Database Projects, deploy it with SqlPackage as part of an automated pipeline, and still keep SSMS open the rest of the day for ordinary administration and troubleshooting. The right tool depends on what you're doing at that moment, not a single choice you make once and stick with everywhere.

Whichever tool you reach for, SSMS's dialog, a raw CREATE DATABASE statement, PowerShell, or an automated SqlPackage deployment, none of them bypass the server's own permission checks. A tool can make running the statement easier, but it can't grant you authority you don't already have; a login without membership in the sysadmin or dbcreator server role, and without an explicit CREATE ANY DATABASE grant, gets the same permission-denied error from SSMS's dialog as it would from a bare CREATE DATABASE statement typed into a query window. The next lesson covers exactly what those permissions are and how to grant them.

In the next lesson, you'll look at the permissions required to create, modify, or delete a database, including the server-level roles and permissions mentioned earlier in this lesson's discussion of logins and server-level objects.