C#实现数据库名称获取、数据库备份和还原

实现数据库的备份:
            SqlConnection conn = new SqlConnection(connection);
              string databaseName=@"E:\Data\Test.mdf"; //指定数据库名称(完整路径)
              conn.Open();
              string bakSQL = @"backup database @DataBaseName to disk=N'E:\Temp\Test.bak'";     //数据库备份SQL语句           
              try
              {
                  SqlCommand cmd = new SqlCommand(bakSQL,conn);
                  cmd.CommandType = CommandType.Text;
                  cmd.Parameters.AddWithValue("@DataBaseName",databaseName);  //参数
                  cmd.ExecuteNonQuery();
              }
              catch (Exception ee)
              {
                  Console.WriteLine(ee);
              }
数据库备份的还原:(待修改)
  public static void Restore()
         {
 
              SqlConnection conn = new SqlConnection(connection);
               try
               {
                conn.Open();

                //杀掉数据库连接的进程
                 SqlCommand killCmd = new SqlCommand("use master; SELECT spid FROM sysprocesses ,sysdatabases WHERE sysprocesses.dbid=sysdatabases.dbid AND sysdatabases.Name='Test'", conn);
                 SqlDataReader myreader = killCmd.ExecuteReader();
                 ArrayList list = new ArrayList();
                 while (myreader.Read())
                 {
                     list.Add(myreader.GetInt16(0));

                 }
                 myreader.Close();
                 for (int i = 0; i < list.Count; i++)
                 {
                     killCmd = new SqlCommand(string.Format("KILL{0}",list[i]),conn);
                     killCmd.ExecuteNonQuery();
                     Console.WriteLine(string.Format("Kill线程{0}",i));
                 }

              //数据库文件恢复
                 string restoreSQL = @"restore database test from disk=N'E:\Temp\Test.bak' with  move 'Test' to 'D:\\Data.mdf'";//此处为SQL恢复语句,报错,待修改
                 SqlCommand cmd = new SqlCommand(restoreSQL, conn);
                cmd.CommandType = CommandType.Text;

                   cmd.ExecuteNonQuery();
               }
             catch(Exception ee)
             {
                 Console.WriteLine(ee);
             }
参考文章:http://topic.csdn.net/u/20070514/20/fa0a9389-5f0e-4c27-a6af-1a1b5120eb19.html


获取当前连接的SQL Server数据库名称:

 public static void GetDataBaseName()
        {
           
                SqlConnection conn = new SqlConnection(connection);
              try
              {
               conn.Open();
                string bakSQL = "select db_name(); ";               //查询当前使用的数据库名字的SQL语句
                SqlCommand cmd = new SqlCommand(bakSQL, conn);
                cmd.CommandType = CommandType.Text;
                SqlDataReader myreader = cmd.ExecuteReader();   
                if (myreader.Read())
                {
                    Console.WriteLine(myreader.GetString(0));          //在控制台输出
                }
                else
                {
                    Console.WriteLine("无结果");
                }
             
            }
            catch(Exception ee)
            {
                Console.WriteLine(ee);
            }
        }
用SQL Server自带的存储过程,获取主机上所有数据库的名称,并将数据库名称的集合绑定到DropDownList控件上:

 SqlConnection connection = new ConnString().GetSqlConn();
                SqlCommand command = new SqlCommand("sp_helpdb", connection);
                command.CommandType = CommandType.StoredProcedure;
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                ddlDatabaseList.DataSource = reader;
                ddlDatabaseList.DataTextField = "Name";
                ddlDatabaseList.DataBind();
                reader.Close();
                connection.Close();

在上述实现数据库的恢复时候出错,希望老鸟给予指点!




©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页