SQL Server批量还原所有数据库备份时报权限与路径错误如何解决

错误原因 #

• 你以管理员身份运行SSMS不生效的核心原因：SQL Server还原操作的权限是由SQL Server服务的运行账号决定的，和你运行SSMS的账号权限无关
• 第二个报错是因为备份的.bak文件里记录的原始mdf/ldf文件路径、文件名，和你当前SQL Server实例的默认数据路径不匹配，或者存在同名文件冲突，必须指定WITH MOVE参数重定向文件路径

分步解决方法 #

第一步：给SQL Server服务账号开放权限 #

1. 打开Windows服务管理器（运行输入services.msc回车）
2. 找到对应的SQL Server实例服务，名称格式为SQL Server (实例名)，你这里实例名为HMNFRK，对应服务就是SQL Server (HMNFRK)
3. 右键属性切换到「登录」标签，记下服务运行的账号（通常是NT Service\MSSQL$HMNFRK、NETWORK SERVICE或者本地系统账号）
4. 打开SQL Server数据目录C:\Program Files\Microsoft SQL Server\MSSQL15.HMNFRK\MSSQL\DATA的属性，切换到「安全」标签，把刚才记下的服务账号添加进去，授予「完全控制」权限；如果你要把数据文件存到其他路径，也要给对应路径添加相同权限。

第二步：修改还原脚本，添加WITH MOVE逻辑 #

原脚本没有处理mdf和ldf文件的路径映射，可参考下方修改后的版本，你可以先确认当前实例的默认数据和日志路径，需要自定义存储路径直接修改对应变量即可：

DECLARE @FilesCmdshell TABLE (outputCmd NVARCHAR (255))
DECLARE @FilesCmdshellCursor CURSOR
DECLARE @FilesCmdshellOutputCmd AS NVARCHAR(255)
-- 配置当前实例的默认数据、日志文件路径，可自行修改
DECLARE @DataPath NVARCHAR(255) = N'C:\Program Files\Microsoft SQL Server\MSSQL15.HMNFRK\MSSQL\DATA\'
DECLARE @LogPath NVARCHAR(255) = N'C:\Program Files\Microsoft SQL Server\MSSQL15.HMNFRK\MSSQL\DATA\'

INSERT INTO @FilesCmdshell (outputCmd) 
    EXEC master.sys.xp_cmdshell 'dir /B  E:\Projects\SQLBackup\*.bak'

SET @FilesCmdshellCursor = CURSOR FOR 
    SELECT outputCmd FROM @FilesCmdshell WHERE outputCmd IS NOT NULL
 
OPEN @FilesCmdshellCursor
FETCH NEXT FROM @FilesCmdshellCursor INTO @FilesCmdshellOutputCmd

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @DbName NVARCHAR(255) = SUBSTRING(@FilesCmdshellOutputCmd, 0, CHARINDEX('.', @FilesCmdshellOutputCmd))
    -- 构建带WITH MOVE的还原语句，自动映射mdf和ldf路径
    DECLARE @cmd NVARCHAR(MAX) = 'RESTORE DATABASE [' + @DbName + '] 
    FROM DISK = N''E:\Projects\SQLBackup\' + @DbName + '.bak'' 
    WITH FILE = 1, NOUNLOAD, STATS = 10,
    MOVE N''' + @DbName + ''' TO N''' + @DataPath + @DbName + '.mdf'',
    MOVE N''' + @DbName + '_log'' TO N''' + @LogPath + @DbName + '_log.ldf'',
    REPLACE' -- REPLACE参数表示如果存在同名数据库直接覆盖，不需要可以删除
    EXEC(@cmd)
 
    FETCH NEXT FROM @FilesCmdshellCursor INTO @FilesCmdshellOutputCmd
END

额外注意事项 #

• 如果你的数据库备份里的逻辑文件名不是「数据库名」和「数据库名_log」的格式，可单独执行RESTORE FILELISTONLY FROM DISK = N'你的bak文件路径'查看实际的逻辑文件名，再对应修改脚本里的MOVE参数部分
• 如果之前没有开启xp_cmdshell，需要先执行以下语句开启：

sp_configure 'show advanced options', 1
RECONFIGURE
sp_configure 'xp_cmdshell', 1
RECONFIGURE

内容的提问来源于stack exchange，提问作者user3769210