数据库表

CREATE TABLE [dbo].[Sys_Backup](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](500) NULL,
	[Version] [nvarchar](500) NULL,
	[BackupFileName] [nvarchar](500) NULL,
	[BackupTime] [datetime] NULL,
	[LastRestoreTime] [datetime] NULL,
	[Description] [nvarchar](2000) NULL,
 CONSTRAINT [PK_Sys_Backup] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

程序

/// <summary>
/// 创建数据备份请求对象
/// </summary>
public class DataBackupRequestDto
{
    /// <summary>
    /// 数据备份名称
    /// </summary>
    [Required]
    [MaxLength(30, ErrorMessage = "最大长度30个字符")]
    public string Name { get; set; }

    /// <summary>
    /// 数据备份描述
    /// </summary>
    [Required]
    [MaxLength(100, ErrorMessage = "最大长度100个字符")]
    public string Description { get; set; }
}

 /// <summary>
 /// 备份数据库
 /// </summary>
 /// <returns></returns>
 public ApiResult AddBackup(DataBackupRequestDto request, int userId, string serverpath)
 {

     using var connection = SqlHelper.GetDBConnection();
   
     
         string filename = Guid.NewGuid().ToString();

         if (!System.IO.Directory.Exists(serverpath + "\\dbback\\"))
         {
             System.IO.Directory.CreateDirectory(serverpath + "\\dbback\\");
         }
         string pathName = serverpath.TrimEnd('\\') + "\\dbback\\" + filename + ".bak";
        

         var tablelist = connection.Query<String>("select name from sysobjects where xtype='U' order by name").AsList();

         StringBuilder sql = new StringBuilder();

         for (int i = 0; i < tablelist.Count; i++)
         {
             if (tablelist[i].ToLower() == "sys_backup" || tablelist[i].ToLower() == "upgrade"|| tablelist[i].ToLower() == "sysdiagrams")
             {
                 continue;
             }
             var identitynum = connection.QuerySingle<int>("select COUNT(1) from syscolumns where id=Object_Id('" + tablelist[i] + "') and COLUMNPROPERTY( id,name,'IsIdentity')=1");
             var colname = connection.Query<String>("select name from syscolumns where id=Object_Id('" + tablelist[i] + "')").AsList();

             List<string> colname2 = new List<string>();
             for (int k = 0; k < colname.Count; k++)
             {
                 colname2.Add("[" + colname[k] + "]");
             }
             string field = string.Join(",", colname2.ToArray());

             string dataquery = "select " + field + " from [" + tablelist[i]+"]";

             DataTable dt = new DataTable();
             dt.Load(connection.ExecuteReader(dataquery));

             if (dt.Rows.Count > 0)
             {

                 sql.AppendLine("TRUNCATE table [" + tablelist[i] + "];");
                 if (identitynum > 0)
                 {
                     sql.AppendLine("SET IDENTITY_INSERT [dbo].[" + tablelist[i] + "] ON;");
                 }

             }

             for (int j = 0; j < dt.Rows.Count; j++)
             {
                 StringBuilder sqldata = new StringBuilder();
                 for (int k = 0; k < colname.Count; k++)
                 {
                     string celldata = dt.Rows[j][colname[k]].ToString();
                     if (celldata == "")
                     {
                         sqldata.Append("NULL");
                     }
                     else
                     {
                         sqldata.Append("'" + dt.Rows[j][colname[k]].ToString().Replace("'", "''").Replace("\r", "").Replace("\n", "") + "'");
                     }

                     if (k != colname.Count - 1)
                     {
                         sqldata.Append(",");
                     }
                 }
                 sql.AppendLine("INSERT [dbo].[" + tablelist[i] + "] (" + field + ") VALUES (" + sqldata + ");");
             }
             if (dt.Rows.Count > 0)
             {
                 if (identitynum > 0)
                 {
                     sql.AppendLine("SET IDENTITY_INSERT [dbo].[" + tablelist[i] + "] OFF;");
                 }
             }
         }

         StreamWriter sw = File.CreateText(pathName);

         sw.Write(sql.ToString());  //写入文件中
         sw.Flush();//清理缓冲区
         sw.Close();//关闭文件


         int? id = connection.Insert<Sys_Backup>(new Sys_Backup { BackupFileName = "\\dbback\\" + filename + ".bak", Version="v1.0", Description = request.Description, Name = request.Name, BackupTime = DateTime.Now });

         if (id.Value > 0)
         {
            
             return  new ApiResult() { Success=true };
         }
    


     return new ApiResult() { Success = false };

 }
 /// <summary>
 /// 还原数据库
 /// </summary>
 /// <param name="backupid"></param>
 /// <returns></returns>
 public ApiResult RestoreBackup( int backupid,int userid, string serverpath)//int backupId, int userId,string serverpath
 {
     using (var conn = SqlHelper.GetDBConnection())
     {
         var model = conn.Get<Sys_Backup>(backupid);
         if (model == null)
         {
             return new ApiResult() { Success = false };
         }

         string dbpath = model.BackupFileName;


         StreamReader reader = new StreamReader(serverpath + dbpath, Encoding.UTF8);
         string sql = reader.ReadToEnd();
         reader.Close();
         int result = -1;

         var tran = conn.BeginTransaction();
         try
         {
             result = conn.Execute(sql, new { }, tran);
             tran.Commit();
         }
         catch (Exception e)
         {
             tran.Rollback();
         }

         if (result > 0)
         {
             conn.Execute("update Sys_Backup set LastRestoreTime=GETDATE() where id=" + backupid);
             return new ApiResult() { Success = true };
         }
         return new ApiResult() { Success=false};

     }

 }