对备份了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()