在线还原数据库备份文件
需求
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(); } } }
版权声明：本文为talentzemin原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。