sql数据库备份

Question

SQL Server?备份整个数据库：BACKUP DATABASE { database_name | @database_name_var } 
TO < backup_device > [ ,...n ] 
[ WITH
    [ BLOCKSIZE = { blocksize | @blocksize_variable } ]
    [ [ , ] DESCRIPTION = { 'text' | @text_variable } ]
    [ [ , ] DIFFERENTIAL ]
    [ [ , ] EXPIREDATE = { date | @date_var }
        | RETAINDAYS = { days | @days_var } ]
    [ [ , ] PASSWORD = { password | @password_variable } ]
    [ [ , ] FORMAT | NOFORMAT ]
    [ [ , ] { INIT | NOINIT } ]
    [ [ , ] MEDIADESCRIPTION = { 'text' | @text_variable } ]
    [ [ , ] MEDIANAME = { media_name | @media_name_variable } ]
    [ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ]
    [ [ , ] NAME = { backup_set_name | @backup_set_name_var } ]
    [ [ , ] { NOSKIP | SKIP } ]
    [ [ , ] { NOREWIND | REWIND } ]
    [ [ , ] { NOUNLOAD | UNLOAD } ]
    [ [ , ] RESTART ]
    [ [ , ] STATS [ = percentage ] ] 
]备份特定的文件或文件组：BACKUP DATABASE { database_name | @database_name_var }
    < file_or_filegroup > [ ,...n ] 
TO < backup_device > [ ,...n ] 
[ WITH
    [ BLOCKSIZE = { blocksize | @blocksize_variable } ]
    [ [ , ] DESCRIPTION = { 'text' | @text_variable } ]
    [ [ , ] DIFFERENTIAL ]
    [ [ , ] EXPIREDATE = { date | @date_var }
        | RETAINDAYS = { days | @days_var } ]
    [ [ , ] PASSWORD = { password | @password_variable } ]
    [ [ , ] FORMAT | NOFORMAT ]
    [ [ , ] { INIT | NOINIT } ]
    [ [ , ] MEDIADESCRIPTION = { 'text' | @text_variable } ]
    [ [ , ] MEDIANAME = { media_name | @media_name_variable } ]
    [ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ]
    [ [ , ] NAME = { backup_set_name | @backup_set_name_var } ]
    [ [ , ] { NOSKIP | SKIP } ]
    [ [ , ] { NOREWIND | REWIND } ]
    [ [ , ] { NOUNLOAD | UNLOAD } ]
    [ [ , ] RESTART ]
    [ [ , ] STATS [ = percentage ] ] 
]--------------------------
学习一下。

Answer 1

switch ( this.FAction )
{
case Actions.BACKUP:
try
{ SqlCommand sqlcom = new SqlCommand("sp_addumpdevice",sqlconn); sqlcom.CommandType=CommandType.StoredProcedure; SqlParameter sparm=sqlcom.Parameters.Add("@devtype",SqlDbType.VarChar,20); sparm.Value="disk"; sparm=sqlcom.Parameters.Add("@logicalname",SqlDbType.VarChar); sparm.Value= "Backup"+DateTime.Now.ToString("yyyyMMddHHmmss");  //该参数对应逻辑文件名称，不能重复，每次需更改 sparm=sqlcom.Parameters.Add("@physicalname",SqlDbType.VarChar); sparm.Value= Application.StartupPath +"\\Backup\\"+DateTime.Now.ToString("yyyyMMddHHmmss")+".dat";//如果已进行一次绑定，下一次需更名。
sqlcom.ExecuteNonQuery(); SqlCommand sqlcom1 = new SqlCommand("BACKUP DATABASE GSPMgr  TO "+"Backup"+DateTime.Now.ToString("yyyyMMddHHmmss"),sqlconn); sqlcom1.ExecuteNonQuery();
//设置滚动条
if ( this.progressBar1.Value == 50 )
{
this.timer1.Stop();
}
this.progressBar1.Value = 50;
this.timer1.Stop();
//提示成功
this.label1.Text  = "备份数据完成！";// break;
}

catch ( System.Exception ex1 )
{
MessageBox.Show("数据备份失败！请重试！"+ex1.Message,"系统提示",MessageBoxButtons.OK,MessageBoxIcon.Information);
this.Close();
}
break; case Actions.RESTORE:
try
{
//通过文件还原，如果要还原成其它数据库，在sql server的联机Transact_sql中有相关的参考和示例--RESTORE DATABASE //关闭其他所有链接
SqlCommand sqlcom2 = new SqlCommand( "killconn" ,sqlconn);
sqlcom2.CommandType = CommandType.StoredProcedure;
SqlParameter database = sqlcom2.Parameters.Add ( "@database",System.Data.SqlDbType.VarChar,50);
database.Value = "GSPMgr";
sqlcom2.ExecuteNonQuery(); //
SqlCommand sqlcom3 =new SqlCommand("use master\n RESTORE DATABASE GSPMgr FROM Disk='" +this.filename +"'" ,sqlconn);
sqlcom3.ExecuteNonQuery();
this.label1.Text  = "还原数据完成！"; //设置滚动条
if ( this.progressBar1.Value == 50 )
{
this.timer1.Stop();
}
this.progressBar1.Value = 50;
this.timer1.Stop();
//提示成功
this.label1.Text  = "还原数据完成！";
}
catch ( System.Exception ex2 )
{
MessageBox.Show( "还原数据失败！"+ex2.Message,"系统提示",MessageBoxButtons.OK,MessageBoxIcon.Information );
}
break;
}

Answer 2

SQL Server的帮助里说得很清楚的，还有例子，你在帮助里搜索一下”备份数据库“

