SQL Server备份和放大器;恢复 [英] SQL Server backup&restore

问题描述

备份

 字符串connectionString1 =（@数据源= .\SQLEXPRESS; AttachDbFilename = | DataDirectory目录| Database1.mdf;数据库=数据库1;集成安全=真;用户实例=真）; 
的SqlConnection CN =新的SqlConnection（connectionString1）; 
 cn.Open（）; 
的SqlCommand CMD =新的SqlCommand（）; 
 SqlDataReader的阅读器; 
 cmd.CommandText = @BACKUP DATABASE数据库1 TO DISK ='C：\SRI2Works.bak'; 
 
 cmd.CommandType = CommandType.Text; 
 cmd.Connection = CN; 
读者= cmd.ExecuteReader（）; 
 cn.Close（）; 
 MessageBox.Show（数据库备份全成。）; 
  

恢复

 字符串connectionString1 =（@数据源= .\SQLEXPRESS; AttachDbFilename = | DataDirectory目录| Database1.mdf;数据库=数据库1;集成安全=真;用户实例=真）; 
的SqlConnection CN =新的SqlConnection（connectionString1）; 
 cn.Open（）; 
 
的SqlCommand CMD =新的SqlCommand（）; 
 SqlDataReader的阅读器; 
 cmd.CommandText = @利用总体; RESTORE DATABASE数据库1 FROM DISK ='C：\SRI2Works.bak'; 
 cmd.CommandText =DBCC CHECKDB（'数据库1'）; 
 cmd.CommandType = CommandType.Text; 
 cmd.Connection = CN; 
读者= cmd.ExecuteReader（）; 
 cn.Close（）; 
 MessageBox.Show（数据库恢复全成。）; 
  

此代码成功运行，但不进行任何更改。

< DIV CLASS =h2_lin>解决方案

在试用此代码还原数据库：

 私人无效restoreButton_Click （对象发件人，EventArgs五）
 {
串数据库= con.Database.ToString（）; 
如果（con.State = ConnectionState.Open！）
 {
 con.Open（）; 
} 
试
 {
串sqlStmt2 =的String.Format（ALTER DATABASE [+数据库+] WITH ROLLBACK IMMEDIATE SET SINGLE_USER）; 
的SqlCommand BU2 =新的SqlCommand（sqlStmt2，CON）; 
 bu2.ExecuteNonQuery（）; 
 
串sqlStmt3 =使用主RESTORE DATABASE [+数据库+] FROM DISK ='+ textBox2.Text +替换;; 
的SqlCommand BU3 =新的SqlCommand（sqlStmt3，CON）; 
 bu3.ExecuteNonQuery（）; 
 
串sqlStmt4 =的String.Format（ALTER DATABASE [+数据库+] SET MULTI_USER）; 
的SqlCommand BU4 =新的SqlCommand（sqlStmt4，CON）; 
 bu4.ExecuteNonQuery（）; 
 
 MessageBox.Show（数据库恢复successefully做）; 
 con.Close（）; 
 
} 
赶上（异常前）
 {
 MessageBox.Show（ex.ToString（））; 
} 
} 
  

有关更多解释查看本教程：的备份和放大器;使用C＃-FreelearningDz

还原SQL Server数据库

backup

string connectionString1 = (@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|Database1.mdf;Database=Database1;Integrated Security=True; User Instance=True");
            SqlConnection cn = new SqlConnection(connectionString1);
            cn.Open();
            SqlCommand cmd = new SqlCommand();
            SqlDataReader reader;
            cmd.CommandText = @"BACKUP DATABASE Database1 TO DISK = 'C:\SRI2Works.bak'";

            cmd.CommandType = CommandType.Text;
            cmd.Connection = cn;
            reader = cmd.ExecuteReader();
            cn.Close();
            MessageBox.Show("Database Backup Successfull.");

restore

string connectionString1 = (@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|Database1.mdf;Database=Database1;Integrated Security=True; User Instance=True");
            SqlConnection cn = new SqlConnection(connectionString1);
            cn.Open();

            SqlCommand cmd = new SqlCommand();
            SqlDataReader reader;
            cmd.CommandText = @"use master; RESTORE DATABASE Database1 FROM DISK = 'C:\SRI2Works.bak'";
            cmd.CommandText = "DBCC CHECKDB ('Database1')";
            cmd.CommandType = CommandType.Text;
            cmd.Connection = cn;
            reader = cmd.ExecuteReader();
            cn.Close();
            MessageBox.Show("Database Restored Successfull.");

This code runs successfully but doesn't make any changes.

解决方案

Try this code in Restore database:

    private void restoreButton_Click(object sender, EventArgs e)
    {
    string database = con.Database.ToString();
    if (con.State != ConnectionState.Open)
    {
        con.Open();
    }
    try
    {
        string sqlStmt2 = string.Format("ALTER DATABASE [" + database + "] SET SINGLE_USER WITH ROLLBACK IMMEDIATE");
        SqlCommand bu2 = new SqlCommand(sqlStmt2, con);
        bu2.ExecuteNonQuery();

        string sqlStmt3 = "USE MASTER RESTORE DATABASE [" + database + "] FROM DISK='" + textBox2.Text + "'WITH REPLACE;";
        SqlCommand bu3 = new SqlCommand(sqlStmt3, con);
        bu3.ExecuteNonQuery();

        string sqlStmt4 = string.Format("ALTER DATABASE [" + database + "] SET MULTI_USER");
        SqlCommand bu4 = new SqlCommand(sqlStmt4, con);
        bu4.ExecuteNonQuery();

        MessageBox.Show("database restoration done successefully");
        con.Close();

   }
   catch (Exception ex)
   {
        MessageBox.Show(ex.ToString());
   }
}

For more explanation check out this tutorial: Backup & Restore Sql Server database using C# -FreelearningDz

这篇关于SQL Server备份和放大器;恢复的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！