Tag: create database

Create a Database in SQL Server (T-SQL)

Posted on by Ian

Many developers and database administrators create databases using GUI tools such as SQL Server Management Studio (SSMS), Azure Data Studio, etc.

But it can be much quicker to create databases using SQL. This is especially true if you have various environments that you need to recreate the same database on. You can run the same script against each environment, and the database will be created within seconds each time.

In SQL Server, we do this with T-SQL. T-SQL stands for Transact-SQL, and it is SQL Server’s extension to SQL.

Below is an example of using T-SQL to create a database in SQL Server.

Continue reading →

2 Ways to Create a Database on a Linked Server using T-SQL

Posted on by Ian

One way of creating a database on a linked server, is to simply jump over to that server and create it locally.

But you’d probably feel a bit cheated if I included that as one of the “2 ways” to create a database on a linked server.

Also, while that option is fine if you’re able and willing to do it, this article shows you how to do it remotely using T-SQL, without having to jump over to the local server. Plus you might find this technique quicker than jumping over to the other server.

Both of the “2 ways” involve the EXECUTE statement (which can also be shortened to EXEC). We can use this statement to execute code on the linked server, and that includes creating a database on it.

Continue reading →

Create a SQL Server Database with Azure Data Studio

Posted on by Ian

Here, I explain how to use Azure Data Studio (formerly SQL Operations Studio) to create a database in SQL Server.

In my case, I’m running SQL Server on a Mac (via a Docker container), but these instructions are generic and should work exactly the same way on Linux and Windows.

This tutorial assumes you already have Azure Data Studio installed on your machine, and you’ve connected to your SQL Server instance. If you don’t have Azure Data Studio installed on your machine, here are instructions for installing it on a Mac and connecting to SQL Server via Docker.  The Azure Data Studio installation is pretty straightforward (you install it just like any other software), so if you’re on Linux or Windows, you shouldn’t have any issues.

Continue reading →

How to Specify the Location of Data Files and Log Files when Creating a Database in SQL Server

Posted on by Ian

The simplest way to create a database in SQL Server is to use CREATE DATABASE my_database without specifying anything else. When you do this, data files and log files are created in the default location (see how to find the default location).

However, sometimes you might want the data files and log files to reside in a different location. If that’s the case, use the following code example to explicitly state your own location for the database’s data files and log files.

USE master;
GO
CREATE DATABASE Solutions
ON
( NAME = Solutions_dat,  
    FILENAME = 'D:\mssql\data\Solutionsdat.mdf',
    SIZE = 10MB,
    MAXSIZE = 50MB,
    FILEGROWTH = 5MB )  
LOG ON
( NAME = Solutions_log,  
    FILENAME = 'D:\mssql\data\Solutionslog.ldf',
    SIZE = 5MB,
    MAXSIZE = 25MB,
    FILEGROWTH = 5MB );
GO

That example uses Windows path conventions (starts with a drive letter and uses a backslash).

Continue reading →

Create a SQL Server Database with SQLOPS

Posted on by Ian

UPDATE: SQL Operations Studio (SQLOPS) has since been renamed to Azure Data Studio. The steps in this article remain the same though. In any case, I’ve also rewritten these steps (and included the equivalent screenshots) specifically for Azure Data Studio . See Create a SQL Server Database with Azure Data Studio.

Here, I explain how to use SQL Operations Studio (SQLOPS) to create a database in SQL Server.

In my case, I’m running SQL Server on a Mac (via a Docker container), but these instructions are generic and should work exactly the same way on Linux and Windows.

This tutorial assumes you already have SQLOPS installed on your machine, and you’ve connected to your SQL Server instance. If you don’t have SQLOPS installed on your machine, here are instructions for installing it on a Mac and connecting to SQL Server via Docker.  The SQLOPS installation is pretty straightforward (you install it just like any other software), so if you’re on Linux or Windows, you shouldn’t have any issues.

Continue reading →

SQL CREATE DATABASE Syntax – Listed by DBMS

Posted on by Ian

This article contains the SQL CREATE DATABASE syntax, as implemented by various database management systems (DBMSs). The syntax is listed exactly as each vendor has listed it on their website. Click on the applicable link to view more detail about the syntax for a particular vendor.

The DBMSs covered are MySQL, SQL Server, PostgreSQL, and Oracle Database.

Continue reading →