Answer 3

sqldemo备份数据库发布时,需要发布不少的dll,建议还是通过sql语句备份吧.
以下是备份还原的源码(sqldemo备份被注释了)
public class SqlDatabaseMaintain
{
private string m_serverName,m_userName,m_password;

/// <summary>
/// 构造函数
/// </summary>
/// <param name="serverName">服务器名</param>
/// <param name="userName">用户名</param>
/// <param name="password">密码</param>
public SqlDatabaseMaintain(string serverName,string userName,string password)
{
try
{ this.m_serverName=serverName;
this.m_password=password;
this.m_userName=userName;// this.srv = new SQLDMO.SQLServerClass();
// srv.Connect(serverName,userName,password);

}
catch
{
throw;
}
}

#region 备份数据库
/// <summary>
/// 备份数据库
/// </summary>
/// <param name="serverName">服务器名称</param>
/// <param name="userName">用户名</param>
/// <param name="password">密码</param>
/// <param name="databaseName">数据库名</param>
/// <param name="fileName">备份文件名</param>
public void BackupDatabaseToFile(string databaseName,string fileName)
{
/*SQLDMO备份方式
bak = null;
bak = new SQLDMO.BackupClass();
bak.Devices = bak.Files;
bak.Files = fileName;
bak.Database = databaseName;
// bak.Complete    += new SQLDMO.BackupSink_CompleteEventHandler(this.OnBackupComplete);
bak.PercentComplete    += new SQLDMO.BackupSink_PercentCompleteEventHandler(this.OnBackupPercentComplete);
bak.SQLBackup(srv);
*/
System.Data.SqlClient.SqlCommand Command=new System.Data.SqlClient.SqlCommand();
System.Data.SqlClient.SqlConnection sqlConnection= new System.Data.SqlClient.SqlConnection();; string SQL;
string strCon;


try
{
Command.Connection=sqlConnection;

strCon = "data source="+this.m_serverName+";initial catalog=master;u" +
"ser id="+this.m_userName+";Password="+this.m_password+"";

Command.Connection.ConnectionString=strCon;

try
{
Command.Connection.Open();
}
catch
{
}


if(Command.Connection.State==System.Data.ConnectionState.Open)
{ Command.Connection.ChangeDatabase("master"); SQL=@" BACKUP DATABASE "+databaseName+" TO disk=  '"+fileName+"' ";
 

Command.CommandText=SQL;
Command.CommandType=System.Data.CommandType.Text;
Command.ExecuteNonQuery(); Command.Connection.Close();

}
}
catch(System.Exception ea)
{

}
}
public void AbortBackup() {if (this.bak != null) this.bak.Abort();} private void OnBackupComplete(string message) {if (this.BackupComplete != null) this.BackupComplete(message);}
private void OnBackupPercentComplete(string message,int percent) {if (this.BackupPercentComplete != null) this.BackupPercentComplete(message,percent);}
#endregion

#region 恢复数据库
/// <summary>
/// 恢复数据库
/// </summary>
/// <param name="databaseName">数据库名称</param>
/// <param name="fileName">文件名</param>
public  void RestoreDatabaseFromFile(string databaseName,string fileName)
{
/*恢复数据库SQLDMO方式
res = new SQLDMO.RestoreClass();
res.Devices = res.Files;
res.Files = fileName;
res.Database = databaseName;
//res.ReplaceDatabase = true;
// res.Complete    += new SQLDMO.RestoreSink_CompleteEventHandler(this.OnRestoreComplete);
res.PercentComplete    += new SQLDMO.RestoreSink_PercentCompleteEventHandler(this.OnRestorePercentComplete);
res.SQLRestore(srv);
*/
System.Data.SqlClient.SqlCommand Command=new System.Data.SqlClient.SqlCommand();
System.Data.SqlClient.SqlConnection sqlConnection= new System.Data.SqlClient.SqlConnection();; string SQL;
string strCon;


try
{
Command.Connection=sqlConnection;

strCon = "data source="+this.m_serverName+";initial catalog=master;u" +
"ser id="+this.m_userName+";Password="+this.m_password+"";

Command.Connection.ConnectionString=strCon; try
{
Command.Connection.Open();
}
catch
{
}


if(Command.Connection.State==System.Data.ConnectionState.Open)
{ Command.Connection.ChangeDatabase("master"); SQL=@" RESTORE DATABASE "+databaseName+" FROM  disk=  '"+fileName+"' ";
  Command.CommandText=SQL;
Command.CommandType=System.Data.CommandType.Text;
Command.ExecuteNonQuery(); Command.Connection.Close();

}
}
catch(System.Exception ea)
{

} }
public void AbortRestore() {if (this.res != null) this.res.Abort();}
private void OnRestoreComplete(string message) {if (this.RestoreComplete != null) this.RestoreComplete(message);}
private void OnRestorePercentComplete(string message,int percent) {if (this.RestorePercentComplete != null) this.RestorePercentComplete(message,percent);}
#endregion
public  event SQLDMO.BackupSink_CompleteEventHandler  BackupComplete;
public  event SQLDMO.BackupSink_PercentCompleteEventHandler BackupPercentComplete;
public  event SQLDMO.RestoreSink_CompleteEventHandler  RestoreComplete;
public  event SQLDMO.RestoreSink_PercentCompleteEventHandler RestorePercentComplete; private SQLDMO._SQLServer srv;
private SQLDMO.Backup   bak;
private SQLDMO.Restore   res;

Answer 4

我说还是用dts包备份数据比较好。也比较简单，不用写那么麻烦的代码。