C#备份数据库文件

c#备份数据库文件完整代码

sqlServer  存储过程：

USE [PSIDBase]
GO
/****** Object:  StoredProcedure [dbo].[sp_BackupDB]    Script Date: 2023/8/31 16:49:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 ALTER procedure [dbo].[sp_BackupDB]
     @savePath nvarchar(4000) -- 备份数据库保存位置(目录)   
     ,@dbName nvarchar(4000) -- 需要进行备份的数据库
     ,@bakName nvarchar(4000) -- 备份文件的名称(不含扩展名)
 as begin
     declare @sql nvarchar(4000)
     /* 验证路径 */
     if(charindex('/',reverse(@savePath))!=1) begin
         set @savePath=@savePath+'/'
     end
     /* 拼SQL并执行 */
     set @sql='backup database '+@dbName+' to disk='''+@savePath+@bakName+'.bak'''
     exec sp_executesql @sql
     
     /* 返回执行结果(1=成功,0=失败) */
     if(@@error=0) begin
         return 1
     end
     return 0
 end
 
 

App.config添加配置项

<?xml version="1.0" encoding="utf-8"?>
<configuration>
    <startup> 
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.8"/>
    </startup>
  <connectionStrings>
    <add name="connStr" connectionString="server=PC-20191008BGNB\SQLEXPRESS;database=***;uid=**;pwd=***;" providerName="System.Data.SqlClient"/>
  </connectionStrings>
	<appSettings>
		<add key="DbName" value="PSIDBase"/>
	</appSettings>
</configuration>

DAL文件：

 /// <summary>
 /// 备份数据
 /// </summary>
 /// <param name="path"></param>
 /// <param name="dbName"></param>
 /// <param name="backupName"></param>
 /// <returns></returns>
 public int BackupData(string path, string dbName, string backupName)
 {
     SqlParameter[] paras =
     {
         new SqlParameter("@savePath",path),
         new SqlParameter("@dbName",dbName),
         new SqlParameter("@bakName",backupName),
         new SqlParameter("@return",SqlDbType.Int,4)
     };
     paras[3].Direction = ParameterDirection.ReturnValue;//返回值参数
     SqlHelper.ExecuteNonQuery("sp_BackupDB", 2, paras);
     return paras[3].Value.GetInt();
 }

BLL：

/// <summary>
/// 备份数据
/// </summary>
/// <param name="path"></param>
/// <returns></returns>
public bool BackupData(string path)
{
    bool bl = false;
    string dbName = ConfigurationManager.AppSettings["DbName"].ToString();
    string backupName = dbName + DateTime.Today.ToString("yyyyMMdd");
    int reVal = sysDAL.BackupData(path, dbName, backupName);
    bl = reVal == 1 ? true : false;
    return bl;
}

窗体调用：

using PSI.BLL;
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 WinPSI.Common;
 
namespace WinPSI.SM
{
    public partial class FrmBackUpData : Form
    {
        public FrmBackUpData()
        {
            InitializeComponent();
        }
        private Verify verify = new Verify();
 
        private void btnCancel_Click(object sender, EventArgs e)
        {
            this.Close();
        }
 
        private void FrmBackUpData_Load(object sender, EventArgs e)
        {
            txtPath.Clear();
        }
        /// <summary>
        /// 选择备份位置
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnChoose_Click(object sender, EventArgs e)
        {
            FolderBrowserDialog ofd = new FolderBrowserDialog();
            if(ofd.ShowDialog()==DialogResult.OK)
            {
                txtPath.Text = ofd.SelectedPath;
            }
        }
        private void btnOK_Click(object sender, EventArgs e)
        {
            string path = txtPath.Text.Trim();
            if (string.IsNullOrEmpty(path))
            {
                MsgBoxHelper.MsgErrorShow("请选择备份文件存放的位置!");
                return;
            }
            if (MsgBoxHelper.MsgBoxConfirm("备份数据", "您确定要备份数据库吗?") == DialogResult.Yes)
            {
                SysBLL sysBLL = new SysBLL();
                bool bl = sysBLL.BackupData(path);
                if (bl)
                {
                    MsgBoxHelper.MsgBoxShow("备份数据", "系统数据备份完毕!");
                }
                else
                {
                    MsgBoxHelper.MsgErrorShow("数据备份失败!");
                    return;
                }
            }
        }
    }
}