sqlserver数据库备份Python脚本
import pymssql
import sys
import os
import time
import datetime
import TuiSong
dir_list =list()
dir_path_root=r"E:\每周数据库备份"
__conn_path = {
'host':"",
'user':"sa",
'password':"xgkj@20181011",
'database':'master'
}
content_str=""
title_str="标题"
def get_database_name_all():
"""获取所有数据库名称"""
connect=None
cursor=None
global content_str
try:
connect = pymssql.connect(**__conn_path)
cursor = connect.cursor()
cursor.execute("SELECT Name FROM Master..SysDatabases ORDER BY Name")
name_list=cursor.fetchall()
except :
print("——————>error:数据库操作异常!")
content_str+=("\n time:"+time.strftime('%Y-%m-%d-%H-%M-%S')
+"——————>error:查询全部数据库名异常!\n SELECT Name FROM Master..SysDatabases ORDER BY Name")
finally:
if connect:
connect.close()
if cursor:
cursor.close()
return name_list
def join_sql(database_name):
"""拼接备份sql
return:sql
"""
global content_str
sql=""
nowtime = time.strftime('%Y-%m-%d-%H-%M-%S',time.localtime(time.time())) + ""
try:
if os.path.exists(dir_path_root+"\\"+database_name):
if dir_path_root+"\\"+database_name not in dir_list:
dir_list.append(dir_path_root+"\\"+database_name)
else:
content_str+=("\n time:"+time.strftime('%Y-%m-%d-%H-%M-%S')+"——————>数据库备份文件夹不存在....开始自动创建")
os.mkdir(dir_path_root+"\\"+database_name)
dir_list.append(dir_path_root+"\\"+database_name)
content_str+=("\n time:"+time.strftime('%Y-%m-%d-%H-%M-%S')+"——————>创建成功,开始备份数据库: %s"%database_name)
sql=r"""BACKUP DATABASE ["""+database_name+"""]
TO DISK = N'"""+dir_path_root+"""\\"""+database_name+"""\\"""+database_name+"""_""" + nowtime + """.bak'
WITH NOFORMAT,
NOINIT,
NAME = N'"""+database_name+"""-完整 数据库 备份',
SKIP,
NOREWIND,
NOUNLOAD,
STATS = 10 """
except Exception as ex:
content_str+=("\n time:"+time.strftime('%Y-%m-%d-%H-%M-%S')+"——————>error:"+ex)
print(ex)
return sql
def backups_database(sql,name_data):
connect=None
cursor=None
global content_str
try:
connect = pymssql.connect(**__conn_path)
connect.autocommit(True)
cursor = connect.cursor()
cursor.execute(sql)
content_str+=("\n time:"+time.strftime('%Y-%m-%d-%H-%M-%S')+"——————>备份成功:"+name_data)
connect.autocommit(False)
except Exception as ex:
print("--------------->error:",ex)
finally:
if connect:
connect.close()
if cursor:
cursor.close()
def del_time_file(folder_path):
"""删除过期文件"""
global content_str
try:
files=os.listdir(folder_path)
now_time_file=time.strftime('%Y%m%d')
for x in files:
file_time=time.strftime('%Y%m%d',time.localtime(os.path.getatime(folder_path+"\\"+x)))
if int(now_time_file)-int(file_time)>=30:
os.remove(folder_path+"\\"+x)
content_str+=("\n time:"+time.strftime('%Y-%m-%d-%H-%M-%S')+"——————>已删除30天过期文件:"+x)
except Exception as ex:
print("------->检查30天过期文件操作失败",ex)
content_str+=("\n time:"+time.strftime('%Y-%m-%d-%H-%M-%S')+"——————>error:检查30天过期文件操作失败"+ex)
def main():
global content_str
backups_time= datetime.datetime.now().strftime("%Y-%m-%d-%H-%M-%S")
while True:
now_time=datetime.datetime.now().strftime("%Y-%m-%d-%H-%M-%S")
if backups_time<=now_time:
print("\n------------->开始数据库备份操作")
backups_time=(datetime.datetime.now()+datetime.timedelta(days=1)).strftime("%Y-%m-%d-%H-%M-%S")
database_names=get_database_name_all()
for name in database_names:
if name[0] == "tempdb":
continue
sql= join_sql(name[0])
if sql!="":
backups_database(sql,name[0])
else:
print("------------->error:sql拼接出现异常")
content_str+=("\n time:"+time.strftime('%Y-%m-%d-%H-%M-%S')+"——————>error:sql拼接出现异常")
else:
print("--------------->数据库备份成功\n",database_names,
"\n下次备份时间:",backups_time)
content_str+=("\n time:"+time.strftime('%Y-%m-%d-%H-%M-%S')+"——————>数据库备份成功\n"+"下次备份时间:"+backups_time)
TuiSong.tuisong_main(title_str,content_str)
print(content_str)
content_str=""
for file in dir_list:
del_time_file(file)
print("--------------->时间:",datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"),"\r",end="")
time.sleep(1)
if __name__ == "__main__":
sys.exit(int(main() or 0))
邮箱推送
import smtplib
from email.mime.text import MIMEText
'''
email模块,
email模块用来构造邮件和解析邮件内容,构造一
个邮件就是创建一个Message对象
MIMEText对象就表示构造一个纯文本的邮件,
MIMEImage对象表示构造了一个作为附件的图片。
如果把多个对象组合起来,用到
MIMEMultipart对象email模块下有多个类包括
Message、MIMEBase、MIMEText、MIMEAudio、MIMEImage和MIMEMultipart
'''
SMTP_SERVER = "smtp.163.com"
SMTP_PORT = 25
def send_mail(user,pwd,to,subject,text):
"""邮箱发送"""
msg = MIMEText(text)
msg['From'] = user
msg['To'] = to
msg['subject'] = subject
smtp_server = smtplib.SMTP(SMTP_SERVER,SMTP_PORT)
print('连接到邮件服务器。')
try:
smtp_server.ehlo()
smtp_server.starttls()
smtp_server.ehlo()
print('Logging Info Mail Server')
smtp_server.login(user,pwd)
smtp_server.sendmail(user,to,msg.as_string())
except Exception as err:
print('Sending Mail faild:{0}'.format(err))
finally:
smtp_server.quit()
def main():
send_mail(发件人邮箱,密码,收件人邮箱,邮件主题,邮件内容)
if __name__ == '__main__':
main()