Skip to main content

Microsoft SQL Server Backups

Overview#

If you define the sourceType property as backup you will be able to create a data image whose content will be what you defined in your backup.

sourceType: backup
name: my-database
engine: mssql
version: 2017
backup:
folder: C:\LocalBackups\
file: my-database.bak

In the YAML above we want to create a SQL Server data image whose source is backup and is named my-database.

Tutorial (video)#

Watch this video for how to create data images from SQL Server backups. Or follow the step-by-step instructions below.

Tutorial (step-by-step)#

Create a backup#

You may want to skip the "Create a backup" step if you already have the backup file for your database available.

Create a directory which your SQL Server instance can access and will write the backup to:

mkdir C:\LocalBackups

In this example we will be backing up the AdventureWorks2017 database running at a local instance .\SQL2017. To backup the databse to C:\LocalBackups\ you can run the following snippet of SQL:

USE AdventureWorks2017;
GO
BACKUP DATABASE AdventureWorks2017
TO DISK = 'C:\LocalBackups\adventureworks.bak'
WITH FORMAT,
MEDIANAME = 'SQLServerBackups',
NAME = 'Backup of AdventureWorks';
GO

Create a data image yaml file#

Now that we've got a adventureworks.bak backup file we can create our Spawn data image definition YAML file called image.yaml in the current directory with the following contents:

name: adventureworks
sourceType: backup
engine: mssql
version: 2017
backup:
folder: C:\LocalBackups\
file: adventureworks.bak
tags:
- production

This will instruct Spawn to create a data image called adventureworks on SQL Server 2017. It will upload the file C:\LocalBackups\adventureworks.bak to Spawn, restore that backup, snapshot the state of the restored database and then produce the data image ready for consumption.

The data image will also have a production tag associated with it, so you later know that this data image represents a backup of production.

Create the image#

spawnctl create data-image --file=image.yaml

This will create the data image from the backup defined in the image.yaml file we created.