使用PowerShell模块SQL数据库备份– DBATools

This article will be first article of series for SQL database backup and restoration using DBAtools, a powerful open source library of PowerShell automation scripts.

本文将是使用DBAtools（一个强大的PowerShell自动化脚本的开源库）进行SQL数据库备份和还原的系列文章的第一篇。

Every organization should prepare a Disaster Recovery plan to avoid a business loss against any unexpected downtime. It can be in the form of power failure, data centre issues, cyber-attacks, natural disaster etc. We must do a regular disaster drill to be prepared any such incidents. The DBA plays a vital role in these drills.

每个组织都应准备灾难恢复计划，以避免因任何意外停机而造成业务损失。 可能是电源故障，数据中心问题，网络攻击，自然灾害等形式。我们必须定期进行灾难演习，以准备任何此类事件。 DBA在这些演练中起着至关重要的作用。

We used to take regular database backups for production servers to restore later in case of any loss of data. You should do regular database restoration drills to verify that your backup policy is valid and able to recover from any incidents. We need to restore a database backup on the instance on the same version of SQL Server.

我们过去经常为生产服务器进行数据库备份，以备日后在数据丢失的情况下还原。 您应该进行常规的数据库还原演练，以验证您的备份策略有效并且能够从任何事件中恢复。 我们需要在相同版本SQL Server上的实例上还原数据库备份。

If you are maintaining a large number of production instances, it becomes tedious work to test backups on a specified frequency. We also need to perform additional task of consistency check using DBCC CHECKDB command after database restoration. It ensures that there is no allocation or consistency error in the restore database. As a summary, we need to perform two tasks in database restoration drills:

如果要维护大量生产实例，那么以指定频率测试备份将变得很繁琐。 数据库还原后，我们还需要使用DBCC CHECKDB命令执行其他一致性检查任务。 它确保还原数据库中没有分配或一致性错误。 作为总结，我们需要在数据库还原练习中执行两项任务：

1. Restore database regularly from existing backups regularly
   定期从现有备份中还原数据库
2. Database Consistency check on the newly restored database
   对新还原的数据库进行数据库一致性检查

In SQL Server, you can write t-SQL code or create an SSIS package to perform the required steps. You need to be good in programming or SSIS package development to do so.

在SQL Server中，您可以编写t-SQL代码或创建SSIS包以执行所需的步骤。 您需要精通编程或SSIS包开发。

As an alternative, we can use DBATools to maintain database backups, restoration and verifications for disaster recovery purpose. In this article, we will discuss database backups using PowerShell SQL Server module DBATools.

或者，我们可以使用DBATools维护数据库备份，还原和验证，以进行灾难恢复。 在本文中，我们将讨论使用PowerShell SQL Server模块DBATools进行数据库备份。

• Note: We are using Azure Data Studio to run DBATools commands. You can also use Windows PowerShell for this.
• 注意： 我们正在使用Azure Data Studio运行DBATools命令。 您也可以使用Windows PowerShell。

使用DBAToolsSQL数据库备份 (SQL Database backups using DBATools)

We can get all commands related to keyword Backup using Get-help.

我们可以使用Get-help获取与关键字Backup相关的所有命令。

>Get-Help *Backup*

We will verify last database backups using command Get-DbaLastBackup. In this command, we used Out-GridView to get results in grid view.

我们将使用命令Get-DbaLastBackup验证最后的数据库备份。 在此命令中，我们使用Out-GridView在网格视图中获取结果。

>get-help Get-DbaLastBackup

In below screenshot, we can see the syntax, description for this command.

在下面的屏幕截图中，我们可以看到此命令的语法，说明。

Let us run this command in my instance Kashish\SQL2019CTP. In the output, we can see that currently, I do not have any SQL backups for my database instance. It gives timestamp of each database backup in corresponding column such as LastFullBackup, lastDiffbackup, LastLogbackup. It also gives the details of the number of days since last full, differential and log backup.

让我们在实例Kashish \ SQL2019CTP中运行此命令。 在输出中，我们可以看到当前我的数据库实例没有任何SQL备份。 它在相应的列（例如LastFullBackup，lastDiffbackup，LastLogbackup）中提供每个数据库备份的时间戳。 它还提供自上次完整备份，差异备份和日志备份以来的天数的详细信息。

使用DBATools进行数据库备份 (Take a database backup using DBATools)

We can perform database backups using Backup-DBADatabase command using DBATools in PowerShell SQL Server. Check the syntax of Backup-DBADatabase using below command.

我们可以在PowerShell SQL Server中使用DBATools使用Backup-DBADatabase命令执行数据库备份。 使用以下命令检查Backup-DBADatabase的语法。

>get-help Backup-DBADatabase