sql备份恢复数据库_使用DBATools通过SQL恢复数据库操作验证备份-CSDN博客

sql备份恢复数据库

In this article, we will explore database backup validation by with SQL restore database operations using DBATools.

在本文中，我们将探讨通过使用DBATools进行SQL恢复数据库操作来进行数据库备份验证。

DBA should regularly conduct database restore drills for production database backups. We might face a situation where we need to restore the database from previous backups, but it fails because the backup file is corrupt. To avoid that type of situation, we should restore databases and perform consistency check using the DBCC CHECKDB command.

DBA应该定期进行数据库还原演练，以进行生产数据库备份。我们可能会遇到需要从以前的备份还原数据库的情况，但是由于备份文件已损坏，该操作失败。为了避免这种情况，我们应该使用DBCC CHECKDB命令还原数据库并执行一致性检查。

If you are managing a large production environment, it is challenging to conduct restoration drills for each database. You can write custom scripts to do this task, but again it will require expertise in writing t-SQL queries.

如果您要管理大型生产环境，则对每个数据库进行恢复演练是一项挑战。您可以编写自定义脚本来执行此任务，但是再次需要在编写t-SQL查询方面的专业知识。

DBATools中的Test-DbaLastBackup函数 (Test-DbaLastBackup function in DBATools)

In my previous article, SQL Restore Database using DBATools, we explored the open source PowerShell module DBATools for database backup and restoration. We can perform disaster recovery drills using the Test-DbaLastBackup command of DBATools in PowerShell. In this article, we will explore Test-DbaLastBackup and its usage for SQL restore database drills.

在上一篇文章《使用DBATools进行SQL恢复数据库》中，我们探讨了用于数据库备份和恢复的开源PowerShell模块DBATools 。我们可以使用PowerShell中DBATools的Test-DbaLastBackup命令执行灾难恢复演练。在本文中，我们将探讨Test-DbaLastBackup及其在SQL还原数据库演练中的用法。

In the following screenshot, you can see all commands related to backups in DBATools.

在以下屏幕截图中，您可以在DBATools中查看与备份有关的所有命令。

We can get more information about Test-DbaLastBackup using the Get-help command.

我们可以使用Get-help命令获得有关Test-DbaLastBackup的更多信息。

Test-DbaLastBackup function in DBATools help page

We can test the last set of full backups in a SQL instance using the Test-DbaLastBackup command.

我们可以使用Test-DbaLastBackup命令在SQL实例中测试最后一组完整备份。

It performs the following steps

它执行以下步骤

It gathers last full backup information for the specified database. If we do not specify a particular database, it gathers information for all databases
它收集指定数据库的最后完整备份信息。如果我们未指定特定数据库，则它将收集所有数据库的信息
dbatools-testrestore-$databaseName. We can specify a custom database name as well dbatools-testrestore- $ databaseName。 我们也可以指定一个自定义数据库名称
It also renames the logical and physical file of a new database. It ensures no conflict with database filenames
它还重命名了新数据库的逻辑和物理文件。它确保与数据库文件名不冲突
It performs DBCC CHECKTABLE to Checks the integrity of all the pages and structures for all tables in a new database
它执行DBCC CHECKTABLE来检查新数据库中所有表的所有页和结构的完整性
In the last step, it drops the newly restored database
在最后一步，它将删除新还原的数据库

示例1：对SQL实例中的所有数据库执行Test-DbaLastBackup命令 (Example 1: Execute Test-DbaLastBackup command for all databases in SQL instance)

In this example, we want to perform a SQL restore database operation on the same instance. We need to specify the SQL instance name in parameter -SqlServer. In the following query, we will use Out-GridView format to get a user-friendly output.

在此示例中，我们要在同一实例上执行SQL恢复数据库操作。我们需要在参数-SqlServer中指定SQL实例名称。在下面的查询中，我们将使用Out-GridView格式获取用户友好的输出。

>-SqlServer Kashish\SQL2019CTP | Out-GridView

Note: We should have sufficient free space in the drive. I would recommend the SQL restore database operation on a different instance for validation purpose.
注意： 驱动器中应该有足够的可用空间。 我建议对其他实例执行SQL恢复数据库操作以进行验证。

Once you execute this command, it shows database restoration stats, and you can see the percentage competition status as well.

一旦执行此命令，它将显示数据库还原统计信息，并且您还可以看到竞争状态百分比。

Execute Test-DbaLastBackup command for all databases in SQL instance

While restoration is in progress, right-click on the databases and refresh it. You can see a new database in the format of dbatools-testrestore-$databaseName. This database will be in restoring mode. In the following screenshot, you can see database restoration is going on for WideWorldImporters database.

