linux恢复数据库,备份和还原 Linux 上的 SQL Server 数据库

备份和还原 Linux 上的 SQL Server 数据库Backup and restore SQL Server databases on Linux

11/14/2017

本文内容

适用于：Applies to: SQL ServerSQL Server(所有支持的版本)SQL ServerSQL Server (all supported versions) - Linux适用于：Applies to: SQL ServerSQL Server(所有支持的版本)SQL ServerSQL Server (all supported versions) - Linux

可以使用多个不同的选项备份 Linux 上的 SQL Server 2017 中的数据库。You can take backups of databases from SQL Server 2017 on Linux with many different options. 在 Linux 服务器上，可以使用 sqlcmd 连接到 SQL Server 并进行备份。On a Linux server, you can use sqlcmd to connect to the SQL Server and take backups. 如果是 Windows，则可以在连接到 Linux 上的 SQL Server 后通过用户界面进行备份。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.

重要

Linux 上的 SQL Server 仅支持使用块 blob 备份到 Azure Blob 存储。SQL Server on Linux only supports backing up to Azure Blob storage using block blobs. 使用存储密钥进行备份和还原将导致使用页 blob，而这不受支持。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. 有关块 blob 和页 blob 的信息，请参阅备份到块 blob 和页 blob。For information on block blogs versus page blogs, see Backup to block blob vs. page blob.

备份数据库Backup a database

下例使用 sqlcmd 连接到本地 SQL Server 实例，并对名为 demodb 的用户数据库进行完整备份。In the following example sqlcmd connects to the local SQL Server instance and takes a full backup of a user database called demodb.

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"

运行该命令后，SQL Server 将提示输入密码。When you run the command, SQL Server will prompt for a password. 输入密码后，shell 将返回备份进度结果。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. 下例使用 sqlcmd 连接到本地 SQL Server 实例，并对事务日志进行备份。In the following example, sqlcmd connects to the local SQL Server instance and takes a transaction log backup.

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

下例使用 sqlcmd 连接到 SQL Server 本地实例，并对 demodb 数据库进行还原。In the following example sqlcmd connects to the local instance of SQL Server and restores the demodb database. 请注意，NORECOVERY 选项用于其他日志文件备份的还原。Note that the NORECOVERY option is used to allow for additional restores of log file backups. 如果不打算还原其他日志文件，请删除 NORECOVERY 选项。If you do not plan to restore additional log files, remove the NORECOVERY option.

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"

提示

如果意外使用 NORECOVERY，但没有其他日志文件备份，请运行不含其他参数的 RESTORE DATABASE demodb 命令。If you accidentally use NORECOVERY but do not have additional log file backups, run the command RESTORE DATABASE demodb with no additional parameters. 这将完成还原，并使数据库正常运行。This finishes the restore and leaves your database operational.

还原事务日志Restore the transaction log

以下命令将还原以前的事务日志备份。The following command restores the previous transaction log backup.

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

使用 SQL Server Management Studio (SSMS) 备份和还原Backup and Restore with SQL Server Management Studio (SSMS)

可以从 Windows 计算机使用 SSMS 连接到 Linux 数据库并通过用户界面进行备份。You can use SSMS from a Windows computer to connect to a Linux database and take a backup through the user-interface.

备注

使用最新版本的 SSMS 连接到 SQL Server。Use the latest version of SSMS to connect to SQL Server. 若要下载和安装最新版本，请参阅下载 SSMS。To download and install the latest version, see Download SSMS. For more information on how to use SSMS, see Use SSMS to Manage SQL Server on Linux.

下列步骤将引导你使用 SSMS 完成备份。The following steps walk through taking a backup with SSMS.

启动 SSMS 并连接到 Linux 上的 SQL Server 2017 中的服务器。Start SSMS and connect to your server in SQL Server 2017 on Linux.

在“对象资源管理器”中，右键单击数据库，单击“任务”，然后单击“备份...”。In Object Explorer, right-click on your database, Click Tasks, and then click Back Up....

在“备份数据库”对话框中，验证参数和选项，然后单击“确定”。In the Backup Up Database dialog, verify the parameters and options, and click OK.

SQL Server 将完成数据库备份。SQL Server completes the database backup.

使用 SQL Server Management Studio (SSMS) 还原Restore with SQL Server Management Studio (SSMS)

以下步骤将引导你使用 SSMS 完成数据库还原。The following steps walk you through restoring a database with SSMS.

在 SSMS 中，右键单击“数据库”，然后单击“还原数据库...”。In SSMS right-click Databases and click Restore Databases....

单击“源”下的“设备:”，然后单击省略号 (...)。Under Source click Device: and then click the ellipses (...).

查找数据库备份文件，然后单击“确定”。Locate your database backup file and click OK.

在“还原计划”下，验证备份文件和设置。Under Restore plan, verify the backup file and settings. 单击“确定”。Click OK.

SQL Server 将还原数据库。SQL Server restores the database.

另请参阅See also