在线还原数据库备份文件

发布时间:2017-2-22 21:21:37 编辑:www.fx114.net 分享查询网我要评论
本篇文章主要介绍了"在线还原数据库备份文件 ",主要涉及到在线还原数据库备份文件 方面的内容,对于在线还原数据库备份文件 感兴趣的同学可以参考一下。

需求

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

上一篇:Redis word bak
下一篇:AE 线编辑

相关文章

相关评论