在还原过程中，右键单击数据库并刷新它。您可以看到dbatools-testrestore- $ databaseName格式的新数据库。 该数据库将处于还原模式。在以下屏幕截图中，您可以看到WideWorldImporters数据库的数据库恢复正在进行中。

New database created by Test-DbaLastBackup command

In the following grid view, we can see the following information

在下面的网格视图中，我们可以看到以下信息

For example, we took AdventureWorks017 database backup in C:\SqlShack folder and SQLShackDemo database backup in C:\Temp folder. We can see the last backup location in BackupFiles column. It restores database SQL Server on the specified instance and performs validation checks.
例如，我们在C：\ SqlShack文件夹中进行了AdventureWorks017数据库备份，在C：\ Temp文件夹中进行了SQLShackDemo数据库备份。我们可以在BackupFiles列中看到最后一个备份位置。它在指定实例上还原数据库SQL Server并执行验证检查。
false status in RestoreResult and 错误的状态，并在DbccResut中看到Skipped status in DbccResut. It can skip database restoration due to the following tasks 跳过的状态。由于以下任务，它可以跳过数据库还原
- No full backups for specified database
  没有指定数据库的完整备份
- Backup files do not exist in a backup directory
  备份目录中不存在备份文件

Let us perform a full backup for the Master database and execute Test-DbaLastBackup again. In the following screenshot, we can it restores system databases backup as well.

让我们对Master数据库执行完整备份，然后再次执行Test-DbaLastBackup 。在以下屏幕截图中，我们还可以还原系统数据库备份。

It restores the master database as dbatools-testrestore-master but does not perform DBCC CHECKTABLE on this database.

它将主数据库恢复为dbatools-testrestore-master，但不对此数据库执行DBCC CHECKTABLE 。

We can check the error logs in SQL Server (Expand Management -> SQL Server Logs). In the error logs, you can find an entry for database restoration, recovery and consistency check as well.

我们可以在SQL Server中检查错误日志（扩展管理-> SQL Server日志）。在错误日志中，您还可以找到用于数据库还原，恢复和一致性检查的条目。

示例2：还原特定数据库并使用Test-DbaLastBackup对其进行验证 (Example 2: Restore a particular database and validate it using Test-DbaLastBackup )

In the previous example, we did not specify any database name for restoration and validation check. Suppose we want to perform validation test for a particular database. We can specify database name using -Databases parameter. In the following query, we want to perform a SQL restore database operation for consistency check for AdventureWorks2017 database.

在前面的示例中，我们没有为还原和验证检查指定任何数据库名称。假设我们要对特定数据库执行验证测试。我们可以使用-Databases参数指定数据库名称。在以下查询中，我们要执行SQL恢复数据库操作以对AdventureWorks2017数据库进行一致性检查。

Execute the following command in PowerShell.

在PowerShell中执行以下命令。

>Test-DbaLastBackup -SqlServer Kashish\SQL2019CTP -Databases AdventureWorks2017

It starts restoration for particular database only.

它仅针对特定数据库开始还原。

Restore database SQL Server - Examples of Test-DbaLastBackup command for a single database

In the output, you can see the status for only AdventureWorks2017 database.

在输出中，您只能看到AdventureWorks2017数据库的状态。

示例3：一致性检查后不要删除已还原的数据库 (Example 3: Do not drop the restored database after consistency check)

As specified earlier, the Test-DbaLastBackup command drops the restored the database after performing a consistency check on it. Suppose, once the database consistency check is completed, we do not want to drop it. It should be in an online state and accessible to users.

如前所述， Test-DbaLastBackup命令在对数据库执行一致性检查后将其删除。假设一旦数据库一致性检查完成，我们就不想删除它。它应该处于在线状态，并且用户可以访问。

We can specify a parameter -NoDrop to accomplish this task for us. Let us run Test-DbaLastBackup to perform a SQL restore database operation of AdventureWorks2017(last full backup) database, perform consistency on it and keep it online afterwards. Execute the following command with -NoDrop parameter.

我们可以指定参数-NoDrop为我们完成此任务。让我们运行Test-DbaLastBackup来执行AdventureWorks2017（最后一次完整备份）数据库SQL恢复数据库操作，对其进行一致性，然后使其保持联机状态。使用-NoDrop参数执行以下命令。

>Test-DbaLastBackup -SqlServer Kashish\SQL2019CTP -Databases AdventureWorks2017 -NoDrop

Examples of Test-DbaLastBackup command with NoDrop

Now, connect to a SQL instance, and we can see online database dbatools-testrestore-AdventureWorks2017.

现在，连接到SQL实例，我们可以看到在线数据库dbatools-testrestore-AdventureWorks2017 。

