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