python还原SQL Server

1、还原数据库resore_db.py

# coding=utf-8

import os,sys,time,random,datetime
from base.db import MSSQL
from base.logger import logger

# 分隔符
delimiter = "-" * 100

# 日志
logname = os.path.basename(sys.argv[0])[:-3]
logdir = "d:\\scripts\\project\\logs\\" + logname
tim = str(time.strftime('%Y%m%d_%H%M%S', time.localtime()))
logfile = logname + "_" + tim + ".log"
logging = logger(logdir,logname,logfile)


def shrinklog(dip):
    mssql = MSSQL(host=dip,user="dumper",pwd="123456",db="master")
    query = "SELECT name FROM Master..SysDatabases Where dbid >6"
    dblist = mssql.ExecQuery(query)
    for db in dblist:
        sql = "USE [%s] ; ALTER DATABASE [%s] SET RECOVERY SIMPLE ;DBCC SHRINKFILE (2, 50) ;ALTER DATABASE [%s] SET RECOVERY FULL;"%(db[0],db[0],db[0])
        mssql.ExecNonQuery(sql)
    
def main():
    # 拉库
    dip = '10.110.12.41'
    iplist = ('11.21.11',)
    dblist = ('MYDB',)
    num = ''
    # uat是否更新sas配置，0否，1是
    sas = 0
    # 0:指定备份时间 1:实时的备份
    flag = 1
    # 指定恢复的时间点，flag=0时有效
    dt = '20180613 17:40:22'
    cur = time.mktime(time.strptime(dt,'%Y%m%d %H:%M:%S'))
    dbdt = datetime.datetime.fromtimestamp(cur).strftime('%Y-%m-%dT%H:%M:%S')
    # 生产备份:dbbackup ,灰度备份:UAT
    env = 'dbbackup'
    pre = '10.'
    dbms = MSSQL(host=dip,user="dumper",pwd="123456",db="master")

    for (i,db) in zip(iplist,dblist):

        ip =  pre + i
        stip='\\\\10.110.0.1\\'
        # 备份目录
        path = stip + '\\dbbackup\\' + ip + '\\' + db + "\\" 
        print(path)
        ti = time.strftime('%Y%m%d_%H%M%S', time.localtime())
        # 实时备份时，第一次备份全备，其它日志备份
        ms = MSSQL(host=ip,user="dumper",pwd="123456",db="master")
        if flag == 1 or not os.path.exists(path):
            # 
            dt = time.strftime('%Y%m%d %H:%M:%S', time.localtime())
            cur = time.mktime(time.strptime(dt,'%Y%m%d %H:%M:%S'))
            dbdt = datetime.datetime.fromtimestamp(cur).strftime('%Y-%m-%dT%H:%M:%S')
            # 时间戳
            time.sleep(5)
            ti = time.strftime('%Y%m%d_%H%M%S', time.localtime())
            if not os.path.exists(path):
                os.makedirs(path)
                # 全量备份
                bakname = path + db + "_" + ti + "_full.bak"
                sql = "backup database [%s] to disk='%s' with checksum,compression"%(db,bakname)
            # 日志备份
            bakname = path + "log\\" + db + "_" + ti + "_log.bak"
            sql = "backup log [%s] to disk='%s' with checksum,compression"%(db,bakname)
            logging.info(sql)
            ms.ExecNonQuery(sql)

        
        x = 0
        y = 0
        z = 9999999999
        fn = ''
        dn = ''
        ll = ''
        ln = []
        # 查找指定备份备份
        f = os.listdir(path)
        for j in range(len(f)):
            fname = path + f[j]
            # 获取最近全量备份
            if  os.path.isfile(fname) and "full" in fname:
                t = os.path.getctime(fname)
                if t < cur:
                    if t > x:
                        x = t
                        fn = fname
        logging.info(datetime.datetime.fromtimestamp(x).strftime("%Y%m%d %H%M%S"))

        for j in range(len(f)):
            fname = path + f[j]
            # 获取最近差异备份
            if  os.path.isfile(fname) and "diff" in fname:
                t = os.path.getctime(fname)
                if t < cur:
                    if t > y:
                        y = t
                        if y > x:
                            dn = fname

        for j in range(len(f)):
            fname = path + f[j]
            # 获取所有日志备份
            if  os.path.isdir(fname) and "log" in fname:
                logpath = path + "log\\"
                flog = os.listdir(logpath)
                for k in range(len(flog)):
                    lname = logpath + flog[k]
                    if os.path.isfile(lname) and "log" in lname:
                        t = os.path.getctime(lname)
                        if t < cur:
                            if t > x and t > y:
                                ln.append(lname)
                        else:
                            if t < z:
                                z = t
                                ll = lname

        if dip == '10.3.128.4':
            dbname = "Event-" + num + db
            datdir='D:\\Program Files\\Microsoft SQL Server\\MSSQL11.MSSQLSERVER\\MSSQL\\DATA\\' 
            dblist = dbms.ExecQuery("SELECT name FROM Master..SysDatabases Where name ='"+dbname+"'")
            if len(dblist)>0:
                dbname = "Event-"  + num + db + "-" +ti


        if ll != "":
            ln.append(ll)
        ln.sort()
        for ii in ln:
            print(ii)
        logging.info(dip + " " + dbname)
               
        sql="restore filelistonly from disk='" + fn + "'"
        logging.info(sql)
        name = dbms.ExecQuery(sql)
        dbn = name[0][0]
        logn = name[1][0]
        rdm = str(random.randint(0,1000))
        dat = datdir + dbname +"_"+rdm+ ".mdf"
        log = datdir + dbname +"_"+rdm+ "_log.ldf"
        #print(dbn,logn,rdm,dat,log)


        #NORECOVERY方式执行全量恢复
        sql = "restore database [%s] from disk='%s' with move '%s' to '%s',move '%s' to '%s',NORECOVERY" %(dbname,fn,dbn,dat,logn,log)
        logging.info(sql)
        try:
            dbms.ExecNonQuery(sql)
        except Exception as e:
            logging.info(e)

        # 
        if dn == "":
            if len(ln) == 0:
                sql = "restore database [%s]" %(dbname)
                logging.info(sql)
                try:
                    dbms.ExecNonQuery(sql)                
                except Exception as e:
                    logging.info(e)
            else:
                # 差异恢复,没有差异备份,直接Recovery\
                for l in range(len(ln)):
                    if l == len(ln) - 1:
                        sql = "restore log [%s] from disk='%s'WITH FILE=1,STOPAT = '%s'" %(dbname,ln[l],dbdt)
                    else:
                        sql = "restore log [%s] from disk='%s'WITH FILE=1,NORECOVERY" %(dbname,ln[l])
                    logging.info(sql)
                    try:
                        dbms.ExecNonQuery(sql)
                    except Exception as e:
                        logging.info(e)
        else:
            if len(ln) == 0:
                sql = "restore database [%s] from disk='%s'WITH FILE=1" %(dbname,dn)
                logging.info(sql)
                try:
                    dbms.ExecNonQuery(sql)
                except Exception as e:
                    logging.info(e)
            else:
                # 差异恢复,没有差异备份,直接Recovery\
                sql = "restore database [%s] from disk='%s'WITH FILE=1,NORECOVERY" %(dbname,dn)
                logging.info(sql)
                try:
                    dbms.ExecNonQuery(sql)
                except Exception as e:
                    logging.info(e)
                for l in range(len(ln)):
                    if l == len(ln) - 1:
                        sql = "restore log [%s] from disk='%s'WITH FILE=1,STOPAT = '%s'" %(dbname,ln[l],dbdt)
                    else:
                        sql = "restore log [%s] from disk='%s'WITH FILE=1,NORECOVERY" %(dbname,ln[l])
                    logging.info(sql)
                    try:
                        dbms.ExecNonQuery(sql)
                    except Exception as e:
                        logging.info(e)
        # 收缩日志
        shrinklog(dip)



