SQL语句来备份还原数据库

 

//通过SQL语句备份数据库
use master;BACKUP DATABASE dbHotelSystem TO DISK ='F:\\DB100151043851.bak'

//通过SQL语句还原数据库 （还原数据库后最好要求用户从新启动系统，保证数据还原）
use master; RESTORE   DATABASE  dbHotelSystem   FROM   DISK ='F:\\DB100151043851.bak' WITH REPLACE

//注意:很多时候不能直接还原,因为数据不是独占打开,可先执行一下方法。在进行还原操作
 public void kill()
        {
            //杀死原来所有的数据库连接进程   using System.Data.SqlClient;
             SqlConnectionStringBuilder conectionStringBuilder = new SqlConnectionStringBuilder();
            conectionStringBuilder.DataSource = ".";
            conectionStringBuilder.InitialCatalog = "master";
            conectionStringBuilder.IntegratedSecurity = true;
            SqlConnection con = new SqlConnection(conectionStringBuilder.ConnectionString);
            con.Open();
            string sql = "SELECT   spid   FROM   sysprocesses   ,sysdatabases   WHERE   sysprocesses.dbid=sysdatabases.dbid   AND   sysdatabases.Name='dbHotelSystem'";
            SqlCommand cmd = new SqlCommand(sql, con);
            SqlDataReader dtr;
            System.Collections.ArrayList list = new ArrayList();
            try
            {
                dtr = cmd.ExecuteReader();
                while (dtr.Read())
                {
                    list.Add(dtr.GetInt16(0));
                }
                dtr.Close();
            }
            catch (SqlException sqlex)
            {
                throw new Exception(sqlex.ToString());
            }
            finally
            {
                con.Close();
            }
            con.Open();
            for (int i = 0; i < list.Count; i++)
            {
                cmd = new SqlCommand(string.Format("KILL {0}", list[i].ToString()), con);
                cmd.ExecuteNonQuery();
            }
            con.Close();
        }