首页 > 代码库 > 在线还原数据库备份文件

在线还原数据库备份文件

2024-08-29 09:00:44   219人阅读

需求

1.用户上传数据库备份文件（.bak）还原到指定服务器上（如果用户不上传文件，则还原默认的备份文件）

2.还原文件后，创建访问该数据库的用户，登录名和密码可由用户输入（如果用户不输入登录名和密码，则生成默认的登录名和密码）

问题

按照需求，很容易想打使用restore database语句，代码实现后测试发现，重复还原一个数据库文件，会提示还原失败，正在使用该文件。

因为还原后在文件夹目录中会有相同的逻辑文件名称，所以会出现问题。

解决方案

使用restore database with move还原语句，将每次还原的文件放在不同的位置

代码

页面代码

<!DOCTYPE html>
<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>还原数据库</title>
    <link rel="stylesheet" type="text/css" href="https://cdn.staticfile.org/webuploader/0.1.5/webuploader.css" />
    <style>
        * { padding: 0; margin: 0; }
        html, body { height: 100%; font-size: 16px; font-family: ‘Microsoft YaHei‘; }
        table { width: 100%; height: 100%; }
            table td { text-align: center; }
        td > input { margin: 15px 0; padding: 5px; border: 1px solid #ccc; border-radius: 3px; width: 400px; height: 30px; font-size: 16px; font-family: ‘Microsoft YaHei‘; }
        p { line-height: 50px; color: #999; display: none; }
        .btn { display: inline-block; text-decoration: none; color: #fff; background-color: #0094ff; padding: 10px 15px; border-radius: 3px; }
            .btn:hover { opacity: 0.9; }
        .icofile { display: none; color: green; }
        .bar { width: 100%; height: 10px; background: #999; position: relative; display: none; }
        .subbar { height: 10px; background: green; position: absolute; top: 0; left: 0; }
        .webuploader-pick { border: 1px dashed #ccc; padding: 20px 10px 20px 50px; background: url(http://www.easyicon.net/api/resizeApi.php?id=1160478&size=32) no-repeat 10px center; color: #666; }
        .tips { color: #999; line-height: 40px; }
    </style>
</head>
<body>
    <table>
        <tr>
            <td>
                <div id="uploader" style="width:400px;margin:0 auto;">
                    <div class="btns">
                        <div id="picker">选择备份文件</div>
                        <div class="bar">
                            <div class="subbar"></div>
                        </div>
                        <div class="filelist"></div>
                    </div>
                </div>
                <div class="tips">
                    说明：不上传备份文件，将还原默认的数据库备份文件
                </div>
                <input type="text" placeholder="还原后数据库名称" id="name" /><br />
                <input type="text" placeholder="数据库账户，可不填写" id="account" /><br />
                <input type="password" placeholder="数据库密码，可不填写" id="pwd" /><br />
                <a href="javascript:;" id="restore" class="btn">一键还原</a><br />
                <p class="tip">还原时间与文件大小成正比，请耐心等待.....</p>
            </td>
        </tr>
    </table>

    <script src="http://apps.bdimg.com/libs/jquery/1.7.2/jquery.min.js"></script>
    <script src="https://cdn.staticfile.org/webuploader/0.1.5/webuploader.js"></script>
    <script>

        $(function () {
            //文件上传
            var uploader = WebUploader.create({
                swf: ‘https://cdn.staticfile.org/webuploader/0.1.5/Uploader.swf‘,
                auto: true,
                server: ‘/Home/Upload‘,
                pick: {
                    id: ‘#picker‘,
                    multiple: false,
                },
                resize: false,
                fileNumLimit: 1,
                duplicate: false,//去重
                fileSizeLimit: 100 * 1024 * 1024,//100M
                accept: {
                    title: ‘Bak‘,
                    extensions: ‘bak‘,
                    mimeTypes: ‘application/x-trash‘
                }
            });
            //加入队列前
            uploader.on(‘beforeFileQueued‘, function (file) {
                var files = uploader.getFiles();
                files.forEach(function (item) {
                    uploader.removeFile(item, true);
                });
            });
            //加入队列时
            uploader.on(‘fileQueued‘, function (file) {
                $(".filelist").html("文件：" + file.name);
            });
            //进度条
            uploader.on(‘uploadProgress‘, function (file, percentage) {
                $(".bar").show();
                $(".subbar").css("width", percentage * 100 + "%");
            });
            //成功
            uploader.on(‘uploadSuccess‘, function (file, response) {
                $(".bar").fadeOut(2000);
                if (response.message != "OK") {
                    alert(response.message);
                }
            });
            //失败
            uploader.on(‘uploadError‘, function (file, reason) {
                alert(reason);
            });
            //完成
            uploader.on(‘uploadComplete‘, function (file) {
            });
            //验证
            uploader.on(‘error‘, function (msg) {
                if (msg == "Q_EXCEED_NUM_LIMIT") {
                    alert("最多选择一个文件上传");
                }
                else if (msg == "Q_EXCEED_SIZE_LIMIT") {
                    alert("文件最大不能超过100M");
                }
                else if (msg == "Q_TYPE_DENIED") {
                    alert("文件类型必须是BAK文件");
                }
                else if (msg == "F_DUPLICATE") {
                    alert("队列中有同名文件了");
                }
                else {
                    alert("未知错误：" + msg + "，请联系客服");
                }
            });


            //还原数据库文件
            $("#restore").click(function () {
                var valid = true;
                if ($("#name").val() == "") {
                    valid = false;
                    return false;
                }

                if (valid) {
                    $(this).hide();
                    $(".tip").show();

                    $.post("/Home/Restore",
                        {
                            name: $("#name").val(),
                            account: $("#account").val(),
                            pwd: $("#pwd").val(),
                            isRestoreDefault: uploader.getFiles().length == 0
                        },
                        function (data) {
                            alert(data.msg);
                            window.location.reload();
                        });
                }
            });
        });
    </script>
</body>
</html>

后台代码

 [HttpPost]
        public JsonResult Restore(string name, string account, string pwd, bool isRestoreDefault)
        {
            using (SqlConnection connection = new SqlConnection("Data Source=.;uid=" + Config("DBUser") + ";pwd=" + Config("DBPwd") + ";database=master;timeout=180"))
            {
                try
                {
                    connection.Open();
                    string dbNewUserName = name + "User";
                    string dbNewUserPwd = name + "User!@#";
                    if (!string.IsNullOrEmpty(account))
                    {
                        dbNewUserName = account;
                        dbNewUserPwd = pwd;
                    }

                    //备份文件获取
                    string path = Server.MapPath("/Content/" + (isRestoreDefault ? "cms" : "temp") + ".bak");
                    List<string> logicalNameList = new List<string>();

                    //还原前，获取数据库备份文件的逻辑名称
                    string cmdText = "restore filelistonly from disk=‘" + path + "‘";
                    SqlCommand cmd = new SqlCommand(cmdText, connection);
                    cmd.CommandTimeout = Int32.MaxValue;//命令执行时间
                    SqlDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        logicalNameList.Add(reader["LogicalName"].ToString());
                    }
                    reader.Close();

                    //创建数据库
                    cmdText = @"restore database " + name + " from disk=‘" + path + "‘ with";
                    string dataPath = Server.MapPath("/RestoreData/");
                    cmdText += " move ‘" + logicalNameList[0] + "‘ to ‘" + dataPath + name + ".mdf‘,";
                    cmdText += " move ‘" + logicalNameList[1] + "‘ to ‘" + dataPath + name + ".ldf‘";
                    cmd = new SqlCommand(cmdText, connection);
                    cmd.ExecuteNonQuery();

                    //创建用户
                    cmd = new SqlCommand("create login " + dbNewUserName + " with password=‘" + dbNewUserPwd + "‘, default_database=" + name, connection);
                    cmd.ExecuteNonQuery();

                    string dbSql = string.Format(@"use {0}
                                                            create user {1} for login {1} with default_schema=dbo
                                                            exec sp_addrolemember ‘db_owner‘, ‘{1}‘
                                                            ", name, dbNewUserName, dbNewUserPwd);
                    cmd = new SqlCommand(dbSql, connection);
                    cmd.ExecuteNonQuery();

                    //删除上传的数据库文件
                    if (!isRestoreDefault)
                    {
                        System.IO.File.Delete(path);
                    }

                    return Json(new
                    {
                        msg = "还原成功"
                    });
                }
                catch (Exception e)
                {
                    return Json(new
                    {
                        msg = "还原失败：" + e.Message
                    });
                }
                finally
                {
                    connection.Close();
                }
            }
        }

 

在线还原数据库备份文件


联系
我们