备份sqlserver 数据库到远程主机脚本
mssqlBackup.py:
# Name: mssqlBackup.py
# Purpose:
#
# Author: yanglixue
#
# Created: 2015/04/01
#-------------------------------------------------------------------------------
import pymssql
import datetime
import logging
import traceback
import os
log_format = '%(filename)s [%(asctime)s] [%(levelname)s] %(message)s'
log_file="c:\\" + "backup_" +datetime.datetime.now().strftime("%Y-%m-%d") + ".log"
logging.basicConfig(filename=log_file,format=log_format,level=logging.INFO)
backupdir="E:\\backup\\"
delcmd="D:\EclipseWork\mssqlBackup\del.bat >> c:\\" + "del_" +datetime.datetime.now().strftime("%Y-%m-%d") + ".log"
dbs=['db2','test']
try:
logging.info("connect database ......")
conn=pymssql.connect(host='10.1.1.2',database='master',user='sa',password='sa',autocommit=True)
cur=conn.cursor()
for dbname in dbs:
now = datetime.datetime.now()
sdate = now.strftime("%Y-%m-%d_%H-%M-%S")
bakname=backupdir + dbname + "_" + sdate + ".bak"
sql = "".join(["backup database [",
dbname,
"] to disk=N'"+ bakname,
"' with noinit,noformat,checksum,stop_on_error"
]
)
cur.execute(sql)
logging.info(sql)
logging.info("db:" + dbname + " OK. ")
conn.close()
logging.info(delcmd)
out = os.system(delcmd)
logging.info(delcmd + " " + str(out))
except Exception as ex:
logging.error("Exception:" + traceback.format_exc())
del.bat:
echo "start ..."
@echo on
xcopy E:\backup\* \\192.168.1.11\E$\dbbackup
del E:\backup\* /Q
转载于:https://blog.51cto.com/raugher/1628075