windows服务自动备份数据库

 

最近写了几个windows服务用于自动备份与删除数据：

 

services代码如下：

 

  public partial class Service1 : ServiceBase
    {
        public Service1()
        {
            InitializeComponent();
        }
        System.Timers.Timer timer1;
        static SqlConnection conn = new SqlConnection();//数据库连接
       
        protected override void OnStart(string[] args)
       { 
                timer1 = new System.Timers.Timer();
                timer1.Interval = (0.5*60)* 60 * 1000;  //设置计时器事件间隔执行时间
                timer1.Elapsed += new System.Timers.ElapsedEventHandler(timer1_Elapsed);
                timer1.Enabled = true;
          
        }

        protected override void OnStop()
        {
            this.timer1.Enabled = false;
        }

        private void timer1_Elapsed(object sender, System.Timers.ElapsedEventArgs e)
        {
            //执行SQL语句或其他操作 

            if (DateTime.Now.Hour == 1 || DateTime.Now.Hour == 07 || DateTime.Now.Hour == 11 || DateTime.Now.Hour == 15 || DateTime.Now.Hour == 19 || DateTime.Now.Hour == 20 ||
                DateTime.Now.Hour == 21 || DateTime.Now.Hour == 22)//以上时间才可以备份。可以添加2个timer用于整点保存数据
            {
                NLog.Logger log = NLog.LogManager.GetCurrentClassLogger();
                try
                { 

                    string logicalname = "BackupTradeDb_" + DateTime.Now.ToString("yyyyMMddhhmmss");
                    string physicalname = AppDomain.CurrentDomain.BaseDirectory;

                    string path = @"F:\WindowsServes\backupDB\" + DateTime.Now.ToString("yyyyMMdd");//保存备份文件的路径
                    
                    if (!Directory.Exists(path))
                    {
                        Directory.CreateDirectory(path);
                    }
                    log.Info("路径为："+path);

                    physicalname = Path.Combine(path, logicalname + ".bak"); 

                    log.Info("开始备份" );
                    StartBackup(logicalname, physicalname, "NewTradeDB");
                }
                catch (Exception ex)
                {
                    System.Diagnostics.Debug.Print(ex.ToString());

                    log.Info(ex.ToString());

                }


                //DateTime date = DateTime.Now.AddDays(-5);


                //foreach (DirectoryInfo item in directory)
                //{

                //    if (item.Name.Contains(date.ToString("yyyyMMdd")))
                //    {
                //        FileInfo[] files = item.GetFiles();
                //        foreach (FileInfo file in files)
                //        {
                //            file.Delete();
                //        }
                //        item.Delete();
                //    }
                //} 这个为删除5天前的备份数据
            }
            
             
        } 

        private void StartBackup(string logicalname, string physicalname,string dbName)
        {
            SqlParameter[] paras = new SqlParameter[]{
                    new SqlParameter("@devtype",SqlDbType.VarChar,20),
                    new SqlParameter("@logicalname",SqlDbType.VarChar,256),
                    new SqlParameter("@physicalname",SqlDbType.VarChar,520)
                };
            paras[0].Value = "disk";
            paras[1].Value = logicalname;
            paras[2].Value = physicalname;

            NLog.Logger log = NLog.LogManager.GetCurrentClassLogger();

            int device = ExecuteOtherProc("sp_addumpdevice", paras);//只是添加一个备份设备

            log.Info("添加一个备份设备"+device);
            string sql_backup =string.Format("backup database {0} to {1} WITH  DIFFERENTIAL",dbName,logicalname);
         　　//差异备份
           

            log.Info(sql_backup);

            int backup = ExecuteSql(sql_backup);

            log.Info("执行备份语句，影响行数为"+backup);

        }
        public static int ExecuteOtherProc(string procName, params SqlParameter[] paras)
        {

            NLog.Logger log = NLog.LogManager.GetCurrentClassLogger();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = procName;
            cmd.Connection = conn;

            if (paras != null)
            {
                cmd.Parameters.AddRange(paras);
            }
            int flag = 0;
            try
            {
                conn.Open();
                flag = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {

              log.Info(ex.ToString());
            }
            finally
            {
                conn.Close();
            }
            return flag;
        }
        public static int ExecuteSql(string sql)
        {
            NLog.Logger log = NLog.LogManager.GetCurrentClassLogger();

            int flag = 0;
            SqlCommand cmd = new SqlCommand(sql, conn);

            try
            {
                conn.Open();
                flag = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                log.Info(ex.ToString());
            }
            finally
            {
                conn.Close();
            }
            return flag;
        }

    }