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 →

How to Find the Default File Location for Data Files and Log Files in SQL Server

Posted on by Ian

Any time you create a database in SQL Server, two files are created. One is the data file, and the other is the transaction log file.

The location of these files will depend on whether or not you explicitly specify a location for these files when you create the database. If not, they will be created in the default location.

You can find the default location with the following code:

SELECT
  SERVERPROPERTY('InstanceDefaultDataPath') AS 'Data Files',
  SERVERPROPERTY('InstanceDefaultLogPath') AS 'Log Files'

Continue reading →

How to Insert Values into an IDENTITY Column in SQL Server

Posted on by Ian

If you’ve ever tried to insert values into an identity column in SQL Server, you might’ve seen an error like this:

Cannot insert explicit value for identity column in table ‘Artists’ when IDENTITY_INSERT is set to OFF.

This is normal. An identity column is there for a reason. It automatically populates the column with an incrementing value for each row that’s inserted. Therefore there’s no need for you to insert a value into that column.

However, sometimes you do need to insert a value into an identity column. For example, you could be populating the database with data that needs to retain its own identity values. If this is the case, you’ll need to override the IDENTITY property. Here’s how.

Continue reading →

T-SQL vs SQL

Posted on by Ian

Here’s a quick overview of the difference between SQL and T-SQL (Transact-SQL). Not so much the differences as such, but more an explanation of T-SQL and where it sits in relation to SQL.

If you’ve done any database development or administration, you’re probably familiar with SQL. SQL, which stands for Structured Query Language, is a standard query language for working with databases. Most of the major relational database management systems such as MySQL, Oracle, SQL Server, PostgreSQL, etc support SQL in one way or another.

However, while the SQL standard provides clear specifications, it also allows for database vendors to add their own extensions. This allows vendors to provide extra features and functionality for their customers that might not be offered by their competitors.

This is where T-SQL comes in.

Continue reading →

How to Restore a SQL Server Database on a Mac using SQL Operations Studio

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 How to Restore a SQL Server Database on a Mac using Azure Data Studio.

Restoring a database is a piece of cake with SQL Operations Studio. It’s a similar process to doing it with SQL Server Management Studio.  Simply click Restore and follow the prompts.

This restore process allows you to navigate through the computer’s file system to locate the .bak file. This .bak file contains a backup of the database you want to restore. So when SQL Server restores the database, it’s using the .bak file to do so.

However, if you’re running your SQL Server instance inside a Docker container (which of course, you would be if you’re running SQL Server on Mac or Linux), there’s something you need to be aware of if your backup file is located outside the Docker container.

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 →

How to Install SQLOPS on a Mac

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 How to Install Azure Data Studio on a Mac.

SQL Operations Studio (also abbreviated to SQLOPS) is a free tool that you can use to manage SQL Server. It uses a graphical user interface (GUI) that helps you view the various databases and objects within a SQL Server instance. It can run on Windows, macOS, and Linux, and it’s also designed to be used with Azure SQL Database, and Azure SQL Data Warehouse.

Here I explain how to install SQLOPS onto a Mac, then how to use it to connect to SQL Server.

Continue reading →