Skip to content
Stackship documentation Svenska

SQL ServerUsers

Back up and restore

Schedule backups of a SQL Server instance, take a snapshot before a risky change, and restore one in place — and what a volume snapshot does and does not give you.

Requires: sqlservercluster/write, sqlservercluster/createSnapshot, sqlservercluster/restoreSnapshot, sqlservercluster/deleteSnapshot

A backup of a SQL Server instance is a snapshot of its data volume: every database on the server, as it was on disk at that moment. It is crash-consistent — what a restore gives back is what SQL Server would find after a sudden power loss, which it recovers from when it starts.

It is not a native SQL Server backup. There is no transaction-log backup and no point-in-time recovery: a restore goes back to the moment of a snapshot, never to a moment in between.

Task Needs Built-in roles
See snapshots sqlservercluster/read every role that can see the instance
Schedule backups sqlservercluster/write Owner, Contributor, Databases Operator
Take a snapshot sqlservercluster/createSnapshot Owner, Contributor, Databases Operator
Restore or delete a snapshot sqlservercluster/restoreSnapshot, sqlservercluster/deleteSnapshot Owner, Databases Operator

Schedule backups

  1. On the instance's Configuration tab, open Database and turn on Backups.
  2. Set the Retention Policy — a number followed by d, w or m, such as 30d — and the Backup schedule: Daily, or Weekly on a Day of week, at a Time (UTC), or a Custom cron expression with five fields, in UTC.
  3. Save.

Scheduled backups appear on the Snapshots tab like the ones you take by hand, and expire when their retention is up. Turning Backups off stops the schedule. Changing the schedule or the retention does not restart the server.

With the CLI:

bash
stsh sqlserver update my-sql -g my-resource-group \
  --set backupConfigured=true --set backupSchedule="30 1 * * *" --set backupRetentionPolicy=14d

Without a schedule of its own, backups run daily at 03:00 UTC and are kept for 30 days.

Take a snapshot

  1. On the Snapshots tab, choose Take snapshot.
  2. Give it a Description, if you like, and choose how long to Keep for: 7, 30, 90 or 365 days. 30 days is the default.
  3. Choose Take snapshot. The server keeps running; the snapshot completes in the background and appears in the list, where it expires by itself when its time is up.

A new instance cannot be snapshotted until it has created its initial database; the platform refuses until then and says to wait for the instance to report Running. An instance created without an initial database, which the API and the CLI allow, never gets past this check: no snapshot can be taken of it by hand, in the portal or with the CLI.

With the CLI (--ttl in days, 1 to 365):

bash
stsh sqlserver snapshot create my-sql -g my-resource-group --description "before the migration" --ttl 30
stsh sqlserver snapshot list my-sql -g my-resource-group

Restore a snapshot

Only a snapshot whose status is Completed can be restored.

  1. On the Snapshots tab, choose Restore on the snapshot.
  2. Type the instance's name to confirm, and choose Restore.

The restore runs in place and keeps the instance's name, connection strings and password:

  • the server is stopped;
  • its data volume is replaced by the one in the snapshot;
  • the server is started again — unless it was stopped before the restore, in which case it stays stopped.

The previous volume is kept for seven days. Follow the restore on the instance's Operations tab. Everything written since the snapshot was taken is lost.

With the CLI, which asks before it starts:

bash
stsh sqlserver snapshot restore my-sql -g my-resource-group <snapshot-id>

Delete a snapshot

Choose Delete snapshot on the snapshot and confirm with Delete, or:

bash
stsh sqlserver snapshot delete my-sql -g my-resource-group <snapshot-id>