.net与数据库备份

[zmbysj]

请问在座的高手朋友们，在.net中怎么实现数据备份还原操作呢？
不好意思，尽量给代码！

[凌雯]

学习

[CathySun118]

//可参考下面代码。代码有待修改的地方，使用sql语句进行数据备份，恢复
/// <summary>
/// 备份数据库
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void _ButtonBackUp_Click(object sender, System.EventArgs e)
{
dbConnSet frm = new dbConnSet();//获得数据库sa帐号，密码
frm.ShowDialog();
if(frm.isNormal==false)
return;
this.Cursor = Cursors.WaitCursor;
string _DBSource = "(local)";
string _DBCatalog = "master";
string _DBUser = frm.textBoxDBUser.Text;
string _DBPass = frm.textBoxDBPas.Text;
string m_ConnectionStr = "Provider=SQLOLEDB;Password="+_DBPass+";User ID="+_DBUser+";Initial Catalog="+_DBCatalog+";Data Source="+_DBSource+";Connect Timeout=5;";
OleDbConnection _Conn = new OleDbConnection(m_ConnectionStr);
try
{
_Conn.Open();
NetBee.Controls.FolderBrowserEX.FolderBrowser _SaveFileDialog = new NetBee.Controls.FolderBrowserEX.FolderBrowser(); //保存的文件目录路径
_SaveFileDialog.Description = "请选择备份保存的目录";
if(_SaveFileDialog.ShowDialog()==DialogResult.OK)
{

string _FolderPath = _SaveFileDialog.DirectoryPath;
string[] DBName = {Cs.PubObject.dbName};
for(int i=0;i<DBName.Length;i++)
{

string _DBName = DBName[i];
OleDbCommand _Comm = new OleDbCommand("",_Conn);
//执行数据库备份命令
_Comm.CommandText = "BACKUP DATABASE "+_DBName+" TO DISK = '"+ _SaveFileDialog.DirectoryPath+@"\"+_DBName +".bak'";
_Comm.ExecuteNonQuery();
}
this.Cursor = Cursors.Arrow;
_Conn.Close();
MessageBox.Show("备份数据成功!","提示",MessageBoxButtons.OK,MessageBoxIcon.Information);


}
this.Cursor = Cursors.Arrow;

}
catch(System.Exception error)
{
this.Cursor = Cursors.Arrow;
MessageBox.Show("异常:"+error.Message,"提示",MessageBoxButtons.OK,MessageBoxIcon.Error);
}
this.Cursor = Cursors.Arrow;
}

#endregion

