SQL数据据分离，附加，备份，还原工具代码,MSSQL代码库,德仔网

代码语言
代码分类
【MSSQL】 SQL数据据分离，附加，备份，还原工具
作者:Dezai.CN / 发布于2011/5/16/ 602
<div>
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;
namespace ADDSQL
{
public partial class Form1 : Form
{
public string ConnectionString;
private SqlConnection Conn;
private SqlCommand Comm;
public string StrSQL;
public string DataBaseName;
public string DataBase_MDF;
public string DataBase_LDF;
public string DataBaseOfBackupName;
public string DataBaseOfBackupPath;
public Form1()
{
InitializeComponent();
InitData();
}
/// <summary>
/// 初始化数据
/// </summary>
private void InitData()
{
this.ConnectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"].ToString();
}
/// <summary> 
/// 执行创建/修改数据库和表的操作 
/// </summary> 
public void DataBaseAndTableControl()
{
try
{
Conn = new SqlConnection(ConnectionString);
Conn.Open();
Comm = new SqlCommand();
Comm.Connection = Conn;
Comm.CommandText = StrSQL;
Comm.CommandType = CommandType.Text;
Comm.ExecuteNonQuery();
MessageBox.Show("数据库操作成功！", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally
{
Conn.Close();
}
}
/// <summary> 
/// 附加数据库 
/// </summary> 
public void AddDataBase()
{
try
{
Conn = new SqlConnection(ConnectionString);
Conn.Open();
Comm = new SqlCommand();
Comm.Connection = Conn;
Comm.CommandText = "sp_attach_db";
Comm.Parameters.Add(new SqlParameter(@"dbname", SqlDbType.NVarChar));
Comm.Parameters[@"dbname"].Value = DataBaseName;
Comm.Parameters.Add(new SqlParameter(@"filename1", SqlDbType.NVarChar));
Comm.Parameters[@"filename1"].Value = DataBase_MDF;
Comm.Parameters.Add(new SqlParameter(@"filename2", SqlDbType.NVarChar));
Comm.Parameters[@"filename2"].Value = DataBase_LDF;
Comm.CommandType = CommandType.StoredProcedure;
Comm.ExecuteNonQuery();
MessageBox.Show("附加数据库成功", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally
{
Conn.Close();
}
}
/// <summary> 
/// 分离数据库 
/// </summary> 
public void DeleteDataBase()
{
try
{
Conn = new SqlConnection(ConnectionString);
Conn.Open();
Comm = new SqlCommand();
Comm.Connection = Conn;
Comm.CommandText = @"sp_detach_db";
Comm.Parameters.Add(new SqlParameter(@"dbname", SqlDbType.NVarChar));
Comm.Parameters[@"dbname"].Value = DataBaseName;
Comm.CommandType = CommandType.StoredProcedure;
Comm.ExecuteNonQuery();
MessageBox.Show("分离数据库成功", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally
{
Conn.Close();
}
}
/// <summary> 
/// 备份数据库 
/// </summary> 
public void BackupDataBase()
{
try
{
Conn = new SqlConnection(ConnectionString);
Conn.Open();
Comm = new SqlCommand();
Comm.Connection = Conn;
Comm.CommandText = "use master;backup database @dbname to disk = @backupname;";
Comm.Parameters.Add(new SqlParameter(@"dbname", SqlDbType.NVarChar));
Comm.Parameters[@"dbname"].Value = DataBaseName;
Comm.Parameters.Add(new SqlParameter(@"backupname", SqlDbType.NVarChar));
Comm.Parameters[@"backupname"].Value = @DataBaseOfBackupPath + @DataBaseOfBackupName;
Comm.CommandType = CommandType.Text;
Comm.ExecuteNonQuery();
MessageBox.Show("备份数据库成功", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally
{
Conn.Close();
}
}
/// <summary> 
/// 还原数据库 
/// </summary> 
public void ReplaceDataBase()
{
try
{
string BackupFile = @DataBaseOfBackupPath + @DataBaseOfBackupName;
Conn = new SqlConnection(ConnectionString);
Conn.Open();
Comm = new SqlCommand();
Comm.Connection = Conn;
Comm.CommandText = "use master;restore database @DataBaseName From disk = @BackupFile with replace;";
Comm.Parameters.Add(new SqlParameter(@"DataBaseName", SqlDbType.NVarChar));
Comm.Parameters[@"DataBaseName"].Value = DataBaseName;
Comm.Parameters.Add(new SqlParameter(@"BackupFile", SqlDbType.NVarChar));
Comm.Parameters[@"BackupFile"].Value = BackupFile;
Comm.CommandType = CommandType.Text;
Comm.ExecuteNonQuery();
MessageBox.Show("还原数据库成功", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally
{
Conn.Close();
}
}
// 还原数据库 
private void button0_Click(object sender, EventArgs e)
{
DataBaseName = this.txtBaseName.Text;
DataBaseOfBackupName = this.txtBfName.Text;
DataBaseOfBackupPath = this.txtBfPath.Text;
ReplaceDataBase();
}
// 附加数据库 
private void button1_Click(object sender, EventArgs e)
{
DataBase_MDF = this.txtData.Text;
DataBase_LDF = this.txtLOG.Text;
AddDataBase();
}
// 备份数据库 
private void button2_Click(object sender, EventArgs e)
{
DataBaseName = this.txtBaseName1.Text;
DataBaseOfBackupName = this.txtBfName1.Text +".bak";
DataBaseOfBackupPath = this.txtBfPath1.Text;
BackupDataBase();
}
// 分离数据库 
private void button3_Click(object sender, EventArgs e)
{
DataBaseName = this.txtDataBase.Text; ;
DeleteDataBase();
}
private void button5_Click(object sender, EventArgs e)
{
this.ofdDialog.ShowDialog();
this.txtData.Text = this.ofdDialog.FileName.ToString();
}
private void button6_Click(object sender, EventArgs e)
{
this.fbdDialog.ShowDialog();
this.txtData.Text = this.fbdDialog.SelectedPath;
}
private void button4_Click(object sender, EventArgs e)
{
ofdDialog.Title = "选择数据文件";
ofdDialog.InitialDirectory = @"c:\";
ofdDialog.Filter = "数据文件(*.mdf)|*.mdf";//|All files (*.*)|*.*";
ofdDialog.FileName = "";
ofdDialog.RestoreDirectory = true;
if (this.ofdDialog.ShowDialog() == DialogResult.OK)
{
string target = ".";
char[] anyof = target.ToCharArray();
int at = this.ofdDialog.SafeFileName.ToString().LastIndexOfAny(anyof);
DataBaseName = this.ofdDialog.SafeFileName.Remove(at);
this.txtData.Text = this.ofdDialog.FileName.ToString();
}
}
private void button7_Click(object sender, EventArgs e)
{
ofdDialog.Title = "选择日志文件";
ofdDialog.InitialDirectory = @"c:\";
ofdDialog.Filter = "数据文件(*.ldf)|*.ldf";//|All files (*.*)|*.*";
ofdDialog.FileName = "";
ofdDialog.RestoreDirectory = true;
if (this.ofdDialog.ShowDialog() == DialogResult.OK)
{
this.txtLOG.Text = this.ofdDialog.FileName.ToString();
}
}
/// <summary>
/// 得到一个字符串路径中包含文件名称
/// </summary>
/// <param name="path"></param>
/// <returns></returns>
public string GetPathName(string path)
{
string target = "\\";
string target1 = ".";
char[] anyof = target.ToCharArray();

char[] anyof1 = target1.ToCharArray();
int at = path.LastIndexOfAny(anyof);
int at2 = path.LastIndexOfAny(anyof1);

string Name = path.Substring(at+1, at2-at-1);
return Name;
}
public static string GetPathNameTwoPoint(string path)
{
string target = "\\";
string target1 = ".";
char[] anyof = target.ToCharArray();
char[] anyof1 = target1.ToCharArray();
int at = path.LastIndexOfAny(anyof);
int at2 = path.IndexOfAny(anyof1);
string Name = path.Substring(at + 1, at2 - at - 1);
return Name;
}
private void button8_Click(object sender, EventArgs e)
{

ofdDialog.Title="选择备份文件";
ofdDialog.InitialDirectory = @"c:\";
ofdDialog.Filter = "备份文件(*.bak)|*.bak";
ofdDialog.FileName = "";
ofdDialog.RestoreDirectory = true;
if (this.ofdDialog.ShowDialog() == DialogResult.OK)
{
this.txtBfName.Text = this.ofdDialog.SafeFileName.ToString();
string target = "\\";
char[] anyof = target.ToCharArray();
int at = this.ofdDialog.FileName.ToString().LastIndexOfAny(anyof);
this.txtBfPath.Text = this.ofdDialog.FileName.ToString().Remove(at) + "\\";
}
}
private void button5_Click_1(object sender, EventArgs e)
{
if (this.fbdDialog.ShowDialog() == DialogResult.OK)
{
this.txtBfPath1.Text = this.fbdDialog.SelectedPath.ToString()+"\\";
MessageBox.Show(txtBfPath1.Text);
}
}
}
}
</div>
试试其它关键字
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)