从project菜单中选择add reference----COM---------
Microsoft DTSPackage Object Library与Microsoft SQLDMO Object Library
打开对象浏览器，展开Interop.SQLDMO，现实属性与方法
使用对象SQLServer,Backup,BackupDevices,Backupdevice,QueryResults
程序太大，留mail我发给你吧。
shaokui.wang@cn.abb.com
try
{
SQLDMO.Backup oBackup = new SQLDMO.BackupClass();
SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
oSQLServer.LoginSecure = false;
oSQLServer.Connect("localhost", "sa", "1234");
oBackup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
oBackup.Database = "Northwind";
oBackup.Files = @"d:/Northwind.bak";
oBackup.BackupSetName = "Northwind";
oBackup.BackupSetDescription = "数据库备份";
oBackup.Initialize = true;
oBackup.SQLBackup(oSQLServer);
}
catch
{
throw;
}
try
{
SQLDMO.Restore oRestore = new SQLDMO.RestoreClass();
SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
oSQLServer.LoginSecure = false;
oSQLServer.Connect("wellserver", "sa", "well");
oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
oRestore.Database = "czwelldb";
oRestore.Files = @"d:/czwelldb.bak";
oRestore.FileNumber = 1;
oRestore.ReplaceDatabase = true;
oRestore.SQLRestore(oSQLServer);
}
catch
{
throw;
}
主 题： 最近由于工作需要，写了个数据库的备份与恢复，贴出来与大家分享。
作 者： zhangqy (zhangqy)
信 誉 值： 90
所属论坛： .NET技术 C#
问题点数： 0
回复次数： 3
发表时间： 2004-02-26 12:28:54
最近由于工作需要，写了个数据库的备份与恢复，贴出来与大家分享。
准备工作:
1.先在项目中添加backfile.xml文件，根结点为<root></root>.
2.打开库master,新建存储过程,旨在杀掉其它用户的访问。其中@dbname为备份数据库的名称(以上实例为hdnjw,根据你的数据库名改。)
CREATE PROCEDURE killspid @dbname varchar(20)
as
begin
declare @sql nvarchar(500),@temp varchar(1000)
declare @spid int
set @sql='declare getspid cursor for
select spid from master..sysprocesses where dbid=db_id('''+@dbname+''')'
exec (@sql)
open getspid
fetch next from getspid into @spid
while @@fetch_status <> -1
begin
set @temp='kill '+rtrim(@spid)
exec(@temp)
fetch next from getspid into @spid
end
close getspid
deallocate getspid
end
GO
3.打开hdnjw,新建存储过程.
CREATE PROCEDURE BackDataBase @backPath varchar(100)
AS
BACKUP DATABASE hdnjw TO DISK = @backPath WITH NOINIT
GO
4.根椐我的经验，最好添加一个能访问，操作数据表的用户。
以下为备份与恢复的源程序.
//备份数据库(hdnjw),备份文件存于../../databackup/下
private void btnBack_Click(object sender, System.EventArgs e)
{
SqlDataBase.DbBase conn = new SqlDataBase.DbBase();
DateTime dt = DateTime.Now ;
string sPath = dt.Year.ToString() + dt.Month.ToString() + dt.Day.ToString() + dt.Hour.ToString() + dt.Minute.ToString() + dt.Second.ToString() ;
System.IO.DirectoryInfo oD = new DirectoryInfo( Server.MapPath( "../../DataBackUp/" ) ) ;
string sLinkName = "../../DataBackUp/" + sPath ;
//判断是否存在此目录,否则新建目录
if ( !oD.Exists )
oD.Create() ;
conn.BackDataBase( Server.MapPath( sLinkName ) ) ;
//XML格式为<root><backfile><id></id><title></title><createtime></createtime><linkname></linkname></backfile></root>
XmlDocument xmlDoc = new XmlDocument() ;
xmlDoc.Load( Server.MapPath( "backfile.xml" ) ) ;
//获取最后一个节点的递一个元素的值(ID) + 1 成为新的元素的ID值
XmlNode node = xmlDoc.LastChild.LastChild ;
int intID = 1 ;
if ( node != null )
intID += Convert.ToInt32( node.FirstChild.InnerText ) ;
//添加一个backfile节点
XmlElement elem = xmlDoc.CreateElement( "backfile" ) ;
XmlElement elem1 ;
XmlText xmlTxt ;
//添加backfile节点的id元素
elem1 = xmlDoc.CreateElement( "id" ) ;
xmlTxt = xmlDoc.CreateTextNode( intID.ToString() ) ; //文件名
elem1.AppendChild( xmlTxt ) ;
elem.AppendChild( elem1 ) ;
//添加backfile节点的filename元素
elem1 = xmlDoc.CreateElement( "title" ) ;
xmlTxt = xmlDoc.CreateTextNode( txtTitle.Text.Trim() ) ; //文件名
elem1.AppendChild( xmlTxt ) ;
elem.AppendChild( elem1 ) ;
//备份时间
elem1 = xmlDoc.CreateElement( "createtime" ) ;
xmlTxt = xmlDoc.CreateTextNode( DateTime.Now.ToString( "yyyy-MM-dd" ) ) ;
elem1.AppendChild( xmlTxt ) ;
elem.AppendChild( elem1 ) ;
//文件路径
elem1 = xmlDoc.CreateElement( "linkname" ) ;
xmlTxt = xmlDoc.CreateTextNode( DateTime.Now.ToString( sPath ) ) ;
elem1.AppendChild( xmlTxt ) ;
elem.AppendChild( elem1 ) ;
//添加操作,方便DataGrid取数据
elem1 = xmlDoc.CreateElement( "dosomething" ) ;
xmlTxt = xmlDoc.CreateTextNode( "恢复" ) ;
elem1.AppendChild( xmlTxt ) ;
elem.AppendChild( elem1 ) ;
//添加backfile节点
xmlDoc.DocumentElement.AppendChild( elem ) ;
xmlDoc.Save( Server.MapPath( "backfile.xml" ) ) ;
Response.Write( "<script language=javascript>alert('已成功备份!');var win =window.open('index.aspx','main');</script>" ) ;
}
//恢复数据库
private void BackUpDataBase ( string strID )
{
string strPath = "" ;
XmlDataDocument dom=new XmlDataDocument();
dom.Load ( Server.MapPath( "backfile.xml" ) ) ;
XmlElement root = dom.DocumentElement;
//查找id.value=Request["id"]的节点
XmlNode node = root.SelectSingleNode( "//backfile[id='"+strID +"']");
if( node != null ) //找到了
{
//文件路径节点
strPath = node.ChildNodes.Item(3).InnerText ;
}
if ( strPath != "" )
{
CommonOperation.Operation Common = new CommonOperation.Operation();
SqlDataBase.DbBase conn = new SqlDataBase.DbBase();
//以数据库管理员(sa)恢复数据,首先杀掉所有用户进程
conn.ConnStr = System.Configuration.ConfigurationSettings.AppSettings["DbConnAdmin"] ;
conn.DynamicStoredProcedure( "killspid" ) ;
SqlParameter sp_temp1;
sp_temp1 = conn.m_Command.Parameters.Add("@dbname",SqlDbType.VarChar,200);
sp_temp1.Direction = ParameterDirection.Input;
sp_temp1.Value = "hdnjw" ;
try
{
string FilePath = Server.MapPath( "../../DataBackUp/" + strPath ) ;
conn.ExecuteSqlForDynamic() ;
conn.ExecuteSql( "alter database hdnjw set RESTRICTED_USER with rollback immediate" ) ;//恢复数据时禁止除SA外的任何人访问库
string sRestroe = "Restore DataBase hdnjw From disk='"+ FilePath +"' " ;
conn.ExecuteSql( sRestroe ) ;
conn.ExecuteSql( "alter database hdnjw set MULTI_USER with rollback immediate" ) ;//充许访问库
conn.ConnStr = System.Configuration.ConfigurationSettings.AppSettings["DbConn"] ;
Response.Write( "<script language=javascript>alert('成功恢复数据!');window.close();</script>" ) ;
}
catch ( System.Exception ee )
{
Common.MsgBox( this,ee.ToString() ) ;
}
finally
{
conn.Close() ;
}
}
}
使用存储过程,根据需要传递相应参数即可
create proc BackupDB
@dbname sysname='', --要备份的数据库名称,不指定则备份当前数据库
@bkpath nvarchar(260)='', --备份文件的存放目录,不指定则使用SQL默认的备份目录
@bkfname nvarchar(260)='', --备份文件名,文件名中可以用/DBNAME/代表数据库名,/DATE/代表日期,/TIME/代表时间
@bktype nvarchar(10)='DB', --备份类型:'DB'备份数据库,'DF' 差异备份,'LOG' 日志备份
@appendfile bit=1 --追加/覆盖备份文件
as
declare @sql varchar(8000)
if isnull(@dbname,'')='' set @dbname=db_name()
if isnull(@bkpath,'')='' set @bkpath=dbo.f_getdbpath(null)
if isnull(@bkfname,'')='' set @bkfname='/DBNAME/_/DATE/_/TIME/.BAK'
set @bkfname=replace(replace(replace(@bkfname,'/DBNAME/',@dbname)
,'/DATE/',convert(varchar,getdate(),112))
,'/TIME/',replace(convert(varchar,getdate(),108),':',''))
set @sql='backup '+case @bktype when 'LOG' then 'log ' else 'database ' end +@dbname
+' to disk='''+@bkpath+@bkfname
+''' with '+case @bktype when 'DF' then 'DIFFERENTIAL,' else '' end
+case @appendfile when 1 then 'NOINIT' else 'INIT' end
print @sql
exec(@sql)
GO
////////////////////////////////////////////////////////////
SQL SERVER备份：
sql="backup database mydb to disk='"+nowpath+"//数据备份文件(不要删除).dll"+"' with format,name='Full Backup of TradeDb/SQL'";
try
{
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.ExecuteNonQuery();
MessageBox.Show("成功备份数据！备份记录为："+nowpath+"//数据备份文件(不要删除).dll"," 成功了！", MessageBoxButtons.OK, MessageBoxIcon.Information );
}
catch(Exception err)
{
MessageBox.Show("出现错误，请与作者联系！"+err.ToString() ,"出错了!",MessageBoxButtons.OK ,MessageBoxIcon.Question );
}
finally
{
conn.Close();
this.Close();
}
还原：
由于可能数据库被其它进程打开，所以写的一段SQL SERVER的代码，在MASTER中存为一个存储过程：
create proc killspid (@dbname varchar(20))
as
begin
declare @sql nvarchar(500)
declare @spid int
set @sql='declare getspid cursor for
select spid from sysprocesses where dbid=db_id('''+@dbname+''')'
exec (@sql)
open getspid
fetch next from getspid into @spid
while @@fetch_status < >-1
begin
exec('kill '+@spid)
fetch next from getspid into @spid
end
close getspid
deallocate getspid
end
然后，在C＃代码里：
try
{
string nowpath=Application.StartupPath;
//进入master
sql="use master";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.ExecuteNonQuery();
try
{
//执行存储过程杀掉其它进程
sql="exec killspid 'mydb'";
cmd = new SqlCommand(sql, conn);
cmd.ExecuteNonQuery();
}
finally
{
FileInfo backupdata = new FileInfo(nowpath+"//数据备份文件(不要删除).dll");
if (backupdata.Exists)
{
sql="restore database mydb from disk='"+nowpath+"//数据备份文件(不要删除).dll"+"' with recovery";
cmd = new SqlCommand(sql, conn);
cmd.ExecuteNonQuery();
MessageBox.Show("成功恢复数据！从"+nowpath+"//数据备份文件(不要删除).dll 中恢复"," 成功了！", MessageBoxButtons.OK, MessageBoxIcon.Information );
}
else
{
MessageBox.Show("你可能以前没有备份过数据库！数据备份文件(不要删除).dll 不存在！"," 错误！", MessageBoxButtons.OK, MessageBoxIcon.Information );
}
}
}
catch(Exception err)
{
MessageBox.Show("出现错误，请对数据库端进行正确配置！"+err.ToString() ,"出错了!",MessageBoxButtons.OK ,MessageBoxIcon.Question );
}
finally
{
conn.Close();
this.Close();
}
/************************************************************************************************/
//可参考下面代码。代码有待修改的地方，使用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
/******************************************************sqldmo
private void Form1_Load(object sender, System.EventArgs e)
{
try
{
//get all available SQL Servers
sqlServers = sqlApp.ListAvailableSQLServers();
for(int i=0;i<sqlServers.Count;i++)
{
object srv = sqlServers.Item( i + 1);
if(srv != null)
{
this.cboServers.Items.Add(srv);
}
}
if(this.cboServers.Items.Count > 0)
this.cboServers.SelectedIndex = 0;
else
this.cboServers.Text = "<No available SQL Servers>";
}
catch(Exception err)
{
MessageBox.Show(err.Message,"Error");
}
}
//Get Tables
private void linkLabel2_LinkClicked(object sender, System.Windows.Forms.LinkLabelLinkClickedEventArgs e)
{
try
{
this.Cursor = Cursors.WaitCursor;
this.lstObjects.Items.Clear();
this.cboDatabase.Items.Clear();
SQLDMO.SQLServer srv = new SQLDMO.SQLServerClass();
srv.Connect(this.cboServers.SelectedItem.ToString(),this.txtUser.Text,this.txtPassword.Text);
foreach(SQLDMO.Database db in srv.Databases)
{
if(db.Name!=null)
this.cboDatabase.Items.Add(db.Name);
}
this.cboDatabase.Sorted = true;
if(this.cboDatabase.Items.Count >0)
{
this.cboDatabase.SelectedIndex = 0;
this.cboDatabase.Enabled = true;
this.groupBox1.Enabled = true;
}
else
{
this.groupBox1.Enabled = false;
this.cboDatabase.Enabled = false;
this.cboDatabase.Text = "<No databases found>";
}
this.Cursor = Cursors.Default;
}
catch(Exception err)
{
this.Cursor = Cursors.Default;
MessageBox.Show(err.Message,"Error");
}
}
//Get stored procedures
private void linkLabel1_LinkClicked(object sender, System.Windows.Forms.LinkLabelLinkClickedEventArgs e)
{
this.Cursor = Cursors.WaitCursor;
SQLDMO.SQLServer srv = new SQLDMO.SQLServerClass();
srv.Connect(this.cboServers.SelectedItem.ToString(),this.txtUser.Text,this.txtPassword.Text);
for(int i=0;i<srv.Databases.Count;i++)
{
if(srv.Databases.Item(i+1,"dbo").Name == this.cboDatabase.SelectedItem.ToString())
{
SQLDMO._Database db= srv.Databases.Item(i+1,"dbo");
this.lstObjects.Items.Clear();
for(int j=0;j<db.Tables.Count;j++)
{
this.lstObjects.Items.Add(db.Tables.Item(j+1,"dbo").Name);
}
this.Cursor = Cursors.Default;
return;
}
}
this.Cursor = Cursors.Default;
}
// Get Views
private void linkLabel3_LinkClicked(object sender, System.Windows.Forms.LinkLabelLinkClickedEventArgs e)
{
this.Cursor = Cursors.WaitCursor;
SQLDMO.SQLServer srv = new SQLDMO.SQLServerClass();
srv.Connect(this.cboServers.SelectedItem.ToString(),this.txtUser.Text,this.txtPassword.Text);
for(int i=0;i<srv.Databases.Count;i++)
{
if(srv.Databases.Item(i+1,"dbo").Name == this.cboDatabase.SelectedItem.ToString())
{
SQLDMO._Database db= srv.Databases.Item(i+1,"dbo");
this.lstObjects.Items.Clear();
for(int j=0;j<db.StoredProcedures.Count;j++)
{
this.lstObjects.Items.Add(db.StoredProcedures.Item(j+1,"dbo").Name);
}
this.Cursor = Cursors.Default;
return;
}
}
this.Cursor = Cursors.Default;
}
private void linkLabel4_LinkClicked(object sender, System.Windows.Forms.LinkLabelLinkClickedEventArgs e)
{
this.Cursor = Cursors.WaitCursor;
SQLDMO.SQLServer srv = new SQLDMO.SQLServerClass();
srv.Connect(this.cboServers.SelectedText,this.txtUser.Text,this.txtPassword.Text);
for(int i=0;i<srv.Databases.Count;i++)
{
if(srv.Databases.Item(i+1,"dbo").Name == this.cboDatabase.SelectedItem.ToString())
{
SQLDMO._Database db= srv.Databases.Item(i+1,"dbo");
this.lstObjects.Items.Clear();
for(int j=0;j<db.Views.Count;j++)
{
this.lstObjects.Items.Add(db.Views.Item(j+1,"dbo").Name);
}
this.Cursor = Cursors.Default;
return;
}
}
this.Cursor = Cursors.Default;
}
private void cboDatabase_SelectedIndexChanged(object sender, System.EventArgs e)
{
this.lstObjects.Items.Clear();
}
}