SqlServer自动备份，分离，附加，删除小工具
using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Linq;using System.Text;using System.Threading.Tasks;using System.Windows.Forms;using System.IO;using System.Data.Sql;using System.Collections;using System.Runtime.InteropServices;using System.Diagnostics;using Microsoft.Win32;using System.Data.SqlClient;using System.Threading;namespace SQL_1._1{ public partial class Form1 : Form { SQL_1._1.cls_RAR cls = new cls_RAR(); //获取数据库连接字符串 /// <summary> /// 获取数据库连接字符串 /// </summary> public string ConnectionString = ""; public Form1() { InitializeComponent(); this.timer1.Enabled = true; this.timer3.Enabled = true; this.LoadSQLIPName(); //获取本地网络内所有的SQL服务器名称 } #region 连接SQL服务器>>>>> //登录SQL服务器 private void btnLoadSQLIP_Click(object sender, EventArgs e) { //判断用户名称是否为空 if (string.IsNullOrEmpty(this.tbUserName.Text.Trim())) { this.tbUserName.Focus(); //获取焦点 MessageBox.Show("登录SQL服务器的用户名称不能为空！", "登录提示"); return; } //判断用户密码是否为空 if (string.IsNullOrEmpty(this.tbPassWord.Text.Trim())) { this.tbPassWord.Focus(); //获取焦点 MessageBox.Show("登录SQL服务器的用户密码不能为空！", "登录提示"); return; } //测试提示信息 MessageBox.Show(this.TestDBConnection("Server=" this.cmbSQLIPName.Text.Trim() ";Database=" "master" ";Uid=" this.tbUserName.Text.Trim() ";Pwd=" this.tbPassWord.Text.Trim() ";"), "登录提示", MessageBoxButtons.OK, MessageBoxIcon.Information); btnLoadSQLIP.Enabled = true; } private void 退出XToolStripMenuItem_Click(object sender, EventArgs e) { Application.Exit(); } //测试数据库连接是否成功 /// <summary> /// 测试数据库连接是否成功 /// </summary> /// <param name="ConnectionString">数据库连接字符串</param> /// <returns>返回（是否已经连接成功）</returns> public string TestDBConnection(string ConnectionString) { try { SqlConnection DBconnection = new SqlConnection(ConnectionString); //建立数据库连接 DBconnection.Open(); //打开数据库连接 if (DBconnection.State.ToString() == "Open") //判断连接的状态 { this.toolStripDBName.Text = ""; this.toolStripDBName.Text = "登录SQL服务器名称：" this.cmbSQLIPName.Text.Trim() " 登录成功！"; this.BtnCmbTextTrue(); //所有内容可用 this.ConnectionString = ConnectionString; this.LoadSQLIPDBName(); //加载当前SQL服务器中的所有数据库名称 DBconnection.Close(); DBconnection.Dispose(); return "数据库连接成功！"; } else { return "数据库连接失败！"; } } catch (Exception ex) { this.toolStripDBName.Text = ""; this.toolStripDBName.Text = "登录SQL服务器名称： 登录失败！"; return "数据库连接失败！：\r\n" ex.Message; //返回连接失败错误信息 } } #endregion #region 控件可用与库名>>>>> public void BtnCmbTextTrue() { //附加数据库操作 this.btnSelectMDF.Enabled = true; //MDF浏览按钮可用 this.tbSelectMDFfile.Enabled = true; //MDF路径文本框可用 this.btnSelectLDF.Enabled = true; //LDF浏览按钮可用 this.tbSelectLDFfile.Enabled = true; //LDF路径文本框可用 this.tbSelectSQLName.Enabled = true; //设置数据库名称可用 //还原数据库操作 this.tbSQLRestDBName.Enabled = true; //获取数据库存名称可用 this.btnSQLrestFile.Enabled = true; //还原数据库文件可用 this.btnSaveNowSQL.Enabled = true; //保存还原数据库的目标路径可用 this.tbNowSQLDBPath.Enabled = true; //还原数据库的目标路径可用 this.tbSQLrestPath.Enabled = true; //还原数据库路径可用 btnSQLrestDB.Enabled = true; //分离或删除数据库操作 this.cmbIPSQLdetName.Enabled = true; //获取数据库名称可用 this.btnDetSQLdb.Enabled = true; //分离数据库可用 this.cmbIPSQLdelName.Enabled = true; //获取数据库名称可用 this.btnDelSQLdb.Enabled = true; //删除数据库可用 //备份数据库操作 this.toolStripButton1.Enabled = true; //手动备份 this.btnSQLbak.Enabled = true; //保存备份配置 this.button3.Enabled = true; //保存路径 this.cmbIPSQLbakName.Enabled = true; //获取数据库名称可用 cmbIPSQLbakName.Enabled = true; //获取数据库名称可用 } //加载当前本地网络内部的所有SQL服务器名称 /// <summary> /// 加载当前本地网络内部的所有SQL服务器名称 /// </summary> private void LoadSQLIPName() { DataTable SQLDataTable = new DataTable(); SqlDataSourceEnumerator sqlDataIP = SqlDataSourceEnumerator.Instance; //列举本地网络内所有的SQL服务器名称 SQLDataTable = sqlDataIP.GetDataSources(); foreach (System.Data.DataRow sqlDataRow in SQLDataTable.Rows) { this.cmbSQLIPName.Items.Add(sqlDataRow[0].ToString()); //添加服务器名称 } if (this.cmbSQLIPName.Items.Count > 0) { this.cmbSQLIPName.SelectedIndex = 0; } SQLDataTable.Dispose(); } //获取当前登录的SQL服务器中所有的数据库名称 /// <summary> /// 获取当前登录的SQL服务器中所有的数据库名称 /// </summary> public void LoadSQLIPDBName() { using (SqlConnection DBconnection = new SqlConnection(this.ConnectionString)) { DataTable sqlDataTable = new DataTable(); SqlDataAdapter sqlDataAdapter = new SqlDataAdapter("select name from sysdatabases", DBconnection); sqlDataAdapter.Fill(sqlDataTable); this.cmbIPSQLbakName.DataSource = sqlDataTable.DefaultView; //加载备份数据库名称 this.cmbIPSQLbakName.DisplayMember = "name"; this.cmbIPSQLbakName.ValueMember = "name"; } using (SqlConnection DBconnection = new SqlConnection(this.ConnectionString)) { DataTable sqlDataTable = new DataTable(); SqlDataAdapter sqlDataAdapter = new SqlDataAdapter("select name from sysdatabases", DBconnection); sqlDataAdapter.Fill(sqlDataTable); this.cmbIPSQLdetName.DataSource = sqlDataTable.DefaultView; //加载分离数据库名称 this.cmbIPSQLdetName.DisplayMember = "name"; this.cmbIPSQLdetName.ValueMember = "name"; } using (SqlConnection DBconnection = new SqlConnection(this.ConnectionString)) { DataTable sqlDataTable = new DataTable(); SqlDataAdapter sqlDataAdapter = new SqlDataAdapter("select name from sysdatabases", DBconnection); sqlDataAdapter.Fill(sqlDataTable); this.cmbIPSQLdelName.DataSource = sqlDataTable.DefaultView; //加载删除数据库名称 this.cmbIPSQLdelName.DisplayMember = "name"; this.cmbIPSQLdelName.ValueMember = "name"; } } #endregion #region 菜单栏代码和时间控制代码>>>>> private void 重新启动RToolStripMenuItem_Click(object sender, EventArgs e) { this.Dispose(); Application.Restart(); } private void 登录SQL服务器LToolStripMenuItem_Click(object sender, EventArgs e) { this.tabSQLManage.SelectedIndex = 0; //选择登录SQL服务器页面 } private void timer1_Tick(object sender, EventArgs e) { this.toolStripSystemTime.Text = "当前时间是：" DateTime.Now.ToString(); } private void timer2_Elapsed(object sender, System.Timers.ElapsedEventArgs e) { string time = DateTime.Now.ToString("HH:mm"); if (dateTimePicker1.Text == time) { toolStripButton1_Click(sender, e); } } #endregion #region 附加数据库文件>>>>> private void btnSelectMDF_Click(object sender, EventArgs e) { this.openFileDBName.Filter = "数据库文件(*.MDF)|*.mdf"; this.openFileDBName.FileName = "SQL数据库文件"; if (this.openFileDBName.ShowDialog() == DialogResult.OK) { this.tbSelectMDFfile.Text = this.openFileDBName.FileName; //获取需要附加数据库的路径 string DatabaseName = this.openFileDBName.FileName.Substring(this.openFileDBName.FileName.LastIndexOf("\\") 1); //获取打开的数据库文件，包括扩展名 this.tbSelectSQLName.Text = Path.GetFileNameWithoutExtension(Convert.ToString(DatabaseName)); //获取数据库名称，不带扩展名 this.AutoLoadSQLDBldf(this.openFileDBName.FileName.Substring(0, this.openFileDBName.FileName.LastIndexOf("\\")), this.tbSelectSQLName.Text.Trim()); //自动加载LDF文件 this.btnSQLAppend.Enabled = true; //附加数据库可用 } } //自动加载当前目录下的所有LDF文件，然后与选择的MDF文件相比较 /// <summary> /// 自动加载当前目录下的所有LDF文件，然后与选择的MDF文件相比较 /// </summary> public void AutoLoadSQLDBldf(string sqlLdfPath, string SQLdbName) { //创建目录对象 DirectoryInfo dir = new DirectoryInfo(sqlLdfPath); //获取目录下的所有文件 FileSystemInfo[] infos = dir.GetFileSystemInfos(); //遍历文件集合，将所有的文件用listbox显示 foreach (FileSystemInfo myfile in infos) { if (myfile.Extension.ToLower() == ".ldf") { if (myfile.ToString().StartsWith(SQLdbName)) { this.tbSelectLDFfile.Text = sqlLdfPath "\\" myfile.ToString(); //添加LDF文件类型 } } } } //附加数据文件 /// <summary> /// 附加数据文件 /// </summary> private void SQLDataBaseAppend() { using (SqlConnection sqlConnection = new SqlConnection(this.ConnectionString)) { try { SqlCommand sqlCommand = new SqlCommand(); sqlConnection.Open(); sqlCommand.Connection = sqlConnection; StringBuilder stringBuilder = new StringBuilder(); stringBuilder.Append("sp_attach_db @dbname='" this.tbSelectSQLName.Text.ToString().Trim() "',"); //附加数据库的名称 stringBuilder.Append("@filename1='" this.tbSelectMDFfile.Text.ToString().Trim() "'"); //附加数据库的MDF文件 if (this.tbSelectLDFfile.Text != "") { stringBuilder.Append(",@filename2='" this.tbSelectLDFfile.Text.Trim() "'"); //附加数据库的LDF文件 } sqlCommand.CommandText = stringBuilder.ToString(); sqlCommand.ExecuteNonQuery(); MessageBox.Show("数据库文件【" this.tbSelectSQLName.Text "】附加成功！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information); this.btnSQLAppend.Enabled = false; sqlConnection.Close(); sqlConnection.Dispose(); sqlCommand.Dispose(); stringBuilder.Clear(); } catch (Exception ex) { MessageBox.Show(ex.Message, "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information); } } } private void btnSelectLDF_Click(object sender, EventArgs e) { this.openFileDBName.Filter = "数据库文件(*.LDF)|*.ldf"; this.openFileDBName.FileName = "SQL数据库文件"; if (this.openFileDBName.ShowDialog() == DialogResult.OK) { this.tbSelectLDFfile.Text = this.openFileDBName.FileName; //获取需要附加数据库的路径 } } private void btnSQLAppend_Click(object sender, EventArgs e) { timer3.Enabled = false; if (this.tbSelectSQLName.Text != "") { this.SQLDataBaseAppend(); //附加数据库文件 } else { MessageBox.Show("请输入数据库名称", "提示信息", MessageBoxButtons.OKCancel, MessageBoxIcon.Error); } this.LoadSQLIPDBName(); //加载当前SQL服务器中的所有数据库名称 timer3.Enabled = true; } #endregion #region 分离/删除数据库文件>>>>> private void btnDetSQLdb_Click(object sender, EventArgs e) { timer3.Enabled = false; using (SqlConnection sqlConnection = new SqlConnection(this.ConnectionString)) { try { SqlCommand sqlCommand = new SqlCommand(); sqlConnection.Open(); sqlCommand.Connection = sqlConnection; sqlCommand.CommandText = "sp_detach_db @dbname='" this.cmbIPSQLdetName.Text.Trim() "'"; sqlCommand.ExecuteNonQuery(); MessageBox.Show("数据库文件【" this.cmbIPSQLdetName.Text.Trim() "】分离成功！", "提示信息", MessageBoxButtons.OKCancel, MessageBoxIcon.Information); sqlConnection.Close(); sqlConnection.Dispose(); sqlCommand.Dispose(); timer3.Enabled = true; } catch (Exception ey) { MessageBox.Show(ey.Message); } } this.LoadSQLIPDBName(); //加载当前SQL服务器中的所有数据库名称 } private void btnDelSQLdb_Click(object sender, EventArgs e) { timer3.Enabled = false; using (SqlConnection sqlConnection = new SqlConnection(this.ConnectionString)) { try { SqlCommand sqlCommand = new SqlCommand(); sqlConnection.Open(); sqlCommand.Connection = sqlConnection; sqlCommand.CommandText = "Drop DataBase " this.cmbIPSQLdelName.Text.Trim(); sqlCommand.ExecuteNonQuery(); MessageBox.Show("数据库文件【" this.cmbIPSQLdelName.Text.Trim() "】删除成功！", "提示信息", MessageBoxButtons.OKCancel, MessageBoxIcon.Information); sqlConnection.Close(); sqlConnection.Dispose(); sqlCommand.Dispose(); timer3.Enabled = true; } catch (Exception ey) { MessageBox.Show(ey.Message); } } this.LoadSQLIPDBName(); //加载当前SQL服务器中的所有数据库名称 } #endregion、 #region 还原数据库文件>>>>> //还原的路径 private void btnSQLrestFile_Click(object sender, EventArgs e) { this.openFileDBName.Filter = "数据库备份文件(*.BAK)|*.bak"; this.openFileDBName.FileName = "SQL备份数据库文件"; if (this.openFileDBName.ShowDialog() == DialogResult.OK) { this.tbSQLrestPath.Text = this.openFileDBName.FileName; string RestSQLdbName = this.tbSQLrestPath.Text.Trim().Substring(this.tbSQLrestPath.Text.Trim().LastIndexOf("\\") 1);//返回当前选择的备份文件名称含有扩展名称 } } private void btnSaveNowSQL_Click(object sender, EventArgs e) { if (this.folderFileDBbakName.ShowDialog() == DialogResult.OK) { this.tbNowSQLDBPath.Text = this.folderFileDBbakName.SelectedPath.ToString(); this.btnSQLrestDB.Enabled = true; //还原数据库可用 } } private void btnSQLrestDB_Click(object sender, EventArgs e) { timer3.Enabled = false; if (string.IsNullOrEmpty(this.btnSQLrestDB.Text.Trim())) { this.btnSQLrestDB.Focus(); //获取焦点 MessageBox.Show("还原数据库名称不能为空！", "登录提示"); return; } else this.SQLDataBaseRest(this.tbSQLrestPath.Text.Trim(), this.tbSQLRestDBName.Text.Trim(), this.tbNowSQLDBPath.Text.Trim()); //还原数据库文件 this.LoadSQLIPDBName(); //加载当前SQL服务器中的所有数据库名称 timer3.Enabled = true; } //还原数据库文件 /// <summary> /// 指定还原数据库文件 /// </summary> /// <param name="sqlDBpath">当前选择备份SQL数据库的路径</param> /// <param name="sqlRestDBname">当前选择的SQL数据库名称</param> /// <param name="sqlDBNowpath">当前选择的SQL数据库名称存放的目标文件路径</param> private void SQLDataBaseRest(string sqlDBpath, string sqlRestDBname, string sqlDBNowpath) { using (SqlConnection sqlConnection = new SqlConnection(this.ConnectionString)) { try { //-------------------关闭所有连接的数据库的进程---------------------------------- sqlConnection.Open(); //打开数据库连接 string sqlConString = "SELECT spid FROM sysprocesses ,sysdatabases WHERE sysprocesses.dbid=sysdatabases.dbid AND sysdatabases.Name='pubs'"; SqlCommand sqlCommand = new SqlCommand(sqlConString, sqlConnection); SqlDataReader sqlDataReader; //定义数据阅读器 ArrayList arrayList = new ArrayList(); //定义动态数组 try { sqlDataReader = sqlCommand.ExecuteReader(); //获取数据阅读器 while (sqlDataReader.Read()) { arrayList.Add(sqlDataReader.GetInt16(0)); } sqlDataReader.Close(); //关闭阅读器 } catch (SqlException ex) { MessageBox.Show(ex.Message); //返回数据库操作错误信息 } finally { sqlConnection.Close(); sqlConnection.Dispose(); } for (int i = 0; i < arrayList.Count; i ) { sqlCommand = new SqlCommand(string.Format("KILL {0}", arrayList[i].ToString()), sqlConnection); //关闭所有数据库进程 sqlCommand.ExecuteNonQuery(); } //-----------------------------还原数据库文件----------------------------------------------------------- //还原数据库时这里一定要是master数据库，而不能是要还原的数据库，因为这样便变成了有其它进程占用了数据库 SqlConnection sqlConnectionRest = new SqlConnection(this.ConnectionString); string SQLBackUpDBString = string.Format("RESTORE DATABASE {0} FROM DISK = '{1}' with move '{0}' to '{2}\\{0}.mdf',move '{0}_log' to '{2}\\{0}_log.ldf'", sqlRestDBname, sqlDBpath, sqlDBNowpath); //设置还原数据库的名称和到指定目录中路径 SqlCommand sqlCommandRest = new SqlCommand(SQLBackUpDBString, sqlConnectionRest); sqlConnectionRest.Open(); //打开数据库连接 try { sqlCommandRest.ExecuteNonQuery(); //执行SQL命令语句 MessageBox.Show("【" sqlRestDBname "】数据库还原成功！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information); this.btnSQLrestDB.Enabled = false; //还原按钮不可用 } catch (SqlException ex) { MessageBox.Show(ex.Message); //返回数据库操作错误信息 } finally { sqlCommandRest.Dispose(); sqlConnectionRest.Close(); sqlConnectionRest.Dispose(); sqlCommand.Dispose(); arrayList.Clear(); } } catch (Exception ex) { MessageBox.Show(ex.Message); } } } #endregion #region 备份数据库文件>>>>> /// <summary> /// 选择备份目录 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void button3_Click(object sender, EventArgs e) { FolderBrowserDialog dialog = new FolderBrowserDialog(); dialog.Description = "请选择备份路径"; if (dialog.ShowDialog() == DialogResult.OK) { this.tbSQLbakFilePath.Text = dialog.SelectedPath; } else { return; } dialog.Dispose(); } /// <summary> /// 手动备份 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void toolStripButton1_Click(object sender, EventArgs e) { timer3.Enabled = false; SQLDataBaseBackup(); timer3.Enabled = true; } //备份数据库文件 ///<summary> ///备份数据库文件 ///</summary> public void SQLDataBaseBackup() { string pth = tbSQLbakFilePath.Text.ToString() @"\" this.cmbIPSQLbakName.Text @"\"; if (!Directory.Exists(pth)) { Directory.CreateDirectory(pth); } using (SqlConnection sqlConnection = new SqlConnection(this.ConnectionString)) { try { SqlCommand sqlCommand = new SqlCommand(); sqlConnection.Open(); if (this.tbSQLbakFilePath.Text != "" ) { sqlCommand.Connection = sqlConnection; sqlCommand.CommandText = "BACKUP DATABASE " this.cmbIPSQLbakName.Text.Trim() " TO DISK = '" pth.Trim() "\\" this.cmbIPSQLbakName.Text.Trim() DateTime.Now.ToString("yyyyMMddHHmmss") ".bak'"; if (sqlCommand.ExecuteNonQuery()== 0) { MessageBox.Show("数据库文件【" this.cmbIPSQLbakName.Text.Trim() "】备份失败！", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);return; } } else { MessageBox.Show("请填写备份的正确位置！", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } sqlConnection.Close(); sqlConnection.Dispose(); sqlCommand.Dispose(); } catch (Exception ex) { MessageBox.Show(ex.Message.ToString(), "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } } cls.CompressRar(pth.ToString(), tbSQLbakFilePath.Text.ToString(), this.cmbIPSQLbakName.Text DateTime.Now.ToString("yyyyMMddHHmmss")); //删除文件夹 DirectoryInfo di = new DirectoryInfo(pth); di.Delete(true); } private void btnSQLbak_Click_1(object sender, EventArgs e) { if (cmbIPSQLbakName.Text != "" && tbSQLbakFilePath.Text != "") { timer2.Start(); timer2.Enabled = true;//是否执行System.Timers.Timer.Elapsed事件； button1.Enabled = true; btnSQLbak.Enabled = false; dateTimePicker1.Enabled = false; button3.Enabled = false; tbSQLbakFilePath.Enabled = false; cmbIPSQLbakName.Enabled = false;} else { MessageBox.Show("数据库名或者保存路径没有选择", "消息", MessageBoxButtons.OK, MessageBoxIcon.Warning); } } private void button1_Click(object sender, EventArgs e) { this.button1.Enabled = false; this.btnSQLbak.Enabled = true; timer2.Enabled = false; timer2.Stop(); dateTimePicker1.Enabled = true; button3.Enabled = true; tbSQLbakFilePath.Enabled = true; cmbIPSQLbakName.Enabled = true; } #endregion #region 托盘>>>>> private void 退出ToolStripMenuItem_Click(object sender, EventArgs e) { Application.Exit(); this.Dispose(); notifyIcon1.Dispose(); } //托盘 private void Form1_FormClosing(object sender, FormClosingEventArgs e) { e.Cancel = true; this.Hide(); } private void notifyIcon1_MouseDoubleClick(object sender, MouseEventArgs e) { if (this.WindowState == FormWindowState.Normal) { this.Show(); } this.WindowState = FormWindowState.Normal; this.Activate(); } #endregion #region 内存回收>>>> [DllImport("kernel32.dll", EntryPoint = "SetProcessWorkingSetSize")] public static extern int SetProcessWorkingSetSize(IntPtr process, int minSize, int maxSize); /// <summary> /// 释放内存 /// </summary> public static void ClearMemory() { GC.Collect(); GC.WaitForPendingFinalizers(); if (Environment.OSVersion.Platform == PlatformID.Win32NT) { SetProcessWorkingSetSize(System.Diagnostics.Process.GetCurrentProcess().Handle, -1, -1); } } /// <summary> /// 获取应用内存 /// </summary> /// <returns></returns> public static float getMem() { using (PerformanceCounter pc = new PerformanceCounter("Process", "Working Set - Private", Process.GetCurrentProcess().ProcessName)) { float r = (pc.NextValue() / 1024 / 1024); return r; } } private void Form1_Load(object sender, EventArgs e) { ClearMemory(); getMem(); } private void timer3_Tick_1(object sender, EventArgs e) { timer3.Enabled = true; ClearMemory(); getMem(); } #endregion }}
评论