使用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

'*

名称：BackupDatabase

'*

功能：备份数据库

'*

控件：一个

文本框

和两个按钮（备份到和确定）

'*********************************************************

Public

Sub

BackupDatabase()

Dim

cn

As

New

ADODB.

Connection

Dim

s_path,

s_dataexport

As

String

s_path

=

App.Path

Me.MousePointer

=

11

'设置

鼠标指针

形状

'student1是需要备份的数据库名称

s_dataexport

=

"backup

database

student1

to

disk='"

+

CommonDialog

1.FileName

+

"'"

cn.Open

"driver={sql

server}server="

&

d1

&

"database=student1persist

security

info=false

userid=sa"

'数据库连接字符串

'这里不需要连接master数据库，即可完成备份

cn.BeginTrans

cn.Execute

s_dataexport

Err.Number

=

0

If

Err.Number

=

0

Then

cn.CommitTrans

MsgBox

"

数据备份

成功！",

vbInformation,

"提示"

MsgBox

"数据备份文件存放路径:"

&

CommonDialog

1.FileName,

vbOKOnly,

"提示"

Unload

Me

Else

cn.RollbackTrans

MsgBox

"数据备份失败！请检查数据库是否正在打开！",

vbCritical,

"提示"

End

If

cn.Close

Set

cn

=

Nothing

Me.MousePointer

=

1

End

Sub

using System

using System.Data

using System.Configuration

using System.Collections

using System.Web

using System.Web.Security

using System.Web.UI

using System.Web.UI.WebControls

using System.Web.UI.WebControls.WebParts

using System.Web.UI.HtmlControls

using System.IO

using System.Text

using _5dRss.Const

using _5dRss.lib.Data.Tool

public partial class admin_admin_dbmanage : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

//生成备份文件列表

ListBackupFiles()

if (!Page.IsPostBack)

{

Showmsg.Visible = false

//数据库路径

lilDBPath.Text = HttpContext.Current.Request.PhysicalApplicationPath + "App_Data\\" + ConfigurationSettings.AppSettings["DBFile"]

//新建一个 FileInfo 对象，并获得数据库文件的大小，然后转换单位为KB

FileInfo myFileInfo = new FileInfo(lilDBPath.Text)

lilDBSize.Text = Convert.ToString(myFileInfo.Length/1024) + " KB"

//如果两个参数都不为空，则继续执行

if (Request.QueryString["cmd"] != null &&Request.QueryString["source"] != null)

{

//备份数据库原文件名

string sourceFileName = Request.QueryString["source"]

//如果 cmd 参数为 DelFile

if (Request.QueryString["cmd"].Equals("DelFile"))

{

//删除备份数据库文件

File.Delete(HttpContext.Current.Request.PhysicalApplicationPath + "Backup\\" + sourceFileName)

//刷新备份文件列表

ListBackupFiles()

Showmsg.Visible = true

Showmsg.Text = "<div align='center' style='margin-bottom:8px'><img src='images/aL.gif' style='margin-bottom:-6px'/><span class='alertTxt'>删除备份数据库成功!</span><img src='images/aR.gif' style='margin-bottom:-6px'/></div>"

}

//如果 cmd 参数为 Restore

if (Request.QueryString["cmd"].Equals("Restore"))

{

//用备份文件覆盖原文件

File.Copy(HttpContext.Current.Request.PhysicalApplicationPath + "Backup\\" + sourceFileName, HttpContext.Current.Request.PhysicalApplicationPath + "App_Data\\" + ConfigurationSettings.AppSettings["DBFile"], true)

//刷新备份文件列表

ListBackupFiles()

Showmsg.Visible = true

Showmsg.Text = "<div align='center' style='margin-bottom:8px'><img src='images/aL.gif' style='margin-bottom:-6px'/><span class='alertTxt'>还原备份数据库成功!</span><img src='images/aR.gif' style='margin-bottom:-6px'/></div>"

}

}

}

}

protected void lnkbtnCompactDB_Click(object sender, EventArgs e)

{

//压缩修复数据库

AccessDBtool.CompactAccessDB(SysConfig.ConnectionString, HttpContext.Current.Request.PhysicalApplicationPath + "App_Data\\" + ConfigurationSettings.AppSettings["DBFile"])

Showmsg.Visible = true

Showmsg.Text = "<div align='center' style='margin-bottom:8px'><img src='images/aL.gif' style='margin-bottom:-6px'/><span class='alertTxt'>压缩修复数据库成功!</span><img src='images/aR.gif' style='margin-bottom:-6px'/></div>"

}

protected void lnkbtnBackupDB_Click(object sender, EventArgs e)

{

string sourceFileName = HttpContext.Current.Request.PhysicalApplicationPath + "App_Data\\" + ConfigurationSettings.AppSettings["DBFile"]

string destFileName = HttpContext.Current.Request.PhysicalApplicationPath + "Backup\\" + "Backup_"

destFileName += DateTime.Now.ToString("yyyyMMddHHmmss")

destFileName += ".mbk"

//将数据库文件Copy到Backup目录，如果有重名文件就覆盖原文件

File.Copy(sourceFileName, destFileName, true)

//生成备份文件列表

ListBackupFiles()

Showmsg.Visible = true

Showmsg.Text = "<div align='center' style='margin-bottom:8px'><img src='images/aL.gif' style='margin-bottom:-6px'/><span class='alertTxt'>备份数据库成功!</span><img src='images/aR.gif' style='margin-bottom:-6px'/></div>"

}

/// <summary>

/// 生成备份文件列表

/// </summary>

/// <returns>文件列表，文件详细信息及 *** 作选项的HTML代码</returns>

public void ListBackupFiles()

{

//如果目录不存在则创建次目录

if (!Directory.Exists(HttpContext.Current.Request.PhysicalApplicationPath + "Backup\\"))

Directory.CreateDirectory(HttpContext.Current.Request.PhysicalApplicationPath + "Backup\\")

DirectoryInfo mydir = new DirectoryInfo(HttpContext.Current.Request.PhysicalApplicationPath + "Backup\\")

StringBuilder sb = new StringBuilder()

foreach (FileInfo f in mydir.GetFiles())

{

sb.Append("<a href='backup/" + f.Name + "' target='_blank'><img border='0' src='images/mdb.gif' style='margin:4px 3px -3px 0px'/>" + f.Name + "</a> <a href='?cmd=DelFile&source=" + f.Name + "' title='删除备份文件'>删除</a>| <a href='?cmd=Restore&source=" + f.Name + "' title='删除备份文件'>还原数据库</a>| " + f.Length/1024 + " KB | " + f.CreationTime + "<br />")

}

lilBackupFileList.Text = sb.ToString()

}

}

把下面这句换成你的数据库地址：

//数据库路径

// lilDBPath.Text = HttpContext.Current.Request.PhysicalApplicationPath + "App_Data\\" + ConfigurationSettings.AppSettings["DBFile"]

---