创建Windows服务实现MSSql数据库备份

创建Windows服务实现MSSql数据库备份
2010年12月09日
　　对于安装的SqlExpress的服务器或服务不够，无法创建Sql作业的服务器上使用
　　由于代码比较简单，就简化点说：
　　1、在VS下新建一个Windows服务项目。里面自动添加了一个名称为Service1的windows 服务。（直接创建Windows运用程序项目的可以在项目上右键添加Windows服务）
　　2、添加App.Config配置文件（根据自己需要）
　　strCon：Sql连接字符串 logPath：日志文件路径 dbPath：备份文件存放路径 3、添加System.Configuration引用
　　4、在Service1中添加代码：
　　备注：备份系统需要对数据库用户添加权限：grant backup database to 用户名 using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Diagnostics; using System.ServiceProcess; using System.Text; using System.IO; using System.Timers; using System.Configuration; namespace AdBackUpService { public partial class Service1 : ServiceBase { public Service1() { InitializeComponent(); } Timer timer = null; private int cTime = 604800000;//正常间隔时间7天(每周日23点59分开始备份) private string logPath = ConfigurationManager.AppSettings["logPath"]; private string dbPath = ConfigurationManager.AppSettings["dbPath"]; /// /// 服务启动 /// /// protected override void OnStart(string[] args) { // TODO: 在此处添加代码以启动服务。 Start();//启动创建日志 //BackUp();//启动时即时备份一次 if(timer == null) timer = new Timer(); timer.Interval = GetBackMillisecond(); timer.Enabled = true; timer.Elapsed += new ElapsedEventHandler(timer_Elapsed); } protected override void OnStop() { // TODO: 在此处添加代码以执行停止服务所需的关闭操作。 timer.Enabled = false; timer.Dispose(); timer = null; Stop(); } /// /// 获取执行备份剩余时间（毫秒） /// /// protected double GetBackMillisecond() { double millisecond = 3;//默认监控间隔3秒，给系统一个缓冲时间 DateTime dt = DateTime.Now; DateTime temp1 = dt; DateTime temp2; if(dt.DayOfWeek.ToString() == "Tuesday") { temp1 = temp1.AddDays(6); } else if(dt.DayOfWeek.ToString() == "Wednesday") { temp1 = temp1.AddDays(5); } else if(dt.DayOfWeek.ToString() == "Thursday") { temp1 = temp1.AddDays(4); } else if(dt.DayOfWeek.ToString() == "Friday") { temp1 = temp1.AddDays(3); } else if(dt.DayOfWeek.ToString() == "Saturday") { temp1 = temp1.AddDays(2); } else if(dt.DayOfWeek.ToString() == "Monday") { temp1 = temp1.AddDays(1); } temp2 = new DateTime(temp1.Year, temp1.Month, temp1.Day, 23, 59, 0, 0); TimeSpan ts = temp2.Subtract(dt); if(ts.TotalMilliseconds > 0) millisecond = ts.TotalMilliseconds; return millisecond; } void timer_Elapsed(object sender, ElapsedEventArgs e) { BackUp(); if(timer.Interval != cTime) timer.Interval = cTime; } protected void BackUp() { string backName = "V3" + GetDateStr(DateTime.Now); string sql = "backup database ADOnlineV3 to disk='" + dbPath + backName + ".bak'"; try { DBHelper.ExecuteCommand(sql, CommandType.Text); WriteRecord("Success：" + backName + ".bak Time：" + DateTime.Now.ToString()); } catch(Exception ex) { WriteRecord("Error：" + ex.Message + " Time：" + DateTime.Now.ToString()); } } #region 服务日志 /// /// 服务启动创建日志并添加记录 /// public void Start() { FileStream fs = new FileStream(logPath, FileMode.OpenOrCreate, FileAccess.Write); StreamWriter m_streamWriter = new StreamWriter(fs); m_streamWriter.BaseStream.Seek(0, SeekOrigin.End); m_streamWriter.WriteLine(DateTime.Now.ToString() + "------------------------------------------------- -------------------------------------------\n"); m_streamWriter.WriteLine("AdBackUpService: Service Started! Time:" + DateTime.Now.ToString() + "\n"); m_streamWriter.Flush(); m_streamWriter.Close(); fs.Close(); } /// /// 备份操作日志记录 /// /// public void WriteRecord(string record) { FileStream fs = new FileStream(logPath, FileMode.OpenOrCreate, FileAccess.Write); StreamWriter m_streamWriter = new StreamWriter(fs); m_streamWriter.BaseStream.Seek(0, SeekOrigin.End); m_streamWriter.WriteLine(record + "\n"); m_streamWriter.Flush(); m_streamWriter.Close(); fs.Close(); } /// /// 服务停止日志 /// public void Stop() { FileStream fs = new FileStream(logPath, FileMode.OpenOrCreate, FileAccess.Write); StreamWriter m_streamWriter = new StreamWriter(fs); m_streamWriter.BaseStream.Seek(0, SeekOrigin.End); m_streamWriter.WriteLine("AdBackUpService: Service Stopped! Time: " + DateTime.Now.ToString() + "\n"); m_streamWriter.Flush(); m_streamWriter.Close(); fs.Close(); } #endregion /// /// 转换时间字符串，精确到毫秒 /// /// /// public string GetDateStr(DateTime dt) { StringBuilder strDate = new StringBuilder(); strDate.Append(dt.Year); strDate.Append(dt.Month /// 获取影响多少行 /// /// T-SQL语句 /// 命令类型 /// T-SQL参数 /// 影响多少行 public static int ExecuteCommand(string queryString, CommandType commandType, params SqlParameter[] param) { int flag = 0; using(SqlConnection con = new SqlConnection(ConnectionString)) { SqlCommand command = new SqlCommand(queryString, con); command.CommandType = commandType; if(param != null) { for(int i = 0; i /// 获取DataSet对象 /// /// T-SQL语句 /// 命令类型 /// T-SQL参数 /// DataSet对象 public static DataSet GetDataSet(string queryString, CommandType commandType, params SqlParameter[] param) { DataSet ds = new DataSet(); using(SqlConnection con = new SqlConnection(ConnectionString)) { SqlDataAdapter adapter = new SqlDataAdapter(queryString, con); adapter.SelectCommand.CommandType = commandType; if(param != null) { adapter.SelectCommand.Parameters.Clear(); for(int i = 0; i /// 获取DataReader对象 /// /// T-SQL语句 /// 命令类型 /// T-SQL参数 /// DataReader对象 public static SqlDataReader GetDataReader(string queryString, CommandType commandType, params SqlParameter[] param) { SqlDataReader dataReader = null; SqlConnection con = new SqlConnection(ConnectionString); SqlCommand command = new SqlCommand(queryString, con); command.CommandType = commandType; if(param != null) { for(int i = 0; i /// 查询单个数据 /// /// T-SQL语句 /// 命令类型 /// T-SQL参数 /// Object对象 public static Object GetScalar(string queryString, CommandType commandType, params SqlParameter[] param) { Object obj = null; using(SqlConnection con = new SqlConnection(ConnectionString)) { SqlCommand command = new SqlCommand(queryString, con); command.CommandType = commandType; if(param != null) { for(int i = 0; i SDK v2.0下的SDK命令提示 工具
　　安装卸载命令： installutil 服务物理路径.exe
　　installutil /u 服务物理路径.exe
　　好了。就是这么简单。需要的话，不妨试试！
　　注：有可能有的服务器没有上述两个命令工具，那一般的命令行工具能不能行呢，这个问题留着另外一篇说明。:)