SQL備份資料庫程式碼

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