You can use the T-SQL BACKUP DATABASE statement to back up any SQL Server database.

This article shows you how to backup a database to disk. This creates a .bak file which can be used later to restore the database if required.

Create a Full Backup

Here’s a basic example of creating a full backup of a SQL Server database to disk:

BACKUP DATABASE Movies  
TO DISK = 'Z:\mssql\backups\Movies.bak';

This example creates a full backup of the Movies database to a file on disk This example uses a location on the Z drive, but the path can be anything you want. The database should be backed up to a different drive to the one used for the actual database itself. That way, if there’s a disk failure, you don’t lose your backup file along with the database.

The following example does the same thing, but this example uses Linux and Mac file path syntax:

BACKUP DATABASE Movies  
TO DISK = '/var/opt/mssql/backups/Movies.bak';

Create a Differential Backup

Once you’ve created a full backup, you can create differential backups. A differential backup is one where only parts of the database that have changed since the last full database backup was created are backed up.

Differential backups reduce the time it takes to backup the database (due to the fact that only the changes since the last full backup are backed up).

To create a differential backup, use the same BACKUP DATABASE statement that you use to create the full backup, except this time add the WITH DIFFERENTIAL clause.

Here’s an example:

BACKUP DATABASE Movies  
TO DISK = 'Z:\mssql\backups\Movies.bak'
WITH DIFFERENTIAL;

Running this statement will append the differential backup to the original backup file that contains the full backup.

Backup the Transaction Log

You can also backup the transaction log. This is done with the BACKUP LOG statement.

Here’s an example:

BACKUP LOG Movies  
TO DISK = 'Z:\mssql\backups\Movies_log.bak';