使用VB.net实现sql数据库的备份与恢复代码

Private Sub Button _Click(ByVal sender As System Object ByVal e As System EventArgs) Handles Button Click 备份数据库 BACKUP DATABASE Test TO DISK= C:\Backup WITH Format Dim cn As SqlConnection Dim cm As SqlCommand cn = getDB() cm = New SqlCommand( BACKUP DATABASE JXbase TO DISK= C:\Backup WITH Format cn) cn Open() cm ExecuteNonQuery() MsgBox( 备份成功 ) cn Close()

End Sub

恢复 Private Sub Button _Click(ByVal sender As System Object ByVal e As System EventArgs) Handles Button Click Dim cn As New SqlConnection Dim cn As New SqlConnection Dim mydr As SqlDataReader Dim str As String

Dim tmpConnectionString As String = Data Source=JJKInitial Catalog=masterIntegrated Security=True cn ConnectionString = tmpConnectionString cn ConnectionString = tmpConnectionString cn Open() cn Open() Dim cm As SqlCommand = New SqlCommand( select spid from master sysprocesses where dbid=db_id( JXbase ) cn) mydr = cm ExecuteReader()

Dim cm As SqlCommand = New SqlCommand() cm Connection = cn

While (mydr Read()) 杀死进程

str = kill &mydr( spid ) ToString() cm CommandText = str cm CommandType = CommandType Text Application DoEvents() cm ExecuteNonQuery() 杀进程 End While mydr Close() cm = New SqlCommand( RESTORE DATABASE JXbase FROM DISK= C:\Backup cn) 恢复备份 cm ExecuteNonQuery() MsgBox( 恢复成功成功 ) cn Close() cn Close()

lishixinzhi/Article/program/net/201311/12901

需要注意的时还原，还原的时候问题最大了，有别的用户使用数据库的时候无法还原，解决办法就是在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

GO

在还原之前先执行这个存储过程，需要传递dbname，就是你的数据库的名字。下边是类的原代码：（web.config里的数据库连接字符串是constr）

using System

using System.Configuration

using System.Data.SqlClient

using System.Data

namespace web.base_class

{

/// <summary>

/// DbOper类,主要应用SQLDMO实现对Microsoft SQL Server数据库的备份和恢复

/// </summary>

public class DbOper

{

private string server

private string uid

private string pwd

private string database

private string conn

/// <summary>

/// DbOper类的构造函数

/// </summary>

public DbOper()

{

conn=System.Configuration.ConfigurationSettings.AppSettings["constr"].ToString()

server=cut(conn,"server=","")

uid=cut(conn,"uid=","")

pwd=cut(conn,"pwd=","")

database=cut(conn,"database=","")

}

public string cut(string str,string bg,string ed)

{

string sub

sub=str.Substring(str.IndexOf(bg)+bg.Length)

sub=sub.Substring(0,sub.IndexOf(""))

return sub

}

/// <summary>

/// 数据库备份

/// </summary>

public　bool DbBackup(string url)

{

SQLDMO.Backup oBackup = new SQLDMO.BackupClass()

SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass()

try

{

oSQLServer.LoginSecure = false

oSQLServer.Connect(server,uid, pwd)

oBackup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database

oBackup.Database = database

oBackup.Files = url//"d:\Northwind.bak"

oBackup.BackupSetName = database

oBackup.BackupSetDescription = "数据库备份"

oBackup.Initialize = true

oBackup.SQLBackup(oSQLServer)

return true

}

catch

{

return false

throw

}

finally

{

oSQLServer.DisConnect()

}

}

/// <summary>

/// 数据库恢复

/// </summary>

public string DbRestore(string url)

{

if(exepro()!=true)//执行存储过程

{

return " *** 作失败"

}

else

{

SQLDMO.Restore oRestore = new SQLDMO.RestoreClass()

SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass()

try

{

oSQLServer.LoginSecure = false

oSQLServer.Connect(server, uid, pwd)

oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database

oRestore.Database = database

oRestore.Files = url//@"d:\Northwind.bak"

oRestore.FileNumber = 1

oRestore.ReplaceDatabase = true

oRestore.SQLRestore(oSQLServer)

return "ok"

}

catch(Exception e)

{

return "恢复数据库失败"

throw

}

finally

{

oSQLServer.DisConnect()

}

}

}

private bool exepro()

{

SqlConnection conn1 = new SqlConnection("server="+server+"uid="+uid+"pwd="+pwd+"database=master")

SqlCommand cmd = new SqlCommand("killspid",conn1)

cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.Add("@dbname","port")

try

{

conn1.Open()

cmd.ExecuteNonQuery()

return true

}

catch(Exception ex)

{

return false

}

finally

{

conn1.Close()

}

}

}

}

把MYSQL里的mysqldump 这个文件复制到你所在目录下就可以吧，如：/backup//// <summary>

/// 备份MYSQL数据

/// </summary>

/// <param name="db_ip">IP地址</param>

/// <param name="db_port">端口</param>

/// <param name="db_user">用户名</param>

/// <param name="db_pwd">密码</param>

/// <param name="db_name">数据库名</param>

/// <param name="backup_path">备份路径</param>

/// <returns></returns>

public string Exec(string db_ip, string db_port, string db_user, string db_pwd, string db_name, string backup_path)

{

string _dump = Server.MapPath("/backup")

Process p = new Process()

p.StartInfo.FileName = "cmd.exe"

p.StartInfo.UseShellExecute = false

p.StartInfo.RedirectStandardInput = true

p.StartInfo.RedirectStandardOutput = true

p.StartInfo.RedirectStandardError = true

p.StartInfo.CreateNoWindow = true

string strOutput = null

try

{

p.Start()

p.StandardInput.WriteLine("@echo 开始对" + db_ip + "服务器上" + db_name + "执行备份 *** 作")

p.StandardInput.WriteLine("IF NOT EXIST " + backup_path + " md " + backup_path + "")

p.StandardInput.WriteLine("@" + _dump + "\\mysqldump -h" + db_ip + " -P" + db_port + " -u" + db_user + " -p" + db_pwd + " --opt " + db_name + " >" + backup_path + "\\" + db_name + "_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".sql")

p.StandardInput.WriteLine("@echo 对" + db_ip + "服务器MYSQL数据库文件" + db_name + "备份成功")

p.StandardInput.WriteLine("@echo 备份文件所在路径 " + backup_path + "\\" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".sql")

p.StandardInput.WriteLine("exit")

strOutput = p.StandardOutput.ReadToEnd()

p.WaitForExit()

p.Close()

}

catch (Exception e)

{

strOutput = e.Message

}

return strOutput

}

---