Skip to content

Backup and restore SQL Server on Linux

    You can take backups of databases from SQL Server 2017 on Linux with many different options. On a Linux server, you can use sqlcmd to connect to the SQL Server and take backups. From Windows, you can connect to SQL Server on Linux and take backups with the user interface. The backup functionality is the same across platforms. For example, you can backup databases locally, to remote drives, or to Microsoft Azure Blob storage service.

    SQL Server on Linux only supports backing up to Azure Blob storage using block blobs. Using a storage key for backup and restore will result in a page blog being used, which isn’t supported. Use a Shared Access Signature instead.

    Backup a database

    In the following example sqlcmd connects to the local SQL Server instance and takes a full backup of a user database called demodb.

    Bash

    sqlcmd -S localhost -U SA -Q “BACKUP DATABASE [demodb] TO DISK = N’/var/opt/mssql/data/demodb.bak’ WITH NOFORMAT, NOINIT, NAME = ‘demodb-full’, SKIP, NOREWIND, NOUNLOAD, STATS = 10”

    When you run the command, SQL Server will prompt for a password. After you enter the password, the shell will return the results of the backup progress. For example:

    Password:

    10 percent processed.

    21 percent processed.

    32 percent processed.

    40 percent processed.

    51 percent processed.

    61 percent processed.

    72 percent processed.

    80 percent processed.

    91 percent processed.

    Processed 296 pages for database ‘demodb’, file ‘demodb’ on file 1.

    100 percent processed.

    Processed 2 pages for database ‘demodb’, file ‘demodb_log’ on file 1.

    BACKUP DATABASE successfully processed 298 pages in 0.064 seconds (36.376 MB/sec).

    Backup the transaction log

    If your database is in the full recovery model, you can also make transaction log backups for more granular restore options. In the following example, sqlcmd connects to the local SQL Server instance and takes a transaction log backup.

    Bash

    sqlcmd -S localhost -U SA -Q “BACKUP LOG [demodb] TO DISK = N’/var/opt/mssql/data/demodb_LogBackup.bak’ WITH NOFORMAT, NOINIT, NAME = N’demodb_LogBackup’, NOSKIP, NOREWIND, NOUNLOAD, STATS = 5”

    Restore a database

    In the following example sqlcmd connects to the local instance of SQL Server and restores the demodb database. Note that the NORECOVERY option is used to allow for additional restores of log file backups. If you do not plan to restore additional log files, remove the NORECOVERY option.

    Bash

    sqlcmd -S localhost -U SA -Q “RESTORE DATABASE [demodb] FROM DISK = N’/var/opt/mssql/data/demodb.bak’ WITH FILE = 1, NOUNLOAD, REPLACE, NORECOVERY, STATS = 5”

    Restore the transaction log

    The following command restores the previous transaction log backup.

    Bash

    sqlcmd -S localhost -U SA -Q “RESTORE LOG demodb FROM DISK = N’/var/opt/mssql/data/demodb_LogBackup.bak'”

    Backup and Restore with SQL Server Management Studio (SSMS)

    You can use SSMS from a Windows computer to connect to a Linux database and take a backup through the user-interface.

    The following steps walk through taking a backup with SSMS.

    1. Start SSMS and connect to your server in SQL Server 2017 on Linux.
    2. In Object Explorer, right-click on your database, Click Tasks, and then click Back Up....
    3. In the Backup Up Database dialog, verify the parameters and options, and click OK.

    SQL Server completes the database backup.

    Restore with SQL Server Management Studio (SSMS)

    The following steps walk you through restoring a database with SSMS.

    1. In SSMS right-click Databases and click Restore Databases....
    2. Under Source click Device: and then click the ellipses (…).
    3. Locate your database backup file and click OK.
    4. Under Restore plan, verify the backup file and settings. Click OK.
    5. SQL Server restores the database.