Question

winform C#2005备份SQL2000数据库！！！绝对高分

VS是05的数据库是2000的，我现在想实现一个功能就是：点击备份，可以选择备份路径，之后点击确定就可以备份了，同样的方式可以实现还原！很急。。
只要您能做出来，400分送上，说话算数，不够可以再添，只要您能做出来！！
用winfrom（c#）做一个数据库的备份和还原！！数据库是2000VS是2005，备份的时候都可以选择路径！
这下能明白了吗？？？？

3Q,我不知道怎么判断是否成功备份和还原备份，还有就是恢复的时候如何把其他连接kill掉，帮帮忙剩下200分也给你！

Answer 1

1 先实现一个SMO处理类

public class SMOHelper
{

private static bool _bExpress = true;
internal static Server _objServer;

/// <summary>
/// Initializes the field 'server'
/// </summary>
public static void InitializeServer(string strServer, string strUser, string strPwd , bool bInterCheck)
{
string strCheck = ";User ID=" + strUser + ";Password=" + strPwd;
string strConnString = @"Integrated Security=SSPI; Data Source=(local)\SQLEXPRESS";

if (bInterCheck)
{
strCheck = ";Integrated Security=True;";
}

if (strServer.Trim() != "")
{
strConnString = @"Data Source= " + strServer + strCheck;
_bExpress = false;
}

// To Connect to our SQL Server - we Can use the Connection from the System.Data.SqlClient Namespace.
SqlConnection sqlConnection = new SqlConnection(strConnString);

//build a "serverConnection" with the information of the "sqlConnection"
ServerConnection serverConnection = new ServerConnection(sqlConnection);

//The "serverConnection is used in the ctor of the Server.
_objServer = new Server(serverConnection);
}

/// <summary>
/// Creates the database.
/// </summary>
/// <param name="strName">Name of the STR.</param>
public static void CreateDatabase(string strName)
{
Database database = new Database(_objServer, strName);
database.Create();
}

/// <summary>
/// Backups the database.
/// </summary>
/// <param name="strPath">The STR path.</param>
/// <param name="Name">The name.</param>
public static void BackupDatabase(string strPath, string Name)
{
BackupDevice backupDevice = new BackupDevice(_objServer, Name);
backupDevice.PhysicalLocation = strPath;
backupDevice.BackupDeviceType = BackupDeviceType.Disk;
}

/// <summary>
/// Restores the database.
/// </summary>
/// <param name="strPath">The STR path.</param>
/// <param name="dbName">Name of the db.</param>
/// <param name="strRestoreDir">The STR restore dir.</param>
public static void RestoreDatabase(string strPath, string dbName , string strRestoreDir)
{
DataTable dt;
DataRow[] foundrows;
string strDBName = "";
string strLogName = "";

Restore db = new Restore();
string location = strPath;// @"C:\Program Files\Honeywell\HUSServer\HUSWebConfig\DB\HUSWEB.bak";
FileInfo objFile = new FileInfo(location);
if (objFile.Exists == false)
{
throw (new Exception("备份文件不存在！"));
}

DirectoryInfo objDir = new DirectoryInfo(strRestoreDir);
if (objDir.Exists == false)
{
objDir.Create();
}

db.Database = dbName;
db.Action = RestoreActionType.Database;
//db.NoRecovery = true;
db.ReplaceDatabase = true;
db.Devices.Add(new BackupDeviceItem(location, DeviceType.File));

dt = db.ReadFileList(_objServer);

foundrows = dt.Select();

strDBName = foundrows[0]["LogicalName"].ToString();
strLogName = foundrows[1]["LogicalName"].ToString();

db.RelocateFiles.Add(new RelocateFile(strDBName, strRestoreDir + "\\" + dbName + ".mdf"));
db.RelocateFiles.Add(new RelocateFile(strLogName, strRestoreDir + "\\" + dbName + ".ldf"));
Database currentDb = _objServer.Databases[dbName];
if (currentDb != null)
_objServer.KillAllProcesses(dbName);

db.SqlRestore(_objServer);
db.Wait();
}

/// <summary>
/// Excutes the script.
/// </summary>
/// <param name="Script">The script.</param>
public static void ExcuteScript(string Script)
{
string[] SqlLine;
Regex regex = new Regex("^GO", RegexOptions.IgnoreCase | RegexOptions.Multiline);

string txtSQL = Script;
SqlLine = regex.Split(txtSQL);

foreach (string line in SqlLine)
{
if (line.Length > 0)
{
_objServer.ConnectionContext.ExecuteNonQuery(line);
}
}
}

/// <summary>
/// Changes the service logon.
/// </summary>
public static void ChangeServiceLogon()
{
_objServer.Logins["sa"].DefaultDatabase = "";
string strExpress = "MSSQL$SQLEXPRESS";
string strSql = "MSSQLSERVER";
string strName = "";
if (_bExpress)
{
strName = strExpress;
}
else
{
strName = strSql;
}
}

/// <summary>
/// Enables the account SA.
/// </summary>
public static void EnableAccountSA()
{
try
{
if (_objServer.Settings.LoginMode != ServerLoginMode.Mixed)
{
_objServer.Settings.LoginMode = ServerLoginMode.Mixed;
_objServer.Settings.Alter();
}
_objServer.Alter();

bool isLogin;
isLogin = _objServer.Logins["sa"].IsDisabled;
if (isLogin)
{
_objServer.Logins["sa"].Enable();
}

//_objServer.Logins["sa"].ChangePassword("");
}
catch (Exception e)
{
Console.WriteLine(e);
}
}

/// <summary>
/// Creates the remote connection.
/// </summary>
public static void CreateRemoteConnection()
{

}

/// <summary>
/// Gets the db path.
/// </summary>
/// <returns></returns>
public static string GetDbPath()
{
string dbPath = "";

return dbPath;
}

/// <summary>
/// Excutes the restore.
/// </summary>
/// <param name="path">The path.</param>
public static void ExcuteRestore(string path)
{
string strConn = "Server=127.0.0.1;UId=sa;PWD=123456789sa;Connect Timeout=1;Max Pool Size=1;Connection Lifetime=10;";
string strCtrl = "\r\n";
string strCommands = "";
string strMdfFile = path + "Husweb.mdf";
string strLdfFile = path + "Husweb.ldf";
string strBackupFile = path + "HusWeb.bak";

SqlConnection objConn = new SqlConnection(strConn);
SqlCommand cmd = objConn.CreateCommand();

//string path = GetDbPath();

strCommands = "USE MASTER" + strCtrl +
"RESTORE DATABASE Test" + strCtrl +
//@"FROM DISK = '"+path+"'+'Husweb.bak'" + strCtrl +
@"FROM DISK = '" + strBackupFile + "'" + strCtrl +
"WITH FILE=1," + strCtrl +
@"MOVE 'Husweb' TO '" + strMdfFile + "'" + strCtrl +
@"MOVE 'Husweb_log' TO '" + strLdfFile + "'" +
"REPLACE";

cmd.CommandText = strCommands;

try
{
objConn.Open();
cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
MessageBox.Show(ex.ToString(), "SQL Server Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
finally
{
if (objConn.State == ConnectionState.Open)
objConn.Close();

cmd.Dispose();

objConn.Dispose();
}
}

/// <summary>
/// Creates the user.
/// </summary>
/// <param name="UserName">Name of the user.</param>
public static void CreateUser(string UserName)
{
Login objUser = new Login(_objServer,UserName);
objUser.LoginType = LoginType.SqlLogin;
objUser.Create("123456789sa");
objUser.AddToRole("sysadmin");
objUser.Alter();
}

public static bool CheckExist(string strDBName)
{
return _objServer.Databases.Contains(strDBName);
}
}

2 备份处理方法
private void DoBackup(string strPath, string Name)
{
SMOHelper.InitializeServer(frm.Server, frm.User, frm.Pwd, frm.InterCheck);

SMOHelper.EnableAccountSA();

SMOHelper.BackupDatabase(strPath, Name);

MessageBox.Show("数据库备份成功！");
}

关于文件选择，请参见 lcg1986 的回答。

Answer 2

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace WindowsApplication11
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
//记得在界面上拖入一个folderBrowserDialog控件

private void btnBackUp_Click(object sender, EventArgs e)
{
try
{
if (this.folderBrowserDialog1.ShowDialog() == DialogResult.OK)
{
string path = this.folderBrowserDialog1.SelectedPath;
string sql = string.Format("Backup database MyTest to disk = '{0}'", path+"\\"+txtBackupName.Text); //MyTest为远程数据库名称，也可以改成用变量传入
SqlConnection conn = new SqlConnection(@"Data Source=ALEX;Initial Catalog=master;User ID=sa;pwd=sa");
conn.Open();
SqlCommand command = new SqlCommand(sql, conn);
command.ExecuteNonQuery();
//下面判断文件是否生成来确定是否备份成功

//还原是一样的.只是sql改成"RESTORE FILELISTONLY FROM DISK ='路径'"
}
}
catch (Exception err)
{
MessageBox.Show(err.Message);
}
}
}
}

