如何远程备份SQL SERVER数据库
作者：Judy发布时间：2023-03-01浏览：457
手头上只有C#的代码，不知道对你有没有帮助
远程备份要注意权限的问题，普通的访问帐户没有备份和还原的权限，最好用sa的帐户，并且程序访问的帐户必须添加到远程机器的系统内
你说的远程备份到本地是不可能的，你只有开放远程某个文件夹加入你的特殊访问帐户权限
最好直接用远程桌面，这样最方便
方法一（不使用SQLDMO）：
///
///备份方法
///
SqlConnection conn = new SqlConnection("Server=.Database=masterUser ID=saPassword=sa")
SqlCommand cmdBK = new SqlCommand()
cmdBK.CommandType = CommandType.Text
cmdBK.Connection = conn
cmdBK.CommandText = @"backup database test to disk='C:ba' with init"
try
{
conn.Open()
cmdBK.ExecuteNonQuery()
MessageBox.Show("Backup successed.")
}
catch(Exception ex)
{
MessageBox.Show(ex.Message)
}
finally
{
conn.Close()
conn.Dispose()
}
///
///还原方法
///
SqlConnection conn = new SqlConnection("Server=.Database=masterUser ID=saPassword=saTrusted_Connection=False")
conn.Open()
//KILL Database Process
SqlCommand cmd = new SqlCommand("SELECtspidFROMsysprocesses ,sysdatabases WHEResysprocesses.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 i = 0 i < list.Count i++)
{
cmd = new SqlCommand(string.Format("KILL {0}", list[i]), conn)
cmd.ExecuteNonQuery()
}
SqlCommand cmdRT = new SqlCommand()
cmdRT.CommandType = CommandType.Text
cmdRT.Connection = conn
cmdRT.CommandText = @"restore database test fromdisk='C:ba'"
try
{
cmdRT.ExecuteNonQuery()
MessageBox.Show("Restore successed.")
}
catch(Exception ex)
{
MessageBox.Show(ex.Message)
}
finally
{
conn.Close()
}
方法二（使用SQLDMO）：
///
///备份方法
///
SQLDMO.Backup backup = new SQLDMO.BackupClass()
SQLDMO.SQLServer server = new SQLDMO.SQLServerClass()
//显示进度条
SQLDMO.BackupSink_PercentCompleteEventHandler progress = new SQLDMO.BackupSink_PercentCompleteEventHandler(Step)
backup.PercentComplete += progress
try
{
server.LoginSecure = false
server.Connect(".", "sa", "sa")
backup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database
backup.Database = "test"
backup.Files = @"D:testmyProgbackupTest"
backup.BackupSetName = "test"
backup.BackupSetDescription = "Backup the database of test"
backup.Initialize = true
backup.SQLBackup(server)
MessageBox.Show("Backup successed.")
}
catch(Exception ex)
{
MessageBox.Show(ex.Message)
}
finally
{
server.DisConnect()
}
this.pbDB.Value = 0
///
///还原方法
///
SQLDMO.Restore restore = new SQLDMO.RestoreClass()
SQLDMO.SQLServer server = new SQLDMO.SQLServerClass()
//显示进度条
SQLDMO.RestoreSink_PercentCompleteEventHandler progress = new SQLDMO.RestoreSink_PercentCompleteEventHandler(Step)
restore.PercentComplete += progress
//KILL Database Process
SqlConnection conn = new SqlConnection("Server=.Database=masterUser ID=saPassword=saTrusted_Connection=False")
conn.Open()
SqlCommand cmd = new SqlCommand("SELECtspid FROMsysprocesses ,sysdatabases WHEResysprocesses.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 i = 0 i < list.Count i++)
{
cmd = new SqlCommand(string.Format("KILL {0}", list[i]), conn)
cmd.ExecuteNonQuery()
}
conn.Close()
try
{
server.LoginSecure = false
server.Connect(".", "sa", "sa")
restore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database
restore.Database = "test"
restore.Files = @"D:testmyProgbackupTest"
restore.FileNumber = 1
restore.ReplaceDatabase = true
restore.SQLRestore(server)
MessageBox.Show("Restore successed.")
}
catch(Exception ex)
{
MessageBox.Show(ex.Message)
}
finally
{
server.DisConnect()
}
this.pbDB.Value = 0
