数据库(增量恢复)

生产线的sqlserver数据库infoservice星期日做全盘备份,备份名为:infoservice_database.7,星期一做差异备份:infoservice_database.1,星期二:infoservice_database.2...
   对应的本地模拟库infoserviceonline要定期恢复,恢复顺序与备份顺序相反. 如今天是星期一,要先还原
上个星期日的全盘备份,再还原今天的差异备份.
   本程序支持在数据库服务器恢复，也支持在工作站恢复.



断开数据库的存储过程(创建于master库):

Create proc DisconnectDB(@dbname varchar(20),@reason nvarchar(200)=null,@delay varchar(8)='00:00:30')     
declare    @sql    nvarchar(500),@cmd nvarchar(500)     
declare    @spid    int
declare    @hostname nvarchar(255),@pgname  nvarchar(255)
declare    @flag bit
set @reason=isnull(@reason,N'未知')  
set @flag=0
set    @sql='declare getspid   cursor  for  select spid,hostname,program_name  from    master..sysprocesses    where    dbid=db_id('''+@dbname+''')'     
exec    (@sql)     
open    getspid 
print 'ready'   
fetch    next    from    getspid    into    @spid,@hostname,@pgname     
while    @@fetch_status <>-1     
begin  
  set @flag=1
  set @cmd='net send '+ltrim(rtrim(@hostname))+' 请及时保存你的程序:'+ltrim(rtrim(@pgname))+'的相关数据,'+'数据库:'+@dbname+'将于'+@delay+'后断开连接.'+'原因:'+@reason 
  print @cmd
  exec master.dbo.xp_cmdshell  @cmd
  fetch    next    from    getspid    into    @spid,@hostname,@pgname     
end     
close    getspid
deallocate    getspid
if @flag=1
   waitfor delay @delay
exec   (@sql)     
open   getspid     
fetch    next    from    getspid    into    @spid,@hostname,@pgname     
while    @@fetch_status <>-1     
begin     
  exec('kill    '+@spid) 
  fetch    next    from    getspid    into    @spid,@hostname,@pgname     
end     
close    getspid     
deallocate    getspid
print 'disconnect db:'+@dbname+' completely!'
return 0     

python脚本

import logging
import os,shutil,subprocess
import re
import datetime
from datetime import timedelta
from datetime import date

global_log='D:\\tmp\\RestoreInfoServiceDB.log'
global_smb_db_filepath='\\\\192.168.0.84\\f$\\ftproot\\infoservice.online\\'
global_localtmpdir='d:\\tmp\\'
global_notify_user=['192.168.0.129','192.168.0.124']
global_sqlserver_user='sa'
global_sqlserver_password='hpsystem'
global_sqlserver_source='192.168.0.86'
global_sqlserver_db='infoserviceonline'
global_disconnectdb_delay='00:00:30'

# filemode='r',只读，清除原有内容（默认方式）   filemode='r+',读写，清除原有内容
# filemode='w',只写，创建一个新的文件覆盖旧的   filemode='w+',读写，创建一个新的文件覆盖旧的
# filemode='a',只写，追加                    filemode='a+',读写，追加
# filemode='b',二进制模式
logging.basicConfig(level=logging.DEBUG,format='[%(asctime)s] [%(levelname)s] %(message)s',filename=global_log,filemode='a')
logging.getLogger(global_log)
def NotifyUser(level,message):
    logging.debug("进入消息通知函数:NotifyUser")
    if level==1:
        messagekind='提示:'
    if level==2:
        messagekind='警告:'
    if level==3:
        messagekind='错误:'
    for user in global_notify_user:
        hint=messagekind+message
        cmd=['net','send',user,hint]
        rst=subprocess.call(cmd)

def search_file(filename, search_path):
    logging.debug("进入文件搜索函数:search_file")
    dbfilelist=os.listdir(search_path)
    for dbfile in dbfilelist:
       
 p=re.compile(filename)
 m=p.match(dbfile)
 if m:
     if os.path.exists(search_path+m.group()):
                return m.group()
     else:
                continue
    return None

def CopyFileFromSmbToLocal(host,local,format):
    logging.debug("进入文件拷贝函数:CopyFileFromSmbToLocal")
    i=date.today().weekday()
    tmpdate=date.today()-datetime.timedelta(days=i+1)
    while tmpdate<=date.today():
        filename=tmpdate.strftime(format)
        filefullname=search_file(filename,host)
        if filefullname:
            localfile=local+filefullname
     if os.path.exists(localfile):
                tmpdate=tmpdate+datetime.timedelta(days=1)
                continue
            else:
                hostfile=host+filefullname
  shutil.copy(hostfile,localfile)
 else:
            logging.info('数据库压缩文件:'+filefullname+'不存在')
     NotifyUser(2,'数据库压缩文件:'+filefullname+'不存在')
     return None
 tmpdate=tmpdate+datetime.timedelta(days=1)
    logging.info('拷贝文件成功')
    return 1
def pickfile(filename):
    logging.debug("进入文件提取函数:pickfile")
    destfile='*infoservice_database.*'
    cmd=['winrar','e',filename,destfile,'-r','e',global_localtmpdir]
    rst=subprocess.call(cmd)
    if rst==0:
        return 1
    else:
 return 0
def Decompress(local,format):
    logging.debug("进入解压函数:Decompress")
    i=date.today().weekday()
    tmpdate=date.today()-datetime.timedelta(days=i+1)
    i=tmpdate.weekday()
    while tmpdate<=date.today():
        localdbfile='infoservice_database.'+str(i+1)
        filename=tmpdate.strftime(format)
        filefullname=search_file(filename,local)
        if os.path.exists(local+localdbfile):
            tmpdate=tmpdate+datetime.timedelta(days=1)
            i=tmpdate.weekday()
            continue
        else:
            source=local+filefullname
            pickfile(source)
        tmpdate=tmpdate+datetime.timedelta(days=1)
        i=tmpdate.weekday()
    logging.info('解压文件成功')
    return 1

def RemoveOldFile(local):
    logging.debug("进入删除旧文件函数:RemoveOldFile")
    i=date.today().weekday()
    if i==6 or i==0:
        logging.info('del '+local+'infoservice_database.*')
        logging.info('del '+local+'*.bz2')
        os.system('del '+local+'infoservice_database.*')
        os.system('del '+local+'*.bz2')
    return 1
       
def ExecSQL(command):
    logging.debug("进入SQL语句执行函数:ExecSQL")
    logging.info("执行的SQL语句:"+command)
    cmd=['isql','/U',global_sqlserver_user,'/P',global_sqlserver_password,'/S',global_sqlserver_source,'/d','master','/Q',command]
    rst=subprocess.call(cmd)
    if rst==0:
        return 1
    else:
 return 0
def DisConnectDB():
    logging.debug("进入断开数据库连接函数:DisConnectDB")
    command='Exec DisconnectDB "'+global_sqlserver_db+'","数据库还原","'+global_disconnectdb_delay+'"'
    print command
    rst=ExecSQL(command)
    return rst

def doRestoreDB(IsInHost):
    logging.debug("进入还原主函数:doRestoreDB")
    RemoveOldFile(global_localtmpdir)
    CopyFileFromSmbToLocal(global_smb_db_filepath,global_localtmpdir,'backup-%Y%m%d\d{6}\.tar\.bz2')
    Decompress(global_localtmpdir,'backup-%Y%m%d\d{6}\.tar\.bz2')
    if not IsInHost:
        hostLocation='\\\\'+global_sqlserver_source+'\\'
        hostLocation=hostLocation+global_localtmpdir.replace(':','$')
        os.system('del '+hostLocation+'infoservice_database.*')
        os.system('copy '+global_localtmpdir+'infoservice_database.* '+hostLocation)
    result=DisConnectDB()
    if result==0:
       NotifyUser(3,'还原库失败,原因:断开连接失败')
       return -3
    i=date.today().weekday()
    tmpdate=date.today()-datetime.timedelta(days=i+1)
    j=tmpdate.weekday()
    while tmpdate<=date.today():
        localdbfile='infoservice_database.'+str(j+1)
        filefullname=global_localtmpdir+localdbfile
        if os.path.exists(filefullname):
            if j+1==7:
                cmd='restore database '+global_sqlserver_db+' from Disk="'+filefullname+'" with file=1, NORECOVERY'
                if tmpdate==date.today():
                    cmd='restore database '+global_sqlserver_db+' from Disk="'+filefullname+'" with RECOVERY'
            else:
                cmd='restore database '+global_sqlserver_db+' from Disk="'+filefullname+'" with file=1,NORECOVERY'
                if tmpdate==date.today():
                   cmd='restore database '+global_sqlserver_db+' from Disk="'+filefullname+'" with file=1,RECOVERY'    
            rst=ExecSQL(cmd)
            if rst==0:
                NotifyUser(3,'还原库失败,原因:执行'+cmd+'失败')
                return -1
        else:
             NotifyUser(3,'还原库失败,原因:文件'+filefullname+'不存在')
             return -2
        tmpdate=tmpdate+datetime.timedelta(days=1)
        j=tmpdate.weekday()
    NotifyUser(1,'还原库'+global_sqlserver_db+'成功')
    logging.info('还原库'+global_sqlserver_db+'成功')
    return 1
if __name__ == "__main__":
    doRestoreDB(3<2)
    logging.shutdown()