使用命令行进行数据库备份

虽然 SQL Server Management Studio (SSMS) 提供了内置的基本备份功能,但在缺少高级功能如维护计划和 SQL Server 代理等的免费 Express 版本中,您可能希望通过命令行执行一些更智能的操作。

在下文中,我将介绍如何使用命令行备份 SQL 数据库以及如何从创建的备份中进行恢复。

如何通过命令行备份 SQL 数据库?

首先,您需要使用 SQLCMD 实用程序在命令提示符中输入 Transact-SQL 语句、系统过程和脚本文件。它随 SQL Server 2014 及更低版本一起提供。但在更高版本中,您需要单独下载。

下载 SQLCMD (x64)

下载 SQLCMD (x32)

然后,您可以打开命令提示符以使用命令行备份 SQL (Express) 数据库。以下是基本的 SQL Server 备份命令行:

sqlcmd -E -S servername -Q "BACKUP DATABASE databasename TO DISK ='filepath'"

在此命令中,-E 表示使用可信连接。如果您想改用用户名和密码,可以将其替换为 -U -P 开关。如果您需要使用其他开关,请点击这里了解更多 SQLCMD 语法。

以下是我使用 SQLCMD 在命名实例中备份名为“example”的数据库的示例:

sqlcmd -E -S .\MSSQLSERVER_01 -Q “将数据库示例备份到磁盘 ='D:\SQL Server\example.bak'”

请注意,我们通常通过服务器名称引用默认实例,并通过服务器名称\实例名称引用命名实例。您还可以通过右键单击一个实例并从菜单中选择“属性”来获取准确的名称。

如果您要备份的不是一个或两个特定的数据库,而是实例中的所有数据库,那么逐个指定它们可能会很痛苦。因此,您可以在 master 数据库中创建一个存储过程,从而简化每次需要使用的备份命令。

首先,请导航到“数据库” > “系统数据库” > 右键单击并选择“新建存储过程”

微软提供了一个脚本SQL Express 备份,您可以将所有内容复制到 SQL Query 窗口中,然后点击“执行”按钮创建一个名为 sp_BackupDatabases 的存储过程。此存储过程指定要备份的数据库,您还可以修改排除条件以满足您的要求。

创建存储过程

例如,如果您使用的是非 Express SQL 版本,并且想要备份实例中除系统数据库之外的所有数据库,则可以使用“DELETE @DBs where DBNAME IN ('master','model','msdb' ,'tempdb')”在每个备份类型下。

成功执行存储过程后,使用以下命令行备份这些数据库:

sqlcmd -S servername -E -Q "EXEC sp_BackupDatabases @backupLocation='filepath', @backupType='F'"

对于 backupType,“F”表示全量备份,“D”表示差异备份,“L”表示事务日志备份。

如果要使用 SQL Login 而不是 Windows 身份验证,请添加 -U -P 开关并删除 -E。

这里我的例子是:

sqlcmd -S .\MSSQLSERVER_01 -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQL Server\', @backupType='F'"

备份完成后,将为每个数据库生成一个 .bak 文件。

如果您需要经常备份相同的数据库,那么每次都在命令提示符中设置备份并不是相对简单的方法。相反,您可以通过 Windows 任务计划程序自动执行该过程。

首先,打开文本编辑器,在其中输入你用于数据库备份的命令,并保存为批处理文件(扩展名为.bat)。然后,转到“控制面板”>“管理工具”>“任务计划程序”>“创建基本任务”,并按照向导创建计划任务。

更具体地说,您需要根据需要选择一个“触发器”,将“操作”选择为“启动程序”,然后找到您创建的批处理文件。详情请参阅自动备份SQL Server数据库.

任务调度器

如何通过命令行恢复 SQL数据库?

使用命令行从备份恢复 SQL数据库同样简单。命令是:

sqlcmd -E -S servername -Q “RESTORE DATABASE databasename FROM DISK='filepath'”

但在实际使用中,您可能会收到“数据库“databasename”的日志尾部尚未备份。如果日志包含您不想丢失的工作,请使用 BACKUP LOG WITH NORECOVERY 备份日志。使用 RESTORE 语句的 WITH REPLACE 或 WITH STOPAT 子句来覆盖日志的内容”。

恢复异常终止

这是因为您尝试恢复的备份文件比您要恢复的数据库旧。解决方法如提示信息所述。

例如,我想恢复旧的备份并简单地覆盖现有数据库,那么我可以使用“WITH REPLACE”开关,它相当于 SSMS 中的“覆盖现有数据库”选项。命令将是这样的:

sqlcmd -E -S .\MSSQLSERVER_01 -Q “RESTORE DATABASE example FROM DISK='D:\SQL Server\example.bak' WITH REPLACE”

备选方案:更简单的SQL Server(Express) 数据库备份解决方案

无论您是为了方便,还是试图在 Express 版本中安排备份,使用命令行数据库备份都是一个不错的解决方案。但是,如果您发现脚本难以掌握,尤其是当您需要备份和恢复多个数据库或实例时,还有一个使用直观 GUI 的简单替代方案。

傲梅企业备份网络版是一个可靠的备份管理解决方案,适用于局域网内的所有台式电脑、笔记本电脑、工作站和服务器。包含数据库备份功能,支持SQL Server 2005 - 2020,让您可以在任何客户端计算机上集中备份和恢复数据库。

要使用它,您需要在中心机上下载并安装傲梅企业备份网络版(这里提供180天免费试用):

接下来,在客户端安装傲梅企业备份网络版,并请求控制这些客户端计算机以进行进一步的操作:

请求控制

如何集中备份 SQL 数据库:

1. 启动傲梅企业备份网络版,导航到“任务”>“新建任务”> SQL Server 备份

sql服务器备份

2. 在“第一步”中点击“+添加计算机”,检测到所有被控制的有 SQL Server 数据库的计算机,然后选择要备份的计算机。

添加电脑

3. 在步骤 2中,单击 + 添加 以检测所选客户端计算机上的所有 SQL Server 实例。在弹出窗口中,您可以选择多个数据库甚至多个实例进行备份。

选择数据库

4. 在第 3 步中,点击“添加存储” 以输入共享或 NAS 路径和相应的身份验证。然后您可以指定一个文件夹作为备份目标。

选择路径

5. 如果你想自动备份,请点击“计划”来配置计划设置,“高级”选项卡也支持完整备份和差异备份。设置完成后,单击“开始备份”以执行任务。

结论

本文提供了关于使用命令行进行数据库备份、自动备份以及从备份中恢复的快速指南。如果您觉得这些步骤繁琐,也有一个替代方案——傲梅企业备份网络版。

该软件简化了备份和恢复的过程,尤其是在您需要自动备份 SQL Server 数据库或将 SQL Server 数据库还原到另一台服务器的情况下。不仅适用于本地服务器,而且您还可以轻松管理局域网内所有计算机的备份任务。