【SqlServer】数据库备份和恢复操作

借鉴自：https://www.cnblogs.com/fishtreeyu/archive/2010/12/27/1918076.html

博主说：

-- 备份数据库
backup database db_CSManage to disk='c:\backup.bak'
-- 还原数据库，必须先备份该数据库的日志文件到原先的备份文件中
backup log db_CSManage to disk='c:\backup.bak'
restore database db_CSManage from disk='c:\backup.bak'

但在还原数据库时报错误

似乎他少写了一句话，改成这样就好了

-- 备份数据库
backup database OA to disk='d:\backup.bak'

-- 备份该数据库的日志文件到原先的备份文件中
backup LOG OA to disk='d:\backup.bak' WITH NORECOVERY
--还原数据库，
restore database OA from disk='d:\backup.bak' WITH STATS = 10,RECOVERY;

结果：

好了，知道这个后直接在项目中执行该sql语句即可。

但是有个坑点，在项目开发中，数据库备份的在安装数据库的那台电脑上，并不在vs项目文件夹中，需下载回本地

MVC控制器备份数据库：

/// <summary>
/// 备份数据文件
/// </summary>
/// <param name="dbName"></param>
/// <param name="dbBackupName"></param>
/// <returns></returns>
public ActionResult Backuping(string dbName, string dbBackupName)
{
    if (dbName == null || dbName.Length == 0)
        return Json(new { msg = false, error = "输入的数据库不存在！" }, JsonRequestBehavior.AllowGet);

    string path = ConfigurationManager.AppSettings["DbBackPath"] + dbName + "/" + DateTime.Now.ToString("yyyyMMdd");
    //path = Server.MapPath(path);路径只能是服务器上的

    //如果路径不存在就创建文件夹
    if (!Directory.Exists(path))
    {
        Directory.CreateDirectory(path);
    }

    //判断文件是否已存在
    string filePath = path + "/" + dbBackupName.Trim() + ".bak'";
    if (System.IO.File.Exists(filePath))
    {
        System.IO.File.Delete(filePath);
    }
    string str = ConfigurationManager.ConnectionStrings[dbName].ToString();
    using (SqlConnection con = new SqlConnection(str))
    {

        string strBacl = "backup database " + dbName + " to disk='" + filePath;

        using (SqlCommand Cmd = new SqlCommand(strBacl, con))
        {
            con.Open();
            if (Cmd.ExecuteNonQuery() != 0)
            {
                con.Close();
                return Json(new { succeed = true, filePath = filePath, error = "备份数据库成功！" }, JsonRequestBehavior.AllowGet);
            }
            else
            {
                con.Close();
                return Json(new { succeed = false, error = "备份数据库失败！" }, JsonRequestBehavior.AllowGet);
            }
        }
    }
}

MVC控制器还原数据库：

/// <summary>
/// 数据库恢复操作
/// </summary>
/// <param name="dbName"></param>
/// <param name="fileUrlUpload"></param>
/// <returns></returns>
public ActionResult RecoverDatabase(string dbName, string fileUrlUpload)
{

    if (dbName == null || dbName.Length == 0)
        return Json(new { msg = false, error = "输入的数据库不存在！" }, JsonRequestBehavior.AllowGet);

    string DateStr  =ConfigurationManager.ConnectionStrings["master"].ConnectionString;

    string DbFilePath = @"F:\DbBackup\tmm\20171122\tmm_ba.bak";//Session["DbFilePath"].ToString();

    SqlConnection con = new SqlConnection(DateStr);
    if (con.State == ConnectionState.Open)
    {
        con.Close();
    }

    SqlConnection conn = new SqlConnection(DateStr);
    conn.Open();

    string strSQL = "select spid from master..sysprocesses where dbid=db_id( '" + dbName + "') ";

    SqlDataAdapter Da = new SqlDataAdapter(strSQL, conn);
    DataTable spidTable = new DataTable();
    Da.Fill(spidTable);

    SqlCommand Cmd = new SqlCommand();
    Cmd.CommandType = CommandType.Text;
    Cmd.Connection = conn;

    for (int iRow = 0; iRow <= spidTable.Rows.Count - 1; iRow++)
    {
        Cmd.CommandText = "kill " + spidTable.Rows[iRow][0].ToString();   //强行关闭用户进程 
        Cmd.ExecuteNonQuery();
    }

    //--------------------------------------------------------------------
    SqlConnection sqlcon = new SqlConnection(DateStr);
    sqlcon.Open();
    string sql = "backup DATABASE " + dbName + " to disk='" + DbFilePath + "' restore database "+dbName+" from disk='" + DbFilePath + "'";
    SqlCommand sqlCmd = new SqlCommand(sql, sqlcon);
    sqlCmd.ExecuteNonQuery();
    sqlCmd.Dispose();
    sqlcon.Close();
    sqlcon.Dispose();
    return Json(new { succeed = false, error = "备份数据库失败！" }, JsonRequestBehavior.AllowGet);
}

先需要切换到系统数据库下，然后先删除与需还原数据库相关的进程，然后在还原之前得先把数据库日志备份到开始的备份文件中，然后才还原备份文件，要不然会出错的，链接字符串在config文件中

明天再贴效果图，