环境: 这个脚本的意义是,通过连接mssql ,拿出最新的备份状态的时间和时间大则表示备份成功。比如说上次全备份是9月10号备份成功了,他在数据库中的记录是9月10号。今天9月17日凌晨备份,如果没成功。他现在的记录还是上次9月10号的记录。则现在的值如果不大于上次的值,则表示状态为0 。
全备份 7天一次。差异备份1天一次,日志备份1小时1次。
#!/usr/bin/env python
import pymssql
import datetime
import json
import redis
import time
#sql = "DECLARE @path NVARCHAR(200) SELECT @path=path FROM sys.traces WHERE id=2 select @@SERVERNAME AS ServerName,SPID,NTUserName,Duration/1000000 AS Duration,Reads,Writes,CPU,StartTime,EndTime,TextData from ::fn_trace_gettable(@path,DEFAULT) where NTUserName <> 'NETWORK SERVICE' and StartTime>=DATEADD(SS,-60,GETDATE())"
sql = "SELECT database_name,type,MAX(backup_finish_date) AS backup_finish_date FROM msdb.dbo.backupset GROUP BY database_name,type ORDER BY database_name,type"
server_list = ['192.168.12.15','192.168.12.16','192.168.12.17','192.168.12.18','192.168.12.2']
now = int(time.time())
day_7 = int(time.time()) - 604800
hour_1 = int(time.time()) - 3600
day_1 = int(time.time()) - 86400
print day_7
for r in server_list:
conn=pymssql.connect(host=r,database='master',user='tech-test',password='test@2016')
cur=conn.cursor()
cur.execute(sql)
rows = cur.fetchall()
a = []
for i in rows:
a = int(time.mktime(i[2].timetuple()))
print a
if i[0] == 'Platform' and i[1] == 'D':
if a > day_7:
print a,day_7
f=open("/home/zabbix/sh/mssql/%s/d.txt" % r,"w")
f.write("1")
f.close()
else:
f=open("/home/zabbix/sh/mssql/%s/d.txt" % r,"w")
f.write("0")
f.close()
if i[0] == 'Platform' and i[1] == 'I':
if a > day_1:
print a,day_1
f=open("/home/zabbix/sh/mssql/%s/i.txt" % r,"w")
f.write("1")
f.close()
else:
f=open("/home/zabbix/sh/mssql/%s/i.txt" % r,"w")
f.write("0")
f.close()
if i[0] == 'Platform' and i[1] == 'L':
if a > hour_1:
print a,hour_1
f=open("/home/zabbix/sh/mssql/%s/L.txt" % r,"w")
f.write("1")
f.close()
else:
f=open("/home/zabbix/sh/mssql/%s/L.txt" % r,"w")
f.write("0")
f.close()