数据库备份与还原的sql语句

//数据库备份
backup database northwind to disk='c:\dbbackup\northwind.bak';
//数据库还原
//数据库还原
use master;
alter database northwind set offline with rollback immediate 
restore database northwind from disk='c:\dbbackup\northwind.bak' 
alter database northwind set online with rollback immediate;

winform界面设计

代码

//winform界面代码：
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace SqlBackupAndRestore
{
    public partial class FrmBackup : Form
    {
        public string strConnection { get; set; }
        public FrmBackup()
        {
            InitializeComponent();
            //默认数据库名称和备份路径
            txtDBName.Text = "northwind";
            txtBackupPath.Text = @"c:\dbbackup";
            strConnection = ConfigurationManager.ConnectionStrings["sqlserver"].ConnectionString;
            GetBackupFiles(txtBackupPath.Text);
        }

        /// <summary>
        /// 选择数据库备份路径
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnSelect_Click(object sender, EventArgs e)
        {
            FolderBrowserDialog dialog = new FolderBrowserDialog();
            dialog.Description = "请选择数据库备份所在文件夹";
            if (dialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                if (string.IsNullOrEmpty(dialog.SelectedPath))
                {
                    MessageBox.Show(this, "文件夹路径不能为空", "提示");
                    return;
                }
                txtBackupPath.Text = dialog.SelectedPath;
                GetBackupFiles(txtBackupPath.Text);
            }
        }

        /// <summary>
        /// 获取所有数据库备份
        /// </summary>
        /// <param name="path"></param>
        private void GetBackupFiles(string path)
        {
            string[] files = Directory.GetFiles(path, "*.bak");
            lbBackups.Items.Clear();
            lbBackups.Items.AddRange(files);
        }

        /// <summary>
        /// 数据库备份
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnBackup_Click(object sender, EventArgs e)
        {
            //数据库名称
            if (string.IsNullOrEmpty(txtDBName.Text))
            {
                MessageBox.Show("请输入需要备份的数据库名称!","提示",MessageBoxButtons.OK,MessageBoxIcon.Warning);
                return;
            }
            if (string.IsNullOrEmpty(txtBackupPath.Text))
            {
                MessageBox.Show("请输入存放备份的目录!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }
            else if (!Directory.Exists(txtBackupPath.Text))
            {
                MessageBox.Show("路径不存在");
                return;
            }
            else
            {
                //yyyyMMddHHmmss为24小时制，yyyyMMddhhmmss为12小时制
                string backFile = txtDBName.Text + DateTime.Now.ToString("yyyyMMddHHmmss") + ".bak";
                DbBackupHelper.BackupDataBase(strConnection, txtDBName.Text, txtBackupPath.Text, backFile);
                GetBackupFiles(txtBackupPath.Text);
                MessageBox.Show("备份成功", "结果", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }

        }

        /// <summary>
        /// 数据库还原
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnRestore_Click(object sender, EventArgs e)
        {
            if (string.IsNullOrEmpty(txtDBName.Text))
            {
                MessageBox.Show("请先输入数据库名称", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
            else if (lbBackups.SelectedItem == null)
            {
                MessageBox.Show("请先选择需要还原的备份文件!");
            }
            else if (!File.Exists(lbBackups.SelectedItem.ToString()))
            {
                MessageBox.Show("备份文件不存在!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
            else
            {
                DbBackupHelper.RestoreDataBase(strConnection,txtDBName.Text, lbBackups.SelectedItem.ToString());
                MessageBox.Show("数据库还原成功！", "结果", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }
    }
}

DBBackupHelper代码：

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SqlBackupAndRestore
{
    public class DbBackupHelper
    {
        /// <summary>
        /// 还原数据库
        /// </summary>
        public static bool RestoreDataBase(string connectionString, string dataBaseName, string dataBaseBackupFile)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;

                //comm.CommandText = "use master;restore database @DataBaseName From disk = @BackupFile with replace;";
                // alter database不支持参数化语法
                comm.CommandText = "use master;alter database " + dataBaseName + " set offline with rollback immediate; restore database " + dataBaseName + " from disk='" + dataBaseBackupFile + "' with replace;alter database  " + dataBaseName + " set online with rollback immediate";
                //comm.CommandText = "use master;alter database @dataBaseName set offline with rollback immediate; restore database @dataBaseName from disk='@backupFile' with replace;alter database  @dataBaseName set online with rollback immediate";
                //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 = dataBaseBackupFile;
                comm.CommandType = CommandType.Text;
                comm.ExecuteNonQuery();
            }
            return true;
        }

        /// <summary>
        /// 备份SqlServer数据库
        /// </summary>
        public static bool BackupDataBase(string connectionString, string dataBaseName, string backupPath, string backupName)
        {
            string filePath = Path.Combine(backupPath, backupName);
            if (File.Exists(filePath))
            {
                File.Delete(filePath);
            }

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlCommand 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 = filePath;
                comm.CommandType = CommandType.Text;
                comm.ExecuteNonQuery();
            }
            return true;
        }

    }
}

参考：

1. 出现“System.Data.SqlClient.SqlError: 尚未备份数据库的日志尾部”错误的解决方案: https://www.cnblogs.com/feiyuhuo/p/5514344.html
   

1. 数据库备份错误
   这个是因为备份数据库时，应用程序还在连接数据库，需要先断开数据库
   

C# sqlserver 数据库备份与还原(winform版)

原文：https://www.cnblogs.com/AlexanderZhao/p/12459434.html