示例4：使用Test-DbaLastBackup为还原的数据库指定自定义名称 (Example 4: Specify a Custom name for the restored database using Test-DbaLastBackup)

In previous examples, once we perform a SQL restore database operation using Test-DBAlastBackup, it gives database name in the format of dbatools-testrestore-$databaseName.

在前面的示例中，一旦我们使用Test-DBAlastBackup执行SQL还原数据库操作，它将以dbatools-testrestore- $ databaseName的格式给出数据库名称。

Suppose we do not want to use a default name for the restored database. In this example, I require to have a database in the format of DRDrill_$databasename. We can do this by specifying a name using -Prefix parameter.

假设我们不想为还原的数据库使用默认名称。在此示例中，我需要具有DRDrill_ $ databasename格式的数据库 。我们可以通过使用-Prefix参数指定名称来实现。

In the following command, we specified prefix DRDrill along with parameter -Nodrop to keep a database in online status.

在以下命令中，我们指定了前缀DRDrill以及参数-Nodrop来使数据库保持在线状态。

>Test-DbaLastBackup -SqlServer Kashish\SQL2019CTP -Databases AdventureWorks2017 -NoDrop -Prefix DRDrill

Restore database SQL Server - Examples of Test-DbaLastBackup command with Prefix

In the following screenshot, you can verify database name in the format of DRDrill_$databasename.

在下面的屏幕快照中，您可以使用DRDrill_ $ databasename格式验证数据库名称。

Examples of Test-DbaLastBackup command with Prefix

示例4：在不还原数据库的情况下验证备份 (Example 4: Verify backups without database restoration)

In SQL Server, we can verify using the RESTORE VERIFYONLY command to verify database backups. It performs internal validation of backup file and ensures it is not corrupt.

在SQL Server中，我们可以使用RESTORE VERIFYONLY命令进行验证以验证数据库备份。它执行备份文件的内部验证，并确保它没有损坏。

We can do this using a parameter -VerifyOnly in Test-DbaLastBackup command. It does not perform a SQL restore database operation and performs validations only.

我们可以在Test-DbaLastBackup命令中使用参数-VerifyOnly进行此操作。它不执行SQL恢复数据库操作，而仅执行验证。

>Test-DbaLastBackup -SqlServer Kashish\SQL2019CTP -Databases AdventureWorks2017  -Prefix DRDrill -Verifyonly

We should have a unique name for a restored database. If a database already exists, it gives the following message.

对于还原的数据库，我们应该具有唯一的名称。如果数据库已经存在，则会显示以下消息。

WARNING: DRDrill-AdventureWorks2017 already exists on KASHISH\SQL2019CTP – skipping

警告：DASHrill-AdventureWorks2017已存在于KASHISH \ SQL2019CTP上–正在跳过

Let us drop database DRDrill-AdventureWorks2017 and rerun the command.

让我们删除数据库DRDrill-AdventureWorks2017并重新运行命令。

In the following screenshot, you can see the status is Verify successful.

在以下屏幕截图中，您可以看到状态为“ 验证成功”。

Examples of Test-DbaLastBackup command with VerifyOnly

示例5：使用Test-DbaLastBackup识别损坏的数据库备份 (Example 5: identify corrupt database backup using Test-DbaLastBackup)

For this example, I have corrupted the last full backup file of the Master database. I opened the file using Edit plus and written random characters in the backup file. Once changes are done, save and exist file.

对于此示例，我已损坏了Master数据库的最后一个完整备份文件。我使用Edit plus打开文件，并在备份文件中写入随机字符。更改完成后，保存并存在文件。

Note: Please do not perform this step on production database backup files. It might damage the backup file permanently.

注意：请不要对生产数据库备份文件执行此步骤。这可能会永久损坏备份文件。

Let us run Test-DbaLastBackup command with -VerifyOnly parameter.

让我们运行带有-VerifyOnly参数的Test-DbaLastBackup命令。

>Test-DbaLastBackup -SqlServer Kashish\SQL2019CTP -Databases Master  -Prefix DRDrill -VerifyOnly

In the following screenshot, we can see that backup file verification failed. We cannot use this backup file for our SQL restore database operation.

在以下屏幕截图中，我们可以看到备份文件验证失败。我们不能将此备份文件用于SQL还原数据库操作。

Examples of Test-DbaLastBackup command for backup verifiation failure

Let us try to restore this backup file with the following command.

让我们尝试使用以下命令还原此备份文件。

>Test-DbaLastBackup -SqlServer Kashish\SQL2019CTP -Databases Master  -Prefix DRDrill -NoDrop

We get a detailed error message.

