SQL Server 2016：自动生成批量完全还原数据库SQL脚本

对备份了20个数据库进行还原，逐个点击鼠标还原效率太低。
故制作了一个自动生成批量还原数据库SQL脚本的程序：

#coding:utf-8
import pymssql


# 连接SQL SERVER服务器
host, user, password, database = 'SQL Server IP地址', 'sa', 'SA密码', 'master'
conn = pymssql.connect(host, user, password, database)  #获取连接
cursor = conn.cursor() # 获取光标


# 获取系统数据库的集合
# sql = "select count(*) from uh_portal_system_version_info"
# sql = "select @@version"
# sql = "select * from master.sys.availability_replicas"
sql = "select name from sysdatabases"

cursor.execute(sql)
row = cursor.fetchall()
db_set = set()
for i in row:
    db_set.add(i[0])
print("系统现有数据库列表：%s \n" % db_set)


# 查询默认Data路径
sql = u'''DECLARE @Result NVARCHAR(4000)
EXECUTE [master].[dbo].xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',N'DefaultData',
@Result OUTPUT,
'NO_OUTPUT'
select @Result'''

cursor.execute(sql)
row = cursor.fetchall()
path_data = row[0][0] + '\\'


# 查询默认Log路径
sql = u'''DECLARE @Result NVARCHAR(4000)
EXECUTE [master].[dbo].xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',N'DefaultLog',
@Result OUTPUT,
'NO_OUTPUT'
select @Result'''

cursor.execute(sql)
row = cursor.fetchall()
path_log = row[0][0] + '\\'


# 查询默认Backup路径
sql = u'''DECLARE @Result NVARCHAR(4000)
EXECUTE [master].[dbo].xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory',
@Result OUTPUT,
'NO_OUTPUT'
select @Result'''

cursor.execute(sql)
row = cursor.fetchall()
path_bak = row[0][0] + '\\'


print("默认DATA文件路径：%s, 默认LOG文件路径：%s, 默认备份文件夹：%s\n" % (path_data, path_log, path_bak))



# 查询 restore_dir 下的.bak文件列表：
restore_dir = "E:\\file\\"
query_bak = "Declare @Table table (filename varchar(500),Depth int, is_file int)" 
query_bak += "Insert into @Table EXEC xp_dirtree  '%s',0,1" % restore_dir
query_bak += "Select filename From @Table where is_file=1 and filename like '%.bak'" 
cursor.execute(query_bak)
row = cursor.fetchall()
bak_set = set()
for i in row:
    bak_set.add(restore_dir + i[0])
print("计划还原 %s 文件夹下的 %s \n" % (restore_dir, bak_set))


print("\n自动生成的还原脚本如下：\n")
for bak_file in bak_set:
    sql = "RESTORE FILELISTONLY FROM DISK='%s'" % bak_file
    cursor.execute(sql)
    row = cursor.fetchall()
    Move_Str = ''
    for item in row:
        if item[2] == 'D': # 数据文件
            dst_filename = item[1].split('\\')[-1]
            Move_Str += "MOVE N'%s' TO N'%s', " % (item[0], path_data + dst_filename)

        if item[2] == 'L': # 日志文件
            dst_filename = item[1].split('\\')[-1]
            Move_Str += "MOVE N'%s' TO N'%s', " % (item[0], path_log + dst_filename)

    db_name = row[0][0].split('_')[0]
    if db_name in db_set:
        print("-- 注意：数据库 [%s] 已经在此系统上运行，请慎重还原！！！" % db_name)
    a = "RESTORE DATABASE [%s] FROM  DISK = N'%s' WITH  FILE = 1, " % (db_name, bak_file )
    c = "NOUNLOAD,  REPLACE,  STATS = 5"

    sql = a + Move_Str + c + '\n'
    print(sql)


conn.close()

运行效果如下：