Answer 3

BEGIN TRANSACTION
DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1
EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'

-- 删除同名的警报（如果有的话）。
SELECT @JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'backupassay')
IF (@JobID IS NOT NULL)
BEGIN
-- 检查此作业是否为多重服务器作业
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @JobID) AND (server_id <> 0)))
BEGIN
-- 已经存在，因而终止脚本
RAISERROR (N'无法导入作业“backupassay”，因为已经有相同名称的多重服务器作业。', 16, 1)
GOTO QuitWithRollback
END
ELSE
-- 删除〔本地〕作业
EXECUTE msdb.dbo.sp_delete_job @job_name = N'backupassay'
SELECT @JobID = NULL
END

BEGIN

-- 添加作业
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'backupassay', @owner_login_name = N'sa', @description = N'备份', @category_name = N'[Uncategorized (Local)]', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- 添加作业步骤
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'完全备份', @command = N'DECLARE @BakSql varchar(300),@BakDate varchar(15),@BakPath varchar(50),@Description nvarchar(50)
SET @BakDate=CONVERT(varchar,GETDATE(),112)--+''-''+left(replace(convert(varchar,getdate(),108),'':'',''''),4)
SET @BakPath=''备份路径\''+@BakDate+''.bak''
SET @Description=''备份文件名''
SET @BakSql=''BACKUP DATABASE [assay] TO DISK = N''''''+@BakPath+'''''' WITH INIT,NOUNLOAD,NAME = N''''''+@BakDate+'''''',NOSKIP,STATS=10,NOFORMAT,DESCRIPTION =N''''''+@Description+''''''''
EXEC (@BakSql)
', @database_name = N'Assay', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- 添加作业调度
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'调度1', @enabled = 1, @freq_type = 4, @active_start_date = 20090101, @active_start_time = 40000, @freq_interval = 1, @freq_subday_type = 1, @freq_subday_interval = 2, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- 添加目标服务器
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

里面可以修改你的数据库名字和字段名

Answer 4

用那么大一段话就实现个备份未免太麻烦了吧.
//备份的方法，直接用就行了，当然有些地方要改改。
//path就是路径了，InitialCatalog备份数据库名
void IDBService.Backup(string path, string InitialCatalog)
{
using (SqlConnection connection = new SqlConnection (connectionstring))
{
try
{
SqlCommand command = new SqlCommand();
command.Connection = connection;
command.CommandText = string.Format("use master backup database {0} to disk = '{1}'", InitialCatalog, path);//备份sql语句
connection.Open();
command.ExecuteNonQuery();
command.CommandText = string.Format("use {0}", InitialCatalog);
command.ExecuteNonQuery();
connection.Close();
connection.Dispose();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
}

Answer 5

利用备份文件可以说能还原.也可以说覆盖原文件.你到底想问什么?