大智(若愚)

由於花不了大錢買設備做備份所以只好上網找資料自己選寫服務來固定一段時間備份資料庫

程式的Timer為System.Timers.Timer必須已選擇項目加入次Timer元件

可參考:http://www.dotblogs.com.tw/mis2000lab/archive/2009/03/06/timer_windows_service_20090306.aspx

相關程式碼

MainService.cs

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.ServiceProcess;
using System.Text;
using System.IO;

namespace WindowsService1
{
    public partial class MainService : ServiceBase
    {
        public MainService()
        {
            InitializeComponent();
        }
        private string mainerrfile = Globals.CreateLog(Globals.BackUpPath) + Globals.DatabaseName + "mainerr.log";//主程式錯誤記錄檔
        protected override void OnStart(string[] args)
        {
            try
            {
                int timeForMinune = Globals.GetTimeForMinune;//讀取app.config檔內所設定的時間
                this.Timer.Interval = timeForMinune * 60 * 1000;
            }
            catch
            {

                this.Timer.Interval = 1 * 60 * 1000; //預設為1分鐘
            }
            this.Timer.Start();
        }

        protected override void OnStop()
        {
            this.Timer.Stop();
        }

        private void Timer_Elapsed(object sender, System.Timers.ElapsedEventArgs e)
        {
            try
            {
                BackupDB backup = new BackupDB();
                backup.StartBackUpDb();
            }
            catch (Exception ex)
            {
                SaveFileResult(mainerrfile, ex.ToString());
            }
        }

        #region 儲存記錄
        //儲存記錄
        private void SaveFileResult(string savedName, string strContent)
        {
            FileInfo fi = new FileInfo(savedName);
            System.Text.StringBuilder builder = new System.Text.StringBuilder();
            builder.Append(strContent);
            System.IO.StreamWriter writer = fi.AppendText();
            //new System.IO.StreamWriter (str);//File.OpenWrite(str);
            writer.Write(builder.ToString());
            writer.Close();
            return;
        }
        #endregion
    }
}

主要呼叫類別

BackupDB.cs

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.IO;

namespace WindowsService1
{
    class BackupDB
    {
        private string Server = Globals.SqlServer;//取得Sql Sever 連現位置
        private string User = Globals.SqlID;//取得Sql帳號
        private string Pwd = Globals.SqlPW;//取得Sql密碼
        private string DatabaseName = Globals.DatabaseName;//取得要備份資料庫名稱
        private string BackUpPath = Globals.BackUpPath;//儲存的路徑
        private string SaveDBfilename = Globals.ServerFileName;
        private string filename = Globals.CreateLog(Globals.BackUpPath) + Globals.DatabaseName + ".log";
        private string errfile = Globals.CreateLog(Globals.BackUpPath) + Globals.DatabaseName + "err.log";
        public void StartBackUpDb()
        {
            string consqlserver = "Data Source=" + Server + ";Initial Catalog=master;Persist Security Info=True;User ID=" + User + ";pwd=" + Pwd;
            //定義SQL Server連接對像
            SqlConnection con = new SqlConnection(consqlserver);
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand();
                //定義備份數據庫的SQL語句
                cmd.CommandText = @"BACKUP DATABASE " + DatabaseName + " TO disk='" + BackUpPath + SaveDBfilename + "' WITH INIT";//INIT備份選項為覆蓋原本檔案(避免檔案越來越大)
                //指出通過前面創建的數據庫連接執行
                cmd.Connection = con;
                //執行SQL語句
                cmd.ExecuteNonQuery();
                //寫入記錄
                SaveFileResult(filename, "The database has been backed up on " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "(sucessfully)" + "\r\n");
            }
            catch (Exception ex)
            {
                SaveFileResult(errfile,ex.ToString());
                SaveFileResult(filename, "The database has been backed up on " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "(failure)"+ "\r\n");
            }
            finally
            {
                con.Close();
                con.Dispose();
            }
        }

        #region 儲存記錄
        //儲存記錄
        private void SaveFileResult(string savedName, string strContent)
        {
            FileInfo fi = new FileInfo(savedName);
            System.Text.StringBuilder builder = new System.Text.StringBuilder();
            builder.Append(strContent);
            System.IO.StreamWriter writer = fi.AppendText();
            //new System.IO.StreamWriter (str);//File.OpenWrite(str);
            writer.Write(builder.ToString());
            writer.Close();
            return;
        }
        #endregion
    }
}

原始碼：BackUpSqlServer for service.rar

參考網址：Jason的電腦健身房-透過批次檔及排程自動備援MS-SQL

我只是個小小的入門者