SQL 数据库 使用 Powershell 集变量还原

Question

情况：

我们有一个托管应用程序，我们需要直接访问 SQL 服务器，但无法访问。解决方法是主机通过 SFTP 提供每周备份。

我有一个使用 WinSCP 下载备份的脚本：

CMD 批处理脚本运行以运行 WinSCP脚本(在 WinSCP 目录中保存并运行)

WinSCP.com /script=sftpscript.txt

WinSCP 脚本运行：

open sftp://<<Serveraddress and login>><<REMOTE SFTP DIRECTORY>>
synchronize local -delete "<<LOCAL DIRECTORY" "<<REMOTE SFTP DIRECTORY>>"
exit

下载的备份在文件名末尾有一个时间戳：BACKUP_20170526_222744.BAK

我需要自动查询更新文件名，并将该文件名用作变量来恢复备份。我已经想出了如何在此处提取文件名并在Powershell中设置为变量：

set-location -path '<<LOCAL BACKUP DIRECTORY>>'
$bak = ls | where-object {$_.Name -Like '*.BAK'} | select-object -expandproperty name

要在 TSQL 中恢复数据库，我当前使用以下脚本，但手动输入备份文件名：

USE [master]
ALTER DATABASE [DB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
RESTORE DATABASE [DB] FROM  DISK = N'<<LOCAL DIRECTORY>><<BACKUP FILE>>' WITH  FILE = 1,  MOVE N'DB' TO N'C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATADB.mdf',  MOVE N'DB_log' TO N'C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATADB_log.ldf',  NOUNLOAD,  REPLACE,  STATS = 5
GO
ALTER DATABASE [DB] SET MULTI_USER
GO
USE [DB]
GO
CREATE USER [Reader] FOR LOGIN [Reader]
GO
USE [DB]
GO
ALTER ROLE [db_datareader] ADD MEMBER [Reader]
GO

Powershell 和 TSQL 脚本目前都按预期执行。 Powershell 将$bak变量设置为备份的正确文件名 SQL 脚本将还原备份，但前提是手动输入备份的文件名。

我的问题是在我的 TSQL 脚本中使用 Powershell 变量$bak。

请注意，当涉及到Powershell和TSQL时，我都处于入门级。

以下是根据肖恩·梅尔顿(Shawn Melton)的答案完成此操作的命令：

Restore-DBADatabase -SqlInstance localhost -path 'C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLBackup' -WithReplace -UseDestinationDefaultDirectories
invoke-sqlcmd -inputfile "Permission.sql" -serverinstance localhost

权限脚本

USE [DB]
GO
CREATE USER [Reader] FOR LOGIN [Reader]
GO
USE [DB]
GO
ALTER ROLE [db_datareader] ADD MEMBER [Reader]
GO

此命令在 WinSCP 命令将备份放入目录后使用。

Answer 1

您可以使用一个名为dbatools的基于社区的模块。你想要的命令是Restore-DbaDatabase，完整的代码可以在这里找到。

如果每次只保留一个备份文件进行还原，则只需调用还原函数并将其指向目录即可。它将选取备份，然后还原它，因为它只是一个完整备份。

例如，如果您将备份文件拉取到C:MSSQLBackups，您的 SQL Server 实例也可以访问该目录。您可以运行以下代码：

Import-Module dbatools
Restore-DbaDatabase -SqlServer SQLInstanceName -Path 'C:MSSQLBackups' -WithReplace `
-DestinationDataDirectory 'C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATA' `
-DestinationLogDirectory 'C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATA'

您可以查看命令的帮助以查看其他参数选项。我经常使用的一个是添加-OutputScriptOnly，如果您想在不同的进程/方法中运行它，这将为您生成 T-SQL 脚本。