This article will cover SQL restore database operations using the open-source PowerShell module, DBAtools, and will cover commands for backup restoration using the command Restore-DBABackup with many various permutations like restoring from file, separate directory, renaming databases, norecovery options and more
本文将介绍使用开源PowerShell模块DBAtoolsSQL恢复数据库操作，并介绍使用Restore-DBABackup命令进行备份恢复的命令，其中包括许多种排列方式，例如从文件还原，单独的目录，重命名数据库，norecovery选项等等。
In my previous article, SQL Database Backups using PowerShell Module – DBATools, We explored taking SQL database backups using PowerShell Module DBATools. Database administrators used to get a frequent request for restoring a database to another instance. We also require backup restoration to recover from corruption, accidental data deletion, disaster recovery scenario. Usually, we restore backups using the SSMS GUI method or t-SQL commands. DBATools can also be a useful tool for SQL restore database operations.
在上一篇文章“ 使用PowerShell模块SQL数据库备份– DBATools”中 ，我们探讨了使用PowerShell模块DBATools进行SQL数据库备份。 数据库管理员过去经常收到将数据库还原到另一个实例的请求。 我们还要求备份还原以从损坏，意外数据删除，灾难恢复方案中恢复。 通常，我们使用SSMS GUI方法或t-SQL命令还原备份。 DBATools也是SQL恢复数据库操作的有用工具。
In this article, we will explore SQL restore database operations using PowerShell Module DBATools.
在本文中，我们将探索使用PowerShell模块DBATools进行SQL还原数据库的操作。
DBATools命令用于备份还原 (DBATools commands for backup restoration)
Let us view command related to keyword Restore in DBATools using Get-Help.
让我们使用Get-Help查看与DBATools中的关键字Restore相关的命令。
We will explore restoration in a further step.
我们将进一步探索恢复。
Restore-DbaBackup (Restore-DbaBackup)
We can perform SQL restore database operations using specified backup files using this command. We have multiple configuration options in this command. We can get syntax as well as brief information of Restore-DbaDatabase with the following command.
我们可以使用此命令使用指定的备份文件执行SQL恢复数据库操作。 此命令中有多个配置选项。 我们可以使用以下命令获取Restore-DbaDatabase的语法以及简要信息。
>Get-help Restore-DbaDatabase
It gives the following output.
它给出以下输出。
We will explore multiple SQL restore database scenarios using DBATools in a further section.
在下一节中，我们将使用DBATools探索多个SQL还原数据库方案。
从放置在目录中的备份文件还原数据库 (Restore database from backup files placed in a directory)
Suppose we have a backup directory having SQL database full backups and we want to restore databases from these backup files.
假设我们有一个具有SQL数据库完整备份的备份目录，并且我们想从这些备份文件中还原数据库。
In the following screenshot, you can see I have three full backups’ files for SQLShackDemo database.
在下面的屏幕快照中，您可以看到我有三个用于SQLShackDemo数据库的完整备份文件。
- SQLShackDemo-Full-201903170927.bak (backup time – 03/17/2019 09:27 AM) SQLShackDemo-Full-201903170927.bak（备份时间– 03/17/2019 09:27 AM）
- SQLShackDemo-Full-201903170904.bak (backup time – 03/17/2019 09:04 AM) SQLShackDemo-Full-201903170904.bak（备份时间– 03/17/2019 09:04 AM）
- SQLShackDemo-Full-201903170857.bak (backup time – 03/17/2019 08:57 AM) SQLShackDemo-Full-201903170857.bak（备份时间– 03/17/2019 08:57 AM）
In Restore-DbaDatabase command, specify SQL instance name and backup directory. Let us see if DBATool tries to restore all backup files.
在Restore-DbaDatabase命令中，指定SQL实例名称和备份目录。 让我们看看DBATool是否尝试还原所有备份文件。
>Restore-DbaDatabase -SqlInstance Kashsish\SQL2019CTP -Path C:\TEMP\Backup
I am trying to restore a database on source instance. We get an error message that data, log file already exists, and we need to specify WithReplace option to replace the existing database.
我正在尝试在源实例上还原数据库。 我们收到一条错误消息，指出数据，日志文件已经存在，并且需要指定WithReplace选项来替换现有数据库。
>Restore-DbaDatabase -SqlInstance Kashish\SQL2019CTP -Path C:\TEMP\Backup -WithReplace
In the following screenshot, you can notice it restores SQLShackDemo-Full-201903170927.bak. Restore-DbaDatabase command scans the files in the specified directory and then filters backup files to prepare a database restoration plan. In my case, we have full backups for SQLShackDemo database taken at different time. It scans all files and chooses the latest full backup file.
在以下屏幕截图中，您可以注意到它还原了SQLShackDemo-Full-201903170927.bak。 Restore-DbaDatabase命令扫描指定目录中的文件，然后过滤备份文件以准备数据库还原计划。 就我而言，我们在不同时间为SQLShackDemo数据库制作了完整备份。 它会扫描所有文件并选择最新的完整备份文件。
Once the SQL Restore database operation is complete, it gives useful information as an output of it.
SQL Restore数据库操作完成后，它将提供有用的信息作为其输出。
- BackupFileName BackupFileName
- Database Name 数据库名称
- Backup Size 备份大小
- Compressed backup size 压缩后的备份大小
- Script: it also gives you the SQL script used by DBATools for restore database in our example. You can also copy this script to use further restoration 脚本：在我们的示例中，它还为您提供了DBATools用于还原数据库SQL脚本。 您也可以复制此脚本以进行进一步还原
从备份文件中还原数据库，其中数据和日志文件位于单独的目录中 (Restore database from backup files with data and log files in a separate directory)
In the previous example, we have used the database default directory to restore a database backup. Suppose we want to use a separate directory. We want to replace the existing database as well. We need to specify a new directory in -DestinationDataDirectory parameter.
在前面的示例中，我们使用了数据库默认目录来还原数据库备份。 假设我们要使用一个单独的目录。 我们也想替换现有的数据库。 我们需要在-DestinationDataDirectory参数中指定一个新目录。
Execute the following code in PowerShell.
在PowerShell中执行以下代码。
>Restore-DbaDatabase -SqlInstance Kashish\SQL2019CTP -Path C:\TEMP\Backup -DestinationDataDirectory C:\TEMP\DB -WithReplace
In the following output, we can verify RestoreDirectory reflects new specified directory.
在以下输出中，我们可以验证RestoreDirectory反映了新的指定目录。
We can go to this directory and verify that a database log and data file exists in this.
我们可以转到该目录并验证其中是否存在数据库日志和数据文件。
Currently, we placed both data and log file in the same directory while doing SQL restore database operations. As per best practice, in a production environment, we place the log file in a separate directory. Let us do another restore database with the following directories.
当前，在执行SQL恢复数据库操作时，我们将数据和日志文件都放在同一目录中。 按照最佳实践，在生产环境中，我们将日志文件放在单独的目录中。 让我们使用以下目录创建另一个还原数据库。
- DestinationDataDirectory parameter DestinationDataDirectory参数指定
- DestinationLogDirectory parameter DestinationLogDirectory参数指定
In the following code, we specified both directories. Execute this in PowerShell.
在以下代码中，我们指定了两个目录。 在PowerShell中执行此操作。
>Restore-DbaDatabase -SqlInstance Kashish\SQL2019CTP -Path C:\TEMP\Backup -DestinationDataDirectory C:\TEMP\DB -DestinationLogDirectory C:\TEMP\DB\Log -WithReplace
从备份文件中将数据库还原为新数据库，并使用DBATools重命名逻辑文件名 (Restore database from backup files as a new database and rename logical file names using DBATools)
In the previous example, we restored the database on an existing SQL Server database with a REPLACE option. Many times, we need to restore the database on same instance with a new database name. Suppose we want to restore last full backup of SQLShackDemo database as SQLShackDemo_Restore.
在上一个示例中，我们使用REPLACE选项在现有SQL Server数据库上还原了该数据库。 很多时候，我们需要使用新的数据库名称在同一实例上还原数据库。 假设我们要将SQLShackDemo数据库的最后完整备份还原为SQLShackDemo_Restore。
Before we start a SQL restore database operation, execute a sp_helpfile command to get a list of logical and physical file names in our source database.
在开始SQL还原数据库操作之前，执行sp_helpfile命令以获取源数据库中逻辑和物理文件名的列表。
In the following command, we specified a new database name with parameter -DatabaseName and specified parameter –ReplaceDbNameInFile.
在以下命令中，我们使用参数-DatabaseName和指定的参数– ReplaceDbNameInFile指定了新的数据库名称。
>Restore-DbaDatabase -SqlInstance Kashish\SQL2019CTP -Path C:\TEMP\Backup -DatabaseName "SQLShackDemo_Restore" -ReplaceDbNameInFile
This command restores a database with a new name and stores physical files with new names.
此命令使用新名称恢复数据库，并使用新名称存储物理文件。
Let us run the command sp_helpfile. We can verify logical and physical file names.
让我们运行命令sp_helpfile 。 我们可以验证逻辑和物理文件名。
In the screenshot, you can observe that physical file name changed as per the new database name. We can see the logical name similar to the source database. We should have different logical file names for each database. We can change logical file names for the restored database using DBATools command Rename-DbaDatabase.
在屏幕截图中，您可以观察到物理文件名已根据新数据库名称更改。 我们可以看到类似于源数据库的逻辑名称。 每个数据库应该有不同的逻辑文件名。 我们可以使用DBATools命令Rename-DbaDatabase更改已还原数据库的逻辑文件名。
In the following query, we specified LogicalName in the format <DBN>_<FT>. Once we execute this command, SQL Server will replace DBN with database name and add suffix _FT. We should have a logical name of database files as SQLShackDemo_Restore and SQLShackDemo_Restore_Log.
在以下查询中，我们以<DBN> _ <FT>格式指定了LogicalName 。 执行完此命令后，SQL Server将用数据库名称替换DBN并添加后缀_FT。 我们应该将数据库文件的逻辑名称命名为SQLShackDemo_Restore和SQLShackDemo_Restore_Log。
>Rename-DbaDatabase -SqlInstance Kashish\SQL2019CTP -Database "SQLShackDemo_Restore" -LogicalName "<DBN>_<FT>"
It gives the following output. We cannot see the modified name in this command output.
它给出以下输出。 我们在此命令输出中看不到修改后的名称。
Rerun sp_helpfile command on SQLShackDemo_Restore database. We can see modified logical file names in the following screenshot.
在SQLShackDemo_Restore数据库上重新运行sp_helpfile命令。 我们可以在以下屏幕截图中看到修改后的逻辑文件名。
从备份文件组合还原数据库（全差异备份和日志备份） (Restore database from combination of backup files (Full differential and log backups) )
In a production environment, we take multiple database backups for a single database to keep RPO and RTO in mind. Suppose we have a large database having a size in TB’s. Usually, a DBA follows the following approach for SQL Restore database operations in a large production database.
在生产环境中，我们为单个数据库进行多个数据库备份，以牢记RPO和RTO。 假设我们有一个大型数据库，其大小为TB。 通常，DBA遵循以下方法在大型生产数据库中执行SQL Restore数据库操作。
- Weekly Full backup 每周完整备份
- Daily differential backup 每日差异备份
- Hourly transaction log backups 每小时事务日志备份
In the following screenshot, I have taken all these backups for SQLShackDemo database for this demo with a small interval.
在下面的屏幕快照中，我以很小的间隔为该演示制作了SQLShackDemo数据库的所有这些备份。
If we are doing SQL restore database operations using SSMS or t-SQL, we need to specify each backup file and prepare the restore chain for it. We can do multiple backup restorations with an intelligent PowerShell module DBATool.
如果我们正在使用SSMS或t-SQL进行SQL恢复数据库操作，则需要指定每个备份文件并为其准备恢复链。 我们可以使用智能PowerShell模块DBATool进行多个备份还原。
Once we specify directory has all backups, it scans all backup files. It further goes through each file and prepares a restoration plan automatically for us.
一旦我们指定目录具有所有备份，它将扫描所有备份文件。 它进一步遍历每个文件并自动为我们准备恢复计划。
Let us execute database backup using Restore-DbaDatabase command and observe the behaviour.
让我们使用Restore-DbaDatabase命令执行数据库备份并观察其行为。
In the following command, we want to restore all backup files placed in the specified directory. We want to create a new database for restoration.
在以下命令中，我们要还原放置在指定目录中的所有备份文件。 我们要创建一个新的数据库进行还原。
>Restore-DbaDatabase –SqlInstance Kashish\SQL2019CTP –Path C:\Temp\Backup –DatabaseName “SQLShackDemo_Restore_1” –ReplaceDbNameInFile
In the output, you can notice that after performing a scan, it starts a backup of individual files in the correct order. The correct order for restoration is as follows.
在输出中，您会注意到执行扫描后，它将以正确的顺序开始备份单个文件。 正确的还原顺序如下。
- Restore Full database backup with NoRecovery 使用NoRecovery恢复完整的数据库备份
- Restore differential database backup with NoRecovery 使用NoRecovery恢复差异数据库备份
- Restore log backup with Recovery 使用恢复还原日志备份
Once it finishes restoration for all backup files, we get the following output for each backup files.
完成所有备份文件的还原后，我们将为每个备份文件获得以下输出。
- NoRecovery: True. It shows that further backups will be restored on this database NoRecovery：True。 它显示进一步的备份将在此数据库上还原
- NoRecovery: True to restore the database in NoRecovery mode NoRecovery：True ，以NoRecovery模式还原数据库
- NoRecovery: False. It shows that the database will be online after log backup restoration. It does not require any further restoration for this database NoRecovery：False。 它显示日志备份还原后数据库将联机。 此数据库不需要任何进一步的还原
SQL在NoRecovery模式下还原数据库操作 (SQL restore database operations in NoRecovery Mode)
We might not want a database to be available after backup restoration. Suppose you are maintaining another copy of the database for a disaster recovery purpose. You want to apply regular transaction log backup on this database. You might consider this approach as a manual log shipping approach.
备份还原后，我们可能不希望数据库可用。 假设您正在维护数据库的另一个副本以用于灾难恢复。 您想对此数据库应用常规的事务日志备份。 您可能会将此方法视为手动日志传送方法。
We can fulfil this requirement using DBATool as well. We can use -NoRecovery parameter in restore database command.
我们也可以使用DBATool满足此要求。 我们可以在restore database命令中使用-NoRecovery参数。
In the following command, we want to restore backups placed in a specified directory with mentioned database name in NoRecovery mode. Execute the following command in PowerShell.
在以下命令中，我们要以NoRecovery模式还原具有指定数据库名称的指定目录中的备份。 在PowerShell中执行以下命令。
>Restore-DbaDatabase -SqlInstance Kashish\SQL2019CTP -Path c:\Temp\backup -DatabaseName SQLShack_NoRecovery -NoRecovery
Once a SQL restore database operation is completed, you can connect to SQL instance and verify database status. In the following screenshot, you can see a status for the highlighted database as Restoring.
SQL还原数据库操作完成后，您可以连接到SQL实例并验证数据库状态。 在以下屏幕截图中，您可以看到突出显示的数据库的状态为“正在还原”。
We are doing regular restoring differential or log backup on this database. We should be able to recover a database and bring it online when required. We can easily perform recovery on this database with parameter –Recover. We do not need to specify any backup files while recovering this database.
我们正在对此数据库上定期还原差异备份或日志备份。 我们应该能够恢复数据库并在需要时使其联机。 我们可以使用参数–Recover轻松地对该数据库执行恢复。 恢复此数据库时，我们不需要指定任何备份文件。
>Restore-DbaDatabase –SqlInstance Kashish\SQL2019CTP –Recover –DatabaseName SQLShack_NoRecovery
In the output, you can see the script for this command. In this script, we can see With Recovery Clause. It performs recovery and brings the database online.
在输出中，您可以看到此命令的脚本。 在此脚本中，我们可以看到With Recovery Clause。 它执行恢复并使数据库联机。
Refresh SQL instance in SSMS, and we can see database is showing online now.
在SSMS中刷新SQL实例，我们可以看到数据库现在正在在线显示。
结论 (Conclusion)
DBATools is an excellent tool to perform SQL restore database. We can efficiently manage the restoration task using this PowerShell module. I suggest you go through it and be familiar with DBATool. We will continue covering more on DBATools in my further articles.
DBATools是执行SQL恢复数据库的出色工具。 我们可以使用此PowerShell模块有效地管理还原任务。 我建议您仔细阅读并熟悉DBATool。 在我的其他文章中，我们将继续涵盖DBATools的更多内容。
目录 (Table of contents)
翻译自: https://www.sqlshack.com/sql-restore-database-using-dbatools/
7750
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
