如何用Python将.bak文件恢复到MSSQL？权限报错求助

我尝试用Python将.bak文件恢复到MSSQL时遇到瓶颈，试过多种方法并排查权限后仍失败，但通过MS Server Management Studio手动恢复正常。现在需要实现恢复和删除的自动化，用于批量处理数据库并执行查询。

环境信息：

• Anaconda环境下的Python 3.11.5
• Jupyter Notebook
• 个人技术经验一般

已做大量研究未找到解决方案，附上代码及报错：

import pyodbc

server_name = 'DESKTOP-OEBL7M5\FYRE'
database_name = 'shell'
target_database_name = 'Hoosker_Doo_6H'
windows_authentication = True
username = 'DESKTOP-OEBL7M5\BKR'
password = None
backup_file_path = 'C:\Database\Raw Data Files\New Database Files'

if windows_authentication:
    conn_str = f'DRIVER={{SQL Server}};SERVER={server_name};DATABASE={database_name};Trusted_Connection=yes;'
else:
    conn_str = f'DRIVER={{SQL Server}};SERVER={server_namer};DATABASE={database_name};UID={username};PWD={password}'

try:
    conn = pyodbc.connect(conn_str)
    cursor = conn.cursor()
except Exception as e:
    print(f"Error: {e}")

restore_query = f'''
RESTORE DATABASE {target_database_name}
FROM DISK = '{backup_file_path}'
WITH REPLACE, RECOVERY;
'''

try:
    conn.autocommit = True
    cursor.execute(restore_query)
    print(f"Databse '{target_database_name}' restored successfully!")
except Exception as e:
    print(f"Error: {e}")
finally:
    conn.autocommit = False
    if conn is not None:
        conn.close()

Error: ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open backup device 'C:\Database\Raw Data Files\New Database Files'. Operating system error 5(Access is denied.). (3201) (SQLExecDirectW); [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE DATABASE is terminating abnormally. (3013)")

问题分析与解决方案 #

核心问题

报错提示的权限拒绝（错误5），本质是SQL Server服务账户没有访问备份文件路径的权限：手动恢复时使用的是当前Windows用户的权限，而Python通过Windows认证连接SQL Server后，RESTORE命令是由SQL Server的服务账户执行的，该账户没有目标路径的访问权限。

另外代码中还存在几个明显问题：

1. 路径字符串未转义：Python中单斜杠\会被解析为转义字符，导致实际传递给SQL的路径错误；
2. 变量名拼写错误：conn_str中的server_namer应为server_name；
3. 备份路径指向目录而非具体.bak文件：SQL Server需要明确的备份文件路径，而非目录。

解决步骤

1. 配置SQL Server服务账户权限

   • 打开服务（运行services.msc），找到SQL Server (FYRE)（你的实例名），查看其登录身份；
   • 右键备份文件所在目录C:\Database\Raw Data Files\New Database Files，选择属性-安全-编辑-添加，找到上述SQL Server服务账户，赋予读取和写入权限；
   • 确保路径指向具体的.bak文件，比如C:\Database\Raw Data Files\New Database Files\your_db.bak。

2. 修正代码中的错误
   使用原始字符串避免转义问题，修正变量名，并添加数据库占用处理：

import pyodbc

server_name = r'DESKTOP-OEBL7M5\FYRE'
database_name = 'shell'
target_database_name = 'Hoosker_Doo_6H'
windows_authentication = True
username = r'DESKTOP-OEBL7M5\BKR'
password = None
# 改为原始字符串，且指向具体.bak文件
backup_file_path = r'C:\Database\Raw Data Files\New Database Files\your_db.bak'

conn = None
try:
    if windows_authentication:
        conn_str = f'DRIVER={{SQL Server}};SERVER={server_name};DATABASE={database_name};Trusted_Connection=yes;'
    else:
        # 修正变量名错误
        conn_str = f'DRIVER={{SQL Server}};SERVER={server_name};DATABASE={database_name};UID={username};PWD={password}'

    conn = pyodbc.connect(conn_str)
    cursor = conn.cursor()

    # 先将目标数据库设置为单用户模式，避免被占用
    cursor.execute(f"ALTER DATABASE {target_database_name} SET SINGLE_USER WITH ROLLBACK IMMEDIATE;")

    restore_query = f'''
    RESTORE DATABASE {target_database_name}
    FROM DISK = '{backup_file_path}'
    WITH REPLACE, RECOVERY;
    '''

    conn.autocommit = True
    cursor.execute(restore_query)
    # 恢复完成后设回多用户模式
    cursor.execute(f"ALTER DATABASE {target_database_name} SET MULTI_USER;")
    print(f"数据库 '{target_database_name}' 恢复成功！")
except Exception as e:
    print(f"错误: {e}")
finally:
    if conn is not None:
        try:
            conn.autocommit = False
            conn.close()
        except Exception as e:
            print(f"关闭连接时出错: {e}")

3. 额外排查点
   • 若仍报错，检查备份文件是否被其他程序占用；
   • 确认SQL Server实例是否有权限访问该磁盘（比如网络共享盘需要额外配置）。

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