if __name__ == '__main__':
    main()

2、base/db.py

# coding=utf-8
# connect db

import pymssql
import sys,os,time
reload(sys)
sys.setdefaultencoding('UTF-8')

class MSSQL:
    def __init__(self, host, user, pwd, db):
        self.host = host
        self.user = user
        self.pwd = pwd
        self.db = db

    def __GetConnect(self):
        # 创建连接

        if not self.db:
            raise (NameError, "没有设置数据库信息")
        self.conn = pymssql.connect(host=self.host, user=self.user, password=self.pwd, database=self.db, charset="utf8")
        cur = self.conn.cursor()
        if not cur:
            raise (NameError, "连接数据库失败")
        else:
            return cur

    def ExecQuery(self, sql):
        # 执行查询语句
        cur = self.__GetConnect()
        cur.execute(sql)
        resList = cur.fetchall()

        self.conn.close()
        return resList

    def ExecNonQuery(self, sql):
        # 执行非查询语句
        cur = self.__GetConnect()
        try:
            self.conn.autocommit(True)
            cur.execute(sql)
            self.conn.autocommit(False)
        except Exception as e:
            print(e)
        self.conn.close()

3、base/logger.py

#!/usr/bin/python  
#coding=utf-8  
  
import logging  
import sys  
import os,time
  
class logger(object):
    """logger"""
    def __init__(self, logdir, logname, logfile):
        self.logger = logging.getLogger(logname)
 
        if os.path.exists(logdir) == False:  
            os.mkdir(logdir)
        log = logdir+'\\'+logfile

        formatter = logging.Formatter('%(asctime)s %(levelname)s: %(message)s',datefmt = '%Y%m%d %X')
        # 文件日志
        file_handler = logging.FileHandler(log)
        file_handler.setFormatter(formatter)
        # 控制台日志
        console_handler = logging.StreamHandler(sys.stdout)
        console_handler.formatter = formatter

        self.logger.addHandler(file_handler)
        self.logger.addHandler(console_handler)

        self.logger.setLevel(logging.INFO)
    def info(self, msg):  
        if self.logger is not None:
            self.logger.info(msg)

4、创建空文件，base/__init__.py

说明：

1、还原操作产生大量的日志，所以还原后一定要进行一次日志收缩

2、实时还原时，备份事务日志然后还原到当前时间点，非实时还原时，指定时间点进行 还原

3、base/__init__.py文件必须要，否则restore_db.py无法调用db.py和logger.py