SqlServer使用数据库脚本，批量还原数据库备份文件

1、查看数据库配置，方便修改后还原回去

-- 查看系统实例级别的配置 （可以先查看配置，使用完后再把配置还原回去）
sp_configure 
go

2、修改数据库配置

-- 使用脚本从一个文件夹中恢复SQL Server中的多个数据库。
-- 但在此之前，请连接到实例并启用 xp_cmdshell。
-- 允许更改高级选项。
EXEC sp_configure 'show advanced options', 1;
GO
-- 更新高级选项的当前配置值。
RECONFIGURE;
GO
-- 启用该功能。
EXEC sp_configure 'xp_cmdshell', 1;
GO
-- 更新此功能的当前配置值。
RECONFIGURE;
GO

3、执行还原操作

-- 查看系统实例级别的配置 （可以先查看配置，使用完后再把配置还原回去）
-- sp_configure 
-- go


-- 使用脚本从一个文件夹中恢复SQL Server中的多个数据库。
-- 但在此之前，请连接到实例并启用 xp_cmdshell。
-- 允许更改高级选项。
EXEC sp_configure 'show advanced options', 1;
GO
-- 更新高级选项的当前配置值。
RECONFIGURE;
GO
-- 启用该功能。
EXEC sp_configure 'xp_cmdshell', 1;
GO
-- 更新此功能的当前配置值。
RECONFIGURE;
GO


-- 开始执行数据库恢复
-- （请将“备份位置”替换为您自己的“包含所有备份文件的文件夹”）
-- 定义一个表变量
DECLARE @FilesCmdshell TABLE (
    outputCmd NVARCHAR (255)
)
-- 定义一个变量表，来存备份文件的属性
DECLARE @backupfile TABLE(
    LogicalName nvarchar(128),
    PhysicalName nvarchar(260),
    Type char(1),
    FileGroupName nvarchar(128),
    Size numeric(20,0),
    MaxSize numeric(20,0),
    FileId bigint,
    CreateLSN numeric(25,0),
    DropLSN numeric(25,0),
    UniqueId uniqueidentifier,
    ReadOnlyLSN numeric(25,0),
    ReadWriteLSN numeric(25,0),
    BackupSizeInBytes bigint,
    SourceBlockSize int,
    FileGroupId int,
    LogGroupGUID uniqueidentifier,
    DifferentialBaseLSN numeric(25,0),
    DifferentialBaseGUID uniqueidentifier,
    IsReadOnly bit,
    IsPresent bit,
    TDEThumbprint varbinary(32),
    SnapshotUrl nvarchar(128)
)
-- 创建游标、文件名变量
DECLARE @FilesCmdshellCursor CURSOR
DECLARE @FilesCmdshellOutputCmd AS NVARCHAR(255)
-- 把该文件夹下面的备份文件写入表变量
INSERT INTO @FilesCmdshell (outputCmd) EXEC master.sys.xp_cmdshell 'dir /B D:\DB\BAK\数据库备份2023\*.bak' -- 备份文件位置
-- 把表变量中的内容都读取到游标中
SET @FilesCmdshellCursor = CURSOR FOR SELECT outputCmd FROM @FilesCmdshell
-- 开启游标
OPEN @FilesCmdshellCursor
FETCH NEXT FROM @FilesCmdshellCursor INTO @FilesCmdshellOutputCmd  -- 把文件名写入 @FilesCmdshellOutputCmd
WHILE @@FETCH_STATUS = 0
BEGIN
    -- 通过bak文件获取数据库实际名称
    declare @dbname nvarchar(255)
    -- 拼接读取配置文件属性的查询语句
    declare @getbackfile nvarchar(max) = 'restore filelistonly from disk = ''D:\DB\BAK\数据库备份2023\'+@FilesCmdshellOutputCmd+''''
    -- 删除变量表之前的数据
    delete @backupfile
    -- 把查询结果存入变量表
    insert into @backupfile
    EXEC(@getbackfile) 
    
    -- 在变量表中查询，得到数据库名
    SET @dbname = (SELECT LogicalName FROM @backupfile WHERE Type='D')
    -- 拼接还原命令
    DECLARE @cmd NVARCHAR(MAX) = 'RESTORE DATABASE [' + @dbname + '] FROM DISK = N''D:\DB\BAK\数据库备份2023\' + @FilesCmdshellOutputCmd + ''' WITH 
        MOVE '''+ @dbname +''' TO ''D:\DB\DATA\local\'+ @dbname +'.mdf'', 
        MOVE '''+ @dbname +'_log'' TO ''D:\DB\DATA\local\'+ @dbname +'_log.ldf'', NOUNLOAD, STATS = 10'
    -- 执行还原命令
    EXEC(@cmd)
    -- 循环便利游标
    FETCH NEXT FROM @FilesCmdshellCursor INTO @FilesCmdshellOutputCmd 
END