两种远程备份和还原SQL数据库的方法
第一种:使用SQLDMO在ASP.NET页面下实现数据库的备份与恢复
首先需要添加对SQLDMO引用
需要注意的时还原，还原的时候问题最大了，有别的用户使用数据库的时候无法还原，解决办法就是在master数据库中添加一个存储过程：
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
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();
//形式如:conn="server=192.168.1.53\\yj;database=Test;uid=sa;pwd=aiqindao;";
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","Test");
try
{
conn1.Open();
cmd.ExecuteNonQuery();
return true;
}
catch(Exception ex)
{
return false;
}
finally
{
conn1.Close();
}
}
}
}
//***************************************数据库备份**************************************\\
protected void btnBackUp_Click(object sender, EventArgs e)
{
string filepath = string.Empty;
if (txtFileName.Text.Trim() != "")
{
filepath = txtFileName.Text.Trim();
}
else
{
filepath = GetNewFileName();
}
//引用Web.config的数据库连接字符串 优点：数据库移植的时候可直接对Web.config的数据库连接字符串中的IP进行更换.
string con_str = "server=192.168.1.53\\yj;database=Test;uid=sa;pwd=aiqindao";
SqlConnection conn = new SqlConnection(con_str);
SqlCommand cmdBK = new SqlCommand();
cmdBK.CommandType = CommandType.Text;
cmdBK.Connection = conn;
cmdBK.CommandText = @"backup database Test to disk='..\Backup\" + filepath + ".bak'" + " with init";
try
{
conn.Open();
cmdBK.ExecuteNonQuery();
Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "backup", "alert('数据库备份或更新备份成功!')", true);
}
catch (Exception)
{
Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "backup", "alert('抱歉,服务器连接失败!或者要备份的数据库不存在于服务器中,请先尝试新建或还原!')", true);
}
finally
{
conn.Close();
conn.Dispose();
}
}
//***************************************数据库还原**************************************\\
/// <summary>
/// 产生一个由时间+随机数组成的一个文件名
/// </summary>
/// <returns></returns>
public string GetNewFileName()
{
Random rand = new Random();
//根据原始文件名产生一个由时间+随机数组成的一个新的文件名
string newfilename = DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString()
+ DateTime.Now.Day.ToString() + DateTime.Now.Hour.ToString()
+ DateTime.Now.Second.ToString() + DateTime.Now.Minute.ToString()
+ DateTime.Now.Millisecond.ToString()
+ rand.Next(1000).ToString();
return newfilename;
}
protected void btnRestore_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection("server=192.168.1.53\\yj;database=master;uid=sa;pwd=1;")
try
{
conn.Open();
//杀死所有正在使用要还原数据的数据库进程
SqlCommand cmd = new SqlCommand("SELECT spid FROM sysprocesses ,sysdatabases WHERE sysprocesses.dbid=sysdatabases.dbid AND sysdatabases.Name='Test'", conn);
SqlDataReader dr;
dr = cmd.ExecuteReader();
ArrayList list = new ArrayList();
while (dr.Read())
{
list.Add(dr.GetInt16(0));
}
dr.Close();
for (int k = 0; k < list.Count; k++)
{
cmd = new SqlCommand(string.Format("KILL {0}", list[k]), conn);
cmd.ExecuteNonQuery();
}
SqlCommand cmdRT = new SqlCommand();
cmdRT.CommandType = CommandType.Text;
cmdRT.Connection = conn;
cmdRT.CommandText = @"restore database Test from disk= '..\Backup\aaa" + ".bak'" + " ";
cmdRT.ExecuteNonQuery();
System.Web.HttpContext.Current.Response.Write("<Script>alert('数据库还原成功!');</Script>");
}
catch (Exception)
{
System.Web.HttpContext.Current.Response.Write("<Script>alert('抱歉,服务器连接失败!或者要还原的数据库备份不存在于服务器中,请先尝试备份;或者数据库目前运转正常而不必还原!');</Script>");
}
finally
{
conn.Close();
}
}
浙公网安备 33010602011771号