首页技术正文

Python查询SQLserver数据库备份（抛砖引玉）

程序员娇娇技术 2022年11月7日

0 收藏 734 点赞 803 浏览 1699 个字

通过python pymssql直接访问SQLserver数据库，查找其数据库mode，这个脚本具有很强的抛砖引玉特性：

1.可以巡检多台多数据库服务器

2.query内容可以多样化，譬如查询死锁、连接数等

当然还有更多需要优化的地方：

1.输出内容未优化

2.未捕获异常

3.未多线程化

#!/usr/bin/python3
#coding=utf-8import pymssqlodbj=[("10.1.10.67","sa","r2hat","master"),("10.1.10.9","s3y3zh","r232hat","master")]#相关信息有做处理
for (h,u,p,d) in odbj:
    conn = pymssql.connect(host=h,user=u,password=p, database=d)
    cur = conn.cursor()
query="select name, database_id, recovery_model_desc from sys.databases"
query1="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"
cur.execute(query1)
    conn.commit
rows = cur.fetchall()
for (s,s1,s2) in rows:
   print ("IP:%s,DBname:%s,DBmode:%s,DBbackuptime:%s"%(h,s,s1,s2))
     cur.close()
     conn.close()

　　输出结果：IP 有去掉

<built-in method commit of pymssql.Connection object at 0x7f35e7c17388>
DBname:MC,DBmode:D,DBbackuptime:2017-03-09 21:00:44
DBname:test1,DBmode:D,DBbackuptime:2017-04-15 17:05:54
DBname:test1,DBmode:L,DBbackuptime:2017-04-15 17:06:42
DBname:tnfc270,DBmode:D,DBbackuptime:2017-04-22 13:32:37
DBname:ufmobile,DBmode:D,DBbackuptime:2016-10-16 20:00:19
<built-in method commit of pymssql.Connection object at 0x7f35e7c17788>
DBname:CB_ZJ,DBmode:D,DBbackuptime:2013-03-26 18:17:14
DBname:ImageDB_ZJ,DBmode:D,DBbackuptime:2016-12-09 23:02:48
DBname:Npmzj20130603,DBmode:D,DBbackuptime:2013-06-04 17:23:33
DBname:NPMZJ20130705,DBmode:D,DBbackuptime:2013-07-06 14:39:54
DBname:PMHS,DBmode:D,DBbackuptime:2016-12-11 18:26:54
DBname:PMTG,DBmode:D,DBbackuptime:2016-12-09 23:03:32
DBname:pmtg_new,DBmode:D,DBbackuptime:2013-08-09 17:51:04
DBname:PMZJ,DBmode:D,DBbackuptime:2016-12-09 23:17:59
DBname:PMZJ,DBmode:L,DBbackuptime:2014-08-18 10:24:13
DBname:UserDB,DBmode:D,DBbackuptime:2017-03-20 22:38:03