#region 恢复数据库
/// <summary>
/// 恢复数据库
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void _ButtonRestory_Click(object sender, System.EventArgs e)
{
dbConnSet frm = new dbConnSet();
frm.ShowDialog();
if(frm.isNormal==false)
return;
if(MessageBox.Show("该操作将数据覆盖!!\n\n如果选择[是],将原来的数据覆盖\n\n如果选择[否],将退出安装","提示",MessageBoxButtons.YesNo,MessageBoxIcon.Question)==DialogResult.No)
return;
if(MessageBox.Show("请在此确认,该操作不能恢复!!\n\n如果选择[是],将原来的数据覆盖\n\n如果选择[否],将退出安装","提示",MessageBoxButtons.YesNo,MessageBoxIcon.Warning)==DialogResult.No)
return;
this.Cursor = Cursors.WaitCursor;
string _DBSource = "(local)";
string _DBCatalog = "master";
string _DBUser = frm.textBoxDBUser.Text;
string _DBPass = frm.textBoxDBPas.Text;
string m_ConnectionStr = "Provider=SQLOLEDB;Password="+_DBPass+";User ID="+_DBUser+";Initial Catalog="+_DBCatalog+";Data Source="+_DBSource+";Connect Timeout=5;";
OleDbConnection _Conn = new OleDbConnection(m_ConnectionStr);
try
{
_Conn.Open();
NetBee.Controls.FolderBrowserEX.FolderBrowser _SaveFileDialog = new NetBee.Controls.FolderBrowserEX.FolderBrowser();
_SaveFileDialog.Description = "请要恢复的数据目录";
if(_SaveFileDialog.ShowDialog()==DialogResult.OK)
{

string _FolderPath = _SaveFileDialog.DirectoryPath;
string[] DBName = {Cs.PubObject.dbName};
for(int i=0;i<DBName.Length;i++)
{
string _DBName = DBName[i];
string _BakFilePath = _SaveFileDialog.DirectoryPath+@"\"+DBName[i]+@".bak";
OleDbCommand _Comm = new OleDbCommand("restore filelistonly from disk='"+_FolderPath+@"\"+_DBName+@".bak'",_Conn);//获得原来的逻辑名称以及物理路径
OleDbDataReader _Reader = null;
_Reader = _Comm.ExecuteReader();
string[] _LogName = new string[2];//获得逻辑名称
string[] _PhiPath = new string[2];//获得物理路径
int j = 0;
while(_Reader.Read())
{
_LogName[j] = (string)_Reader.GetValue(0);
_PhiPath[j] = (string)_Reader.GetValue(1);
j = j+1;
}
_Reader.Close();

//执行数据库恢复脚本
_Comm = new OleDbCommand("",_Conn);
_Comm.CommandText ="RESTORE DATABASE [" + _DBName + "] FROM DISK = '" + _BakFilePath + "' With Move '"+ _LogName[0] +
"' TO '" +_PhiPath[0]+ "', Move '" + _LogName[1] + "' TO '" + _PhiPath[1]+ "'";
_Comm.ExecuteNonQuery();

}
this.Cursor = Cursors.Arrow;
MessageBox.Show("数据恢复成功!","提示",MessageBoxButtons.OK,MessageBoxIcon.Information);
_Conn.Close();

}
this.Cursor = Cursors.Arrow;

}
catch(System.Exception error)
{
this.Cursor = Cursors.Arrow;
MessageBox.Show("异常:"+error.Message,"错误",MessageBoxButtons.OK,MessageBoxIcon.Error);
}
}
#endregion

[liuyun1987]

private   void   BackUpSQLServer() 
                { 
                        string   str   =   strPath   +   "\\ "   +   DateTime.Now.Year.ToString()   +   DateTime.Now.Month.ToString()   +   DateTime.Now.Day.ToString(); 
                        string   strConnectstring   =   "Server= "   +   this.textBox2.Text.Trim()   +   ";Database=Master;User   ID= "   +   this.textBox4.Text.Trim()   +   ";Password= "   +   textBox4.Text.Trim()   +   "; "; 
                        SqlConnection   conn   =   new   SqlConnection(strConnectstring); 

                        SqlCommand   cmdBK   =   new   SqlCommand(); 
                        cmdBK.CommandType   =   CommandType.Text; 
                        cmdBK.Connection   =   conn; 
                        cmdBK.CommandText   =   @ "backup   database   "   +   textBox3.Text.Trim()   +   "   to   disk= ' "   +   str   +   " '   with   init "; 

                        try 
                        { 
                                conn.Open(); 
                                cmdBK.ExecuteNonQuery(); 
                                this.richTextBox1.Text   =   "备份成功\n文件路径为： "   +   str   +   "\n操作时间：\n "   +   DateTime.Now.ToString(); 

                                this.richTextBox1.ForeColor   =   Color.Blue; 
                        } 
                        catch   (Exception   ex) 
                        { 
                                this.richTextBox1.Text   =   "备份失败\n失败原因\n "   +   ex.ToString()   +   "\n操作时间\n "   +   DateTime.Now.ToString(); 
                                this.richTextBox1.ForeColor   =   Color.Red; 
                        } 
                        finally 
                        { 
                                conn.Close(); 
                                conn.Dispose(); 
                        } 
                } 

                private   void   RestoreSQLServer() 
                { 
                        string   strConnectstring   =   "Server= "   +   this.textBox2.Text.Trim()   +   ";Database=Master;User   ID= "   +   this.textBox4.Text.Trim()   +   ";Password= "   +   textBox5.Text.Trim()   +   "; "; 
                        SqlConnection   conn   =   new   SqlConnection(strConnectstring); 
                        conn.Open(); 

                        //KILL   DataBase   Process   
                        SqlCommand   cmd   =   new   SqlCommand( "SELECT   spid   FROM   dbo.sysprocesses   ,dbo.sysdatabases   WHERE   dbo.sysprocesses.dbid=dbo.sysdatabases.dbid   AND   dbo.sysdatabases.Name= ' "   +   textBox3.Text.Trim()   +   " ' ",   conn); 
                        SqlDataReader   dr; 
                        dr   =   cmd.ExecuteReader(); 
                        ArrayList   list   =   new   ArrayList(); 
                        while   (dr.Read()) 
                        { 
                                list.Add(dr.GetInt16(0)); 
                        } 
                        dr.Close(); 
                        for   (int   i   =   0;   i   <   list.Count;   i++) 
                        { 
                                cmd   =   new   SqlCommand(string.Format( "KILL   {0} ",   list[i]),   conn); 
                                cmd.ExecuteNonQuery(); 
                        } 

                        SqlCommand   cmdRT   =   new   SqlCommand(); 
                        cmdRT.CommandType   =   CommandType.Text; 
                        cmdRT.Connection   =   conn; 
                        cmdRT.CommandText   =   @ "restore   database   "   +   textBox3.Text.Trim()   +   "     from   disk= ' "   +   strPath   +   " ' "; 

                        try 
                        { 
                                cmdRT.ExecuteNonQuery(); 
                                this.richTextBox1.Text   =   "还原成功\n文件路径为： "   +   strPath   +   "\n操作时间：\n "   +   DateTime.Now.ToString(); 
                                this.richTextBox1.ForeColor   =   Color.Blue; 
                        } 
                        catch   (Exception   ex) 
                        { 
                                this.richTextBox1.Text   =   "还原失败\n失败原因\n "   +   ex.ToString()   +   "\n操作时间：\n "   +   DateTime.Now.ToString(); 
                                this.richTextBox1.ForeColor   =   Color.Red; 
                        } 
                        finally 
                        { 
                                conn.Close(); 
                        }   
                } 

[zmbysj]

谢谢各位导师。。。o(∩_∩)o...

[ericzhangbo1982111]

数据库备份还可以用sqlserver的dts包一样很好用