python連接配接SQL Server需要pymssql,安裝pymssql參考https://mp.csdn.net/postedit/81283149
#--*-- coding:utf-8 --*--
# 備份SQL Server事務日志,每小時一次
import os,time,logging
import pymssql
import threading
from datetime import datetime
logpath='D:\\scripts\\backup\\logs\\'
# 日志目錄是否存在
isExists = os.path.exists(logpath)
if not isExists:
os.makedirs(logpath)
# 日志資訊
logging.basicConfig(level=logging.INFO,
format='%(asctime)s: %(levelname)-4s %(message)s',
filename=logpath + 'backup_log_' + time.strftime('%Y%m%d_%H%M%S', time.localtime()) + '.log',
filemode='w',
datefmt='%Y%m%d %X')
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,"Not set db info")
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,"Connect db Failed!")
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:
logging.info(e)
self.conn.close()
def delbak(path):
p = list(os.listdir(path))
#logging.info("Delete >35 days backup file %s" % path)
for i in range(len(p)):
filedate = os.path.getmtime(path + p[i])
currdate = time.time()
num = (currdate - filedate) / (60 * 60 * 24)
if num > 90:
try:
os.remove(path + p[i])
#logging.info(u"Delete %s" % p[i])
except Exception as e:
logging.info(e)
class MyThread(threading.Thread):
def __init__(self,func,args,name=''):
threading.Thread.__init__(self)
self.name=name
self.func=func
self.args=args
def run(self):
apply(self.func,self.args)
def baklog(ip,unuse):
stip='\\\\10.110.0.1'
ms = MSSQL(host=ip,user="dumper",pwd="123456",db="master")
# 隻備份所有業務資料庫
dblist = ms.ExecQuery("SELECT name FROM Master..SysDatabases Where dbid > 6")
delpath = stip + '\\dbbackup\\' + ip + '\\'
# 逐個庫進行備份
for db in dblist:
# 備份的資料庫名
dbname = db[0]
logging.info('Backup ' + ip + ' ' + dbname)
ti = time.strftime('%Y%m%d_%H%M%S', time.localtime())
# 指定備份目錄
path = delpath + dbname + "\\"
# 全量/差異備份辨別
flag = 1
isExists = os.path.exists(path)
if not isExists:
os.makedirs(path)
flag = 0
# 備份檔案名
fname = path + dbname + "_" + str(ti)
if flag == 0:
fname = path + dbname + "_" + str(ti) + "_full.bak"
sql = "backup database [" + dbname + "] to disk='" + fname + "' with checksum,compression"
else:
path = path + "log\\"
isExists = os.path.exists(path)
if not isExists:
os.makedirs(path)
fname = path + dbname + "_" + str(ti) + "_log.bak"
sql = "backup log [" + dbname + "] to disk='" + fname + "' with checksum,compression"
# 執行備份
ms.ExecNonQuery(sql)
# 删除超過90天的備份檔案(事務日志)
delbak(path)
def main():
# 所有需要備份的資料庫IP
pre = '10.'
iplist = ('100.10.11','100.10.12','100.10.13','100.10.14','100.10.15','100.10.16','100.10.17','100.10.18','100.10.19',
'100.10.220','100.10.200','100.10.180','100.10.181','100.65.150','100.65.151',
'100.65.190','100.66.19','100.66.59',
'11.12.81','11.12.11',
)
threads = []
num = len(iplist)
for i in iplist:
ip = pre + i
t = MyThread(baklog,(ip,0),baklog.__name__)
threads.append(t)
for i in xrange(num):
threads[i].start()
for i in xrange(num):
threads[i].join()
logging.info(num)
if __name__ == '__main__':
main()