数据库(增量恢复)
生产线的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 intdeclare @hostname nvarchar(255),@pgnamenvarchar(255)declare @flag bitset @reason=isnull(@reason,N'未知')set @flag=0set @sql='declare getspid cursorforselect spid,hostname,program_namefrom 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=1set @cmd='net send '+ltrim(rtrim(@hostname))+' 请及时保存你的程序:'+ltrim(rtrim(@pgname))+'的相关数据,'+'数据库:'+@dbname+'将于'+@delay+'后断开连接.'+'原因:'+@reason print @cmdexec master.dbo.xp_cmdshell@cmdfetch next from getspid into @spid,@hostname,@pgname end close getspiddeallocate getspidif @flag=1 waitfor delay @delayexec (@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 getspidprint 'disconnect db:'+@dbname+' completely!'return 0
python脚本
import loggingimport os,shutil,subprocessimport reimport datetimefrom datetime import timedeltafrom datetime import dateglobal_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 Nonedef 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+filefullnameshutil.copy(hostfile,localfile) else: logging.info('数据库压缩文件:'+filefullname+'不存在') NotifyUser(2,'数据库压缩文件:'+filefullname+'不存在') return None tmpdate=tmpdate+datetime.timedelta(days=1) logging.info('拷贝文件成功') return 1def 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 0def 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 1def 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 0def DisConnectDB(): logging.debug("进入断开数据库连接函数:DisConnectDB") command='Exec DisconnectDB "'+global_sqlserver_db+'","数据库还原","'+global_disconnectdb_delay+'"' print command rst=ExecSQL(command) return rstdef 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 1if __name__ == "__main__": doRestoreDB(3<2) logging.shutdown()
页:
[1]