以下为Python应用解决SQL SERVER指定数据备份还原的方案。
公司最近需要每天还原线上的试用通道网站，但是又要保留部分表，比如新闻内容。 数据库是SQL SERVER的，
那么该如何做呢？ 我比较擅长python，那就用python实现程序的备份还原吧。 再加到windows cron里面。
该程序解决了备份还原sql server数据库， 但是保留新闻表内容。
1.备份当前新闻表
2.备份当前数据库
3. 数据库还原历史纯净备份。 事务处理，若失败自动回滚
4. 清理新闻表。 事务处理，若失败自动回滚
5. 恢复旧新闻表。 若恢复失败，邮件通知supporter
6. 检查网站可用性
7. 若可用性检查网站发现错误，立即邮件通知supporter
8. 自动重启iis web，再次检查网站可用性
9. 将最后网站可用性状态发送邮件给supporter
注: 以上若程序遭遇任何异常，自动退出执行。
#!/usr/bin/python
#coding=utf8
import os
import pymssql
import urllib
import time
import smtplib
from email.mime.text import MIMEText
from email.header import Header
from email.MIMEMultipart import MIMEMultipart
import email.MIMEBase
from email import Encoders
#Email module
class Sendmail:
def Sendemails(self,status):
print status
sender = 'xxxxxxxx@xxxxx.xx'
receiver = '1036040496@qq.com'
subject = "xxx RESTORE TASK Exception" + time.strftime('%Y-%m-%d %H:%M:%S')
smtpserver = 'smtp.exmail.qq.com'
username = 'xxxxxxxx@xxxxx.xx'
password = 'password'
msg = MIMEText('<html><body>\
<p>Hello,</p>\
<p>     This is sytem email. After restored the OA database. The website is not ok now.</p>\
<p>     Have found error proxy code ' + str(status) +'. Please check by supporters urgently.</p>\
<p></p>\
<p>               \
               \
               \
System From host xxx</p>\
</body></html>','html','utf-8')#中文需参数‘utf-8’，单字节字符不需要
msg['Subject'] = subject
msg['From'] = "XXX Admin"
msg['To'] = receiver
msg['date'] = time.strftime('%a, %d %b %Y %H:%M:%S %z')
smtp = smtplib.SMTP()
smtp.connect(smtpserver)
smtp.login(username, password)
smtp.sendmail(sender, receiver, msg.as_string())
smtp.quit()
print("send monitor email ok")
#备份当前新闻表xx开头的表
print "备份当前新闻相关表"
list=['xx','xx1,'xx2','xx3','xx4']
for x in list:
tb_querysql="select * from [数据库名].dbo."+x
print tb_querysql
try:
if os.system("bcp.exe "+"\""+tb_querysql+"\""+" queryout " +x+".bcp -N -S localhost -T -E") == 0:
print "bcp.exe "+"'"+tb_querysql+"'"+" queryout " +x+".bcp -N -S localhost -T -E"
print "备份当前新闻相关表成功"
else:
print "backup news tables failure"
except Exception,e:
print "备份当前新闻相关表失败"
print e
print "程序遇到异常退出"
exit()
#备份当前数据库
backup_sql="backup database 数据库名 to disk='E:\OA_backup_restore\数据库名.bak_"+time.strftime('%Y-%m-%d_%H%M%S')+"'"
conn = pymssql.connect(host="localhost:SQLSERVER服务端口",user="sa",password="数据库密码",database="master",charset="utf8")
cur=conn.cursor()
if not cur:
raise(NameError,"数据库连接失败")
else:
print "数据库连接成功"
print "备份当前数据库"
try:
conn.autocommit(True)
cur.execute(backup_sql)
print "备份当前数据库成功"
except Exception,e:
print "备份遇到程序异常"
print e
try:
print "Try to online the database"
cur.execute("ALTER DATABASE 数据库名称 SET ONLINE")
print "Re-try to backup database"
cur.execute(backup_sql)
except Exception,e:
print "备份遇到程序异常"
print e
print "程序遇到异常退出"
exit()
#数据库还原
#----Make Database to single user Mode
single_mode='''ALTER DATABASE 数据库名称
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE'''
print "单用户模式"
try:
cur.execute(single_mode)
print "成功进入单用户模式"
except Exception,e:
print "进入单用户模式失败"
print e
print "程序遇到异常退出"
exit()
#查询logicalname
#search_logicalname='''RESTORE FILELISTONLY FROM DISK="E:\备份及程序路径\数据库名称.bak"'''
#print "查询数据库logicalname"
#try:
# cur.excute(search_logicalname)
# resList=cur.fetchall()
# for (logicalname,physicalname) in resList:
# print logicalname,physicalname
# print "查询数据库logicalname成功"
#except Exception,e:
# print "查询数据库logicalname失败"
# print e
# print "程序遇到异常退出"
# exit()
#还原数据库
#注: 此处逻辑名和物理名由以上RESTORE FILELISTONLY FROM DISK="E:\备份及程序路径\数据库名称.bak 查询获得
#----Restore Database
logicalname1="XXX"
physicalname1="C:\XXX_DATA\XXX.mdf"
logicalname2="XXX_Log"
physicalname2="C:\XXX_DATA\XXX_log.ldf"
restore_db='''RESTORE DATABASE XXX
FROM DISK = 'E:\备份及程序路径\数据库名称_new_cleanbackup'
WITH MOVE \'%s\' TO \'%s\',MOVE \'%s\' TO \'%s\'''' %(logicalname1,physicalname1,logicalname2,physicalname2)
print restore_db
print "还原初始化数据库"
try:
x=""
cur.execute(restore_db)
print "还原初始化数据库成功"
except Exception,x:
print "还原初始化数据库失败"
print x
finally:
#切换回多用户模式
print "切回多用户模式"
switch_multimode="ALTER DATABASE 数据库名称 SET MULTI_USER"
cur.execute(switch_multimode)
if x != "":
print "Program meet exceptions and exit now"
exit()
#清理新闻表
for y in list:
exec_pro="use 数据库名称;delete from dbo.%s" %y
print "清理新闻表"
try:
e1=""
cur.execute("begin tran")
print exec_pro
cur.execute(exec_pro)
print "清理新闻表成功"
except Exception,e1:
print "清理新闻表失败"
cur.execute("rollback tran")
print e1
finally:
if e1 != "":
print "Program meet exceptions and exit now"
exit()
else:
cur.execute("commit tran")
print "Have done news table %s clean and go on." %y
#恢复旧新闻相关表
#恢复前检查数据是否已经清空
print "恢复旧新闻备份表"
try:
os.system("bcp.exe [数据库名称].dbo."+y+" in "+y+".bcp -N -S localhost -T -E -b 10000")
print ("bcp.exe [数据库名称.dbo."+y+" in "+y+".bcp -N -S localhost -T -E -b 10000")
print "恢复旧新闻表成功"
except Exception,e:
print "恢复旧新闻表失败"
sd=Sendmail()
sd.Sendemails(e)
print e
print "程序遇到异常退出"
exit()
#检查网站可用性
try:
status=urllib.urlopen("http://网站url").code
if status == 200:
print "website is ok!!!"
else:
sd=Sendmail()
sd.Sendemails(status)
os.system("cscript.exe c:\\inetpub\\AdminScripts\\adsutil.vbs STOP_SERVER W3SVC/1")
os.system("cscript.exe c:\\inetpub\\AdminScripts\\adsutil.vbs START_SERVER W3SVC/1")
print "website access with status %s" % status
print "send email to supporter."
except Exception, e:
sd=Sendmail()
sd.Sendemails(e)
print "try to restart iis web"
os.system("cscript.exe c:\\inetpub\\AdminScripts\\adsutil.vbs STOP_SERVER W3SVC/1")
os.system("cscript.exe c:\\inetpub\\AdminScripts\\adsutil.vbs START_SERVER W3SVC/1")
print "send email to supporter."
print "website access with below exceptions:"
print e
2894
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
