C#代码备份与还原数据库解决思路

C#代码备份与还原数据库

C# code

string myStr = System.Configuration.ConfigurationManager.AppSettings["98lunwen"];protected void btn_backup_Click(object sender, EventArgs e)    {        SqlConnection myConn = new SqlConnection(myStr);        string SqlStr1 = "Exec sp_helpdb";        myConn.Open();        SqlCommand com = new SqlCommand(SqlStr1, myConn);        SqlDataReader dr = com.ExecuteReader();        string SqlStr2 = "backup database lunwena to disk='~/Admin/BAK/lunwena_B.bak'";                try        {            SqlCommand com1 = new SqlCommand(SqlStr2, myConn);            com1.ExecuteNonQuery();            Response.Write("<script language=javascript>alert('备份数据成功！')</script>");        }        catch (Exception error)        {            Response.Write(error.Message);            Response.Write("<script language=javascript>alert('备份数据失败！')</script>");        }        finally        {            myConn.Close();        }               Response.Redirect("Web_Database.aspx");    }    protected void btn_return_Click(object sender, EventArgs e)    {        SqlConnection myConn = new SqlConnection(myStr);        string SqlStr1 = "Exec sp_helpdb";        myConn.Open();        SqlCommand com = new SqlCommand(SqlStr1, myConn);        SqlDataReader dr = com.ExecuteReader();        string SqlStr2 = "use master restore database lunwena_B from disk='~/Admin/BAK/lunwena_B.bak'";                try        {            SqlCommand com1 = new SqlCommand(SqlStr2, myConn);            com1.ExecuteNonQuery();            Response.Write("<script language=javascript>alert('还原数据成功！')</script>");        }        catch (Exception error)        {            Response.Write(error.Message);            Response.Write("<script language=javascript>alert('还原数据失败！')</script>");        }        finally        {            myConn.Close();        }        Response.Redirect("Web_Database.aspx");    }

以上代码能不能备份与还原数据库呢，各位高手指点一下，如果不能要怎样改？？

[解决办法]
不一定，备份SqlServer 数据库，不一定仅仅是sql指令是不是正确，还有连接，备份可以不断开连接，但还原需要断开连接。备份还要考虑备份位置有没有读写权限。
[解决办法]
调用存储过程或执行语句
CREATE proc dbo.Data_Backup @dbname sysname='',@bkpath nvarchar(260)='',@bkfname nvarchar(260)='',@bktype nvarchar(10)='DB',@appendfile bit=1
as declare @sql varchar(8000) if isnull(@dbname,'')='' set @dbname=db_name() 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),':',''))
update XTSZ set FNAME=@bkfname
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
exec(@sql)

USE master
EXEC sp_addumpdevice 'disk', 'MyNwind',
'c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\MyNwind.dat'

[解决办法]
我这里有个备份oracle 和图片的 批处理，你可以看下
@echo off
@echo =======================================================================
@echo 配置
set fileup=\\Web-1\FileUpload\fileUp\
set imges=\\Web-1\FileUpload\WebImages\ManageImages\NewsImages\
set saveurl=c:\11\%date:~0,10%\
@echo 创建备份目录(%saveurl%).....
md %saveurl%
@echo =======================================================================
@echo 备份数据库
@echo =======================================================================

exp madeinchina/madeinchina@web_server owner=madeinchina rows=y indexes=n compress=n buffer=65536 file='%saveurl%china%date:~0,10%.dmp' log='%saveurl%china%date:~0,10%.log'
@echo 备份china数据库成功
exp micask/micask@web owner=micask rows=y indexes=n compress=n buffer=65536 file='%saveurl%support%date:~0,10%.dmp' log='%saveurl%support%date:~0,10%.log'
@echo 备份support数据库成功
@echo =======================================================================
@echo 备份图片
@echo =======================================================================
@echo 开始备份NewsImages.....
rar a -m5 -r %saveurl%NewsImages.rar %imges%
@echo 备份NewsImages成功.....
@echo 开始备份fileup.....
rar a -m5 -r %saveurl%fileup.rar %fileup%
@echo 备份fileup成功.....

@echo %date:~0,10%的图片、数据备份结束....
pause

[解决办法]
private void button1_Click(object sender, EventArgs e)
{
Restore();
}





private bool RestoreBase(string serverName, string uID, string pWD, string dataBase, string bakPath)
{
SqlConnection RestoreCon = new SqlConnection("server=" + serverName + "; uid=" + uID + "; pwd=" + pWD + ";database=master");
SqlCommand RestoreCmd = new SqlCommand("killspid", RestoreCon);
RestoreCmd.CommandType = CommandType.StoredProcedure;
RestoreCmd.Parameters.Add("@dbname", SqlDbType.VarChar, 50);
RestoreCmd.Parameters["@dbname"].Value = "'" + dataBase + "'";
try
{
RestoreCon.Open();
RestoreCmd.ExecuteNonQuery();
SqlCommand RestoreCmd1 = new SqlCommand();
RestoreCmd1.CommandText = "RESTORE DATABASE " + dataBase + " FROM DISK='" + bakPath + "'";
RestoreCmd1.Connection = RestoreCon;
RestoreCmd1.ExecuteNonQuery();
RestoreCon.Close();
return true;
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
RestoreCon.Close();
return false;
}
}


ofd为openFileDialog控件

public void Restore()
{
try
{
ofd.InitialDirectory = Application.StartupPath + "\\";
ofd.Filter = "(*.bak)|*.bak|(所有文件)|*.*";
ofd.FilterIndex = 1;
ofd.RestoreDirectory = true;
if (ofd.ShowDialog() == DialogResult.OK)
{
this.textBox1.Text = ofd.FileName.ToString();
if (this.RestoreBase(".", "xiaohu", "41400991", "db_showHouse", ofd.FileName.ToString()) == true)
{
MessageBox.Show("还原成功!", "", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
ConnHelper.Sqlconn.connOpen();
}
else
{
this.button1.PerformClick();
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}




这是还原代码




private void button1_Click(object sender, EventArgs e)
{
beifenInfo();
}



备份方法

sd为savefileDialog控件的名称


public void beifenInfo()
{
sd.InitialDirectory = Application.StartupPath + "\\";

sd.Filter = "备份文件 (*.bak)|*.bak|所有文件 (*.*)|*.*";
sd.FilterIndex = 1;
sd.RestoreDirectory = true;
if (sd.ShowDialog() == DialogResult.OK)
{
if (!File.Exists(sd.FileName.ToString()))
{
ConnHelper.Sqlconn.connOpen();
SqlConnection con = new SqlConnection();
con.ConnectionString = "server=.; uid=xiaohu;pwd=41400991; database=db_showHouse";
con.Open();
SqlCommand cmd = new SqlCommand();
this.textBox1.Text = sd.FileName.ToString();
cmd.CommandText = "BACKUP DATABASE db_showHouse TO DISK ='" + sd.FileName.ToString() + "'";
cmd.Connection = con;
cmd.ExecuteNonQuery();
con.Close();
ConnHelper.Sqlconn.connClose();
con = null;
MessageBox.Show("数据备份成功!");
}
else
{
MessageBox.Show("请重新命名!");
}
}
}


这是备份代码

查看更多 下一篇