我们收到详细的错误消息。

Restore database SQL Server - Examples of Test-DbaLastBackup command for backup verifiation failure

示例6：将数据库文件还原到指定目录 (Example 6: Restore database files to a specified directory)

By default, Test-DbaLastBackup restores the database in default directories. To verify the default path right click on SQL Instance and properties. In the properties page, go to Database Settings and verify database default locations.

默认情况下， Test-DbaLastBackup将数据库还原到默认目录中。要验证默认路径，请右键单击“ SQL实例和属性”。在属性页中，转到“ 数据库设置”，然后验证数据库的默认位置。

Previously, we restored a SQL Server database without specifying database file locations. Once restoration is completed, verify the file path. You can see database files in default locations.

以前，我们在不指定数据库文件位置的情况下还原了SQL Server数据库。恢复完成后，请验证文件路径。您可以在默认位置看到数据库文件。

Suppose we do not want to create restored databases in a specified directory. We want to create data and log file in C:\TEMP\Backup folder. We can specify a directory using DataDirectory and LogDirectory parameter.

假设我们不想在指定目录中创建还原的数据库。我们要在C：\ TEMP \ Backup文件夹中创建数据和日志文件。我们可以使用DataDirectory和LogDirectory参数指定目录。

>Test-DbaLastBackup -SqlServer Kashish\SQL2019CTP -Databases AdventureWorks2017  -Prefix DRDrill -NoDrop -DataDirectory C:\TEMP\Backup -LogDirectory C:\TEMP\Backup

Examples of Test-DbaLastBackup command to restore on a specific directory

Verify file locations using the sp_helpfile command in a restored database. In the following screenshot, we can verify files exist in specified directories.

在还原的数据库中使用sp_helpfile命令验证文件位置。在以下屏幕截图中，我们可以验证文件是否存在于指定目录中。

We can specify destination SQL instance name using -Destination parameter.

我们可以使用-Destination参数指定目标SQL实例名称。

示例7：将输出导出到文件中 (Example 7: Export output in a file)

If we are have a large number of databases, we want to get a SQL restore database. We want further validation results in a file instead of getting it on screen. We can export the output of Test-DbaLastBackup in various formats using Out-file.

如果我们有大量数据库，则希望获得一个SQL恢复数据库。我们希望在文件中提供更多的验证结果，而不是将其显示在屏幕上。我们可以使用Out-file以各种格式导出Test-DbaLastBackup的输出。

以文本格式导出结果 (Export result in a Text format)

>Test-DbaLastBackup -SqlServer Kashish\SQL2019CTP -Databases AdventureWorks2017  -Prefix DRDrill -DataDirectory C:\TEMP\Backup -LogDirectory C:\TEMP\Backup | <strong>Out-file c:\temp\results.txt</strong>

It does not provide any output on the screen if we specify the output file.

如果我们指定输出文件，它在屏幕上不提供任何输出。

Export output of Test-DbaLastBackup commandin a text file

Now, you can go to the path and open a text file in a notepad.

现在，您可以转到路径并在记事本中打开一个文本文件。

以CSV格式导出结果 (Export result in a CSV format)

Execute the following command to get output in a CSV format.

执行以下命令以CSV格式获取输出。

>Test-DbaLastBackup -SqlServer Kashish\SQL2019CTP -Databases AdventureWorks2017  -Prefix DRDrill -DataDirectory C:\TEMP\Backup -LogDirectory C:\TEMP\Backup | Export-CSV c:\temp\results.csv -NoTypeInformation

Go to the path and open the CSV file to view the output.

转到路径并打开CSV文件以查看输出。

将结果导出为HTML文件 (Export result in an HTML file)

>Test-DbaLastBackup -SqlServer Kashish\SQL2019CTP -Databases AdventureWorks2017  -Prefix DRDrill -DataDirectory C:\TEMP\Backup -LogDirectory C:\TEMP\Backup | ConvertTo-Html | Out-File c:\temp\results.html

Export output of Test-DbaLastBackup commandin a HTML file

结论 (Conclusion)

In this article, we explored useful commands in PowerShell module DBATools to validate the last full backups by with a SQL restore database operation and performed a consistency check on it. We should regularly perform this kind of checks on backup files to safeguard you against backup corruption issues that might come to a later date. I suggest you explore these commands in your environment and be familiar with them.

在本文中，我们探索了PowerShell模块DBATools中的有用命令，以使用SQL恢复数据库操作来验证最后的完整备份，并对其执行一致性检查。我们应该定期对备份文件执行这种检查，以保护您免受以后可能发生的备份损坏问题的困扰。我建议您在您的环境中探索这些命令并熟悉它们。