SQL备份数据库代码
#region 服务每天备份一次数据库 /// <summary> /// 服务每天备份一次数据库 /// </summary> public void ServiceForBackupDatabaseEveryDay() { Thread thread = new Thread(new ThreadStart(BackupDatabaseEveryDay)); thread.IsBackground = true; thread.Start(); } private void BackupDatabaseEveryDay() { while (true) { try { //每天凌晨1点进行全备份 if (DateTime.Now.Hour == 1) { string filename = string.Format("Grandmap.Cms_Full_{0}.bak", DateTime.Now.ToString("yyyyMMddHHmmss")); Hashtable param = new Hashtable(); param.Add("DatabaseName", "Grandmap.Cms"); param.Add("FileName", filename); //<![CDATA[ BACKUP DATABASE $DatabaseName$ TO DISK = '$FileName$';]]> } Thread.Sleep(TimeSpan.FromHours(1)); } catch (Exception ex) { Log4Net.Error("每天凌晨1点进行全备份数据库服务异常!", ex); } } } #endregion #region 每天12点和18点进行一次差异备份 /// <summary> /// 每天12点和18点进行一次差异备份 /// </summary> public void ServiceForBackupDatabaseWithDiffrent() { Thread thread = new Thread(new ThreadStart(BackupDatabaseWithDiffrent)); thread.IsBackground = true; thread.Start(); } private void BackupDatabaseWithDiffrent() { while (true) { try { //每天12点和18进行差异备份 if (DateTime.Now.Hour == 12 || DateTime.Now.Hour == 18) { string filename = string.Format("Grandmap.Cms_Diffrent_{0}.bak", DateTime.Now.ToString("yyyyMMddHHmmss")); Hashtable param = new Hashtable(); param.Add("DatabaseName", "Grandmap.Cms"); param.Add("FileName", filename); //<![CDATA[ BACKUP DATABASE $DatabaseName$ TO DISK = '$FileName$' WITH DIFFERENTIAL;]]> } Thread.Sleep(TimeSpan.FromHours(1)); } catch (Exception ex) { Log4Net.Error("每天12点和18进行差异备份数据库服务异常!", ex); } } } #endregion #region 文件夹创建 /// <summary> /// 文件夹创建 /// </summary> public void CreateDir() { string filedir = DateTime.Now.ToString("yyyy-MM"); string xmlPath = AppDomain.CurrentDomain.BaseDirectory; xmlPath = System.IO.Path.GetDirectoryName(xmlPath); if (!xmlPath.EndsWith("\\")) { xmlPath += "\\"; } xmlPath = string.Format("{0}DatabaseDir\\{1}", xmlPath, filedir); if (!Directory.Exists(xmlPath)) { Directory.CreateDirectory(xmlPath); } } #endregion
附:SQL存储过程
-- ============================================= -- Author: 管理员 -- Create date: 2016-03-12 -- Description: 备份数据库 -- ============================================= ALTER PROCEDURE [dbo].[up_BackupDatabase] -- Add the parameters for the stored procedure here @Backtype INT, @Databasename VARCHAR(50), @Filename VARCHAR(500) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- 全备份(压缩备份) IF(@Backtype = 1) BEGIN BACKUP DATABASE @Databasename TO DISK = @Filename WITH COMPRESSION; END --差异备份 IF(@Backtype = 2) BEGIN BACKUP DATABASE @Databasename TO DISK = @Filename WITH DIFFERENTIAL; END END