SQL数据据分离,附加,备份,还原工具

原创 2011年01月23日 22:50:00

前几天没事,写了一个数据库操作的一个小工具,源码和大家分享,有问题的地方请大家多多指出!

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);
            }
        }
    }
}

SQL Server 2005的备份、还原及分离、附加基本操作

1.备份 连接好数据库引擎后,我本地有2个实例,分别是sqlserver、sqltest,如图(1-1)。 (图1-1) 以备份sqlserver为例,右击->任务->备份,如图(1-2)。 ...
  • kanglongyao
  • kanglongyao
  • 2016年04月09日 23:17
  • 4314

SQL数据库的分离附加,导出脚本,备份和还原

一、数据库的分离 方法一:1、打开对象资源管理器,右击要分离的数据库,选择任务,分离 2、分离之前还要注意保证该分离的数据库没有当前活动的表,否则会报错,像下面这个。如果关闭了活动表还有的话就...
  • IstarI
  • IstarI
  • 2015年08月16日 16:17
  • 2139

【数据库】数据库的bak格式备份与还原

这些天忙于处理评教的事情,其中接触了很多的新的知识。话说数据库的备份已经不是很新鲜了,但是感觉自己很有必要去总结一下,以便以后自己查看,或者是给新的朋友一点点启示。 在开始之前有几个问题? 1、什...
  • kisscatforever
  • kisscatforever
  • 2015年12月08日 08:26
  • 3614

SQL Server 2005数据库的附加和分离 还原和备份

  • 2011年08月02日 10:04
  • 1.17MB
  • 下载

SQL 数据库 学习 005 学习必备的一些操作 --- 如何新建数据库 如何附加和分离数据库(如何备份还原数据库) 如何删除数据库

我的电脑系统: Windows 10 64位 使用的SQL Server软件: SQL Server 2014 Express如果我们要学习这个数据库,我们需要学习什么知识。比如:如何新建一个数据库...
  • github_35160620
  • github_35160620
  • 2016年09月25日 02:48
  • 13398

SQL Server 2005数据库的附加和分离/备份与还原实验指导

Sql server 2005数据库的附加和分离/备份与还原实验指导 一、启动服务器 图1 如图1,启动服务器,进入图2界面,点击连接,开始连接服务器。请注意服务器名称一项,如果后面几位数字不是实验...
  • free4294
  • free4294
  • 2011年07月30日 16:00
  • 2384

操作数据库(还原、备份、分离、附加)

  • 2012年12月16日 18:43
  • 308KB
  • 下载

.net 操作数据库附加、备份、还原和分离

  • 2010年07月27日 14:38
  • 3KB
  • 下载

一个操作数据库的类(备份,还原,分离,附加,添加删除用户等操作)

using System; using System.Collections.Generic; using System.Linq; using System.Text;...
  • fox123871
  • fox123871
  • 2011年09月19日 15:34
  • 448

sql server 2000 备份 还原 附加 数据库 操作 教程

  • 2011年03月25日 11:06
  • 660KB
  • 下载
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:SQL数据据分离,附加,备份,还原工具
举报原因:
原因补充:

(最多只允许输入30个字)