C#中数据库备份与恢复问题。

Question

SQL2005数据库数据备份：
        private void btn备份_Click(object sender, EventArgs e)
        {
            if (this.txtBakName.Text != "" && this.txtPlace.Text != "")
            {
                string P_Str_cmdtxt2 = "backup database YZG to disk='" + this.txtPlace.Text.Trim() + "\\" + this.txtBakName.Text.Trim() + ".bak'";
                if (this.GetExecute(P_Str_cmdtxt2))
                {
                    MessageBox.Show("数据备份成功！", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                else
                {
                    MessageBox.Show("数据备份失败！", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
            }
            else
            {
                MessageBox.Show("请输入备份的正确位置及文件名！", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }
数据恢复：
                    SqlConnection conn = con.OpenConn();
                    string P_Str_cmdtxt = "USE master DECLARE tb CURSOR LOCAL FOR SELECT 'Kill '+ CAST(Spid AS VARCHAR) FROM master.dbo.sysprocesses";
                    P_Str_cmdtxt += "WHERE dbid=DB_ID('YZG') DECLARE @s nvarchar(1000) OPEN tb FETCH tb INTO @s";
                    P_Str_cmdtxt += "WHILE @@FETCH_STATUS = 0 BEGIN EXEC (@s) FETCH tb INTO @s END CLOSE tb DEALLOCATE tb";
                    P_Str_cmdtxt += "RESTORE DATABASE YZG FROM disk='" + P_Str_BakPath + "'";
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                        try
                        {
                            SqlCommand sqlcom = new SqlCommand(P_Str_cmdtxt, conn);
                            sqlcom.ExecuteNonQuery();
                            MessageBox.Show("数据还原成功！", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                            if (conn.State == ConnectionState.Closed)
                            {
                                conn.Open();
                            }
                        }备份可以备份出来；但是恢复不进去。

Answer 1

数据还原时你的数据库连接有问题，不能连接现在的数据库，要连接系统数据库master

Answer 2

                    SqlConnection conn = new SqlConnection(@"server=54951d;database=master;Uid=sa;Pwd=123");
还是不对。

Answer 3

if (conn.State == ConnectionState.Open) 
                    { 
                        conn.Close(); 
                        try 
                        { 
                            SqlCommand sqlcom = new SqlCommand(P_Str_cmdtxt, conn); 
                            sqlcom.ExecuteNonQuery(); 
                            MessageBox.Show("数据还原成功！", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information); 
                            if (conn.State == ConnectionState.Closed) 
                            { 
                                conn.Open(); 
                            } 
                       有问题，连接都关闭了怎么执行

Answer 4

數據庫還原的時候,需要把鏈接到該DB的所有用戶連接斷開,并且把DB下線掉.再Recover.
用程式來做,就要十分注意了
還原比備份復雜多了

Answer 5

斷開的是所有鏈接到這個DB的鏈接而已,你可以到mastr DB上去操作,不是讓你斷開整個Server的鏈接..
不過還原真不太推薦用程式做(除非十分有必要),要不然要DBA做什么呢?

Answer 6

                    string P_Str_cmdtxt = "USE master DECLARE tb CURSOR LOCAL FOR SELECT 'Kill '+ CAST(Spid AS VARCHAR) FROM master.dbo.sysprocesses";
                    P_Str_cmdtxt += "WHERE dbid=DB_ID('YZG') DECLARE @s nvarchar(1000) OPEN tb FETCH tb INTO @s";
                    P_Str_cmdtxt += "WHILE @@FETCH_STATUS = 0 BEGIN EXEC (@s) FETCH tb INTO @s END CLOSE tb DEALLOCATE tb";
                    P_Str_cmdtxt += "RESTORE DATABASE YZG FROM disk='" + P_Str_BakPath + "'";
                    if (con.OpenConn().State == ConnectionState.Open)
                    {
                        con.OpenConn().Close();
                        try
                        {
                            SqlCommand sqlcom = new SqlCommand(P_Str_cmdtxt, con.OpenConn());
                            sqlcom.ExecuteNonQuery();
                            MessageBox.Show("数据还原成功！", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                        }关闭了连接的

Answer 7

                    if (con.OpenConn().State == ConnectionState.Open)
                    {
                        con.OpenConn().Close();
                        try
                        {
                            SqlConnection conn = new SqlConnection("server=Listen;database=master;Uid=sa;Pwd=611118");
                            conn.Open();
                            SqlCommand sqlcom = new SqlCommand(P_Str_cmdtxt,conn);
                            sqlcom.ExecuteNonQuery();
                            MessageBox.Show("数据还原成功！", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                            if (con.OpenConn().State == ConnectionState.Closed)
                            {
                                con.OpenConn().Open();
                            }
                        }
                        catch
                        {
                            btn恢复_Click(sender, e);
                        }
                    }这样有问题没有？？？