经过一些改编的 Collet 脚本对我有用。
首先通过运行以下 SQL 命令启用 xp_cmdshell:
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1;
GO
-- To update the currently configured value for advanced options.
RECONFIGURE;
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1;
GO
-- To update the currently configured value for this feature.
RECONFIGURE;
GO
接下来,更新以下脚本以从备份文件恢复数据库。代替C:\Backup\
与包含 .BAK 文件的本地目录并替换C:\Microsoft SQL Server\SQLINSTANCE\MSSQL\DATA\
与您的 SQL Server 数据目录。该脚本还假设 .BAK 文件名与数据库名称匹配,否则脚本可能会抛出有关逻辑文件名不匹配的错误。
DECLARE @FilesCmdshell TABLE (
outputCmd NVARCHAR (255)
)
DECLARE @FilesCmdshellCursor CURSOR
DECLARE @FilesCmdshellOutputCmd AS NVARCHAR(255)
INSERT INTO @FilesCmdshell (outputCmd) EXEC master.sys.xp_cmdshell 'dir /B C:\Backup\*.bak'
SET @FilesCmdshellCursor = CURSOR FOR SELECT outputCmd FROM @FilesCmdshell
OPEN @FilesCmdshellCursor
FETCH NEXT FROM @FilesCmdshellCursor INTO @FilesCmdshellOutputCmd
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @sqlRestore NVARCHAR(MAX) = 'RESTORE DATABASE [' + SUBSTRING(@FilesCmdshellOutputCmd, 0, CHARINDEX('.', @FilesCmdshellOutputCmd)) + '] FROM DISK = N''C:\Backup\' + SUBSTRING(@FilesCmdshellOutputCmd, 0, CHARINDEX('.', @FilesCmdshellOutputCmd)) + '.bak'' WITH FILE = 1, MOVE N''' + SUBSTRING(@FilesCmdshellOutputCmd, 0, CHARINDEX('.', @FilesCmdshellOutputCmd)) + ''' TO N''C:\Microsoft SQL Server\SQLINSTANCE\MSSQL\DATA\' + SUBSTRING(@FilesCmdshellOutputCmd, 0, CHARINDEX('.', @FilesCmdshellOutputCmd)) + '.mdf'', MOVE N''' + SUBSTRING(@FilesCmdshellOutputCmd, 0, CHARINDEX('.', @FilesCmdshellOutputCmd)) + '_log'' TO N''C:\Microsoft SQL Server\SQLINSTANCE\MSSQL\DATA\' + SUBSTRING(@FilesCmdshellOutputCmd, 0, CHARINDEX('.', @FilesCmdshellOutputCmd)) + '_log.ldf'', NOUNLOAD, STATS = 10'
EXEC(@sqlRestore)
FETCH NEXT FROM @FilesCmdshellCursor INTO @FilesCmdshellOutputCmd
END