描述:
最近写项目收到了一个需求, 就是将 SQL Server 数据库备份为 Sql 脚本, 如果是 My Sql 之类的还好说, 但是在网上搜了一大堆, 全是教你怎么操作 SSMS 的, 就很 d 疼!
解决方案:
通过各种查找资料, 还有一些老哥的帮助, 找到了解决方案:
通过 Microsoft.SqlServer.Management.Smo, Microsoft.SqlServer.Management.Sdk.Sfc, Microsoft.SqlServer.Management.Common 来解决, 但是不巧的是, 这个方法可能只适用于.Net Framework, 并且微软已经提供一个合集的类库封装为 Microsoft.SqlServer.Scripts. 但是我是一个 Net5 的项目!
但是最后还是找到了, 微软封装了一个其它包...emmMicrosoft.SqlServer.SqlManagementObjects, 此类库可以适用于 Net Core.
基本使用
Server server = new Server( new ServerConnection( // 服务器IP _dbBackupOptions.ServerInstance, // 登录名 _dbBackupOptions.Username, // 密码 _dbBackupOptions.Password ));// 获取数据库Database templateDb = server.Databases[_dbBackupOptions.DatabaseName];// 脚本导出路径string sqlFilePath = string.Format("{0}.sql", $"{dbBackupPath}/{name}");// 自定义规则var startWith = _dbBackupOptions.FormatTables.Where(x => x.EndsWith("*")).Select(x => x.TrimEnd('*'));var endWith = _dbBackupOptions.FormatTables.Where(x => x.StartsWith("*")).Select(x => x.TrimStart('*'));
if (_dbBackupOptions.FormatTables is not null && _dbBackupOptions.FormatTables.Any()){ foreach (Table tb in templateDb.Tables) { if (_dbBackupOptions.FormatTables.Contains(tb.Name) || startWith.Where(x => tb.Name.StartsWith(x)).Any() || endWith.Where(x => tb.Name.EndsWith(x)).Any()) { // 按表获取Sql IEnumerable<string> sqlStrs = tb.EnumScript(_dbBackupOptions.ScriptingOptions); // 将Sql向文件中追加 using (StreamWriter sw = new StreamWriter(sqlFilePath, true, Encoding.UTF8)) { foreach (var sql in sqlStrs) { sw.WriteLine(sql); sw.WriteLine("GO"); } } } }}else{ foreach (Table tb in templateDb.Tables) { IEnumerable<string> sqlStrs = tb.EnumScript(_dbBackupOptions.ScriptingOptions); using (StreamWriter sw = new StreamWriter(sqlFilePath, true, Encoding.UTF8)) { foreach (var sql in sqlStrs) { sw.WriteLine(sql); sw.WriteLine("GO"); } } }}
复制代码
开箱即用(封装库 Powers.DbBackup)
我针对这个封装了一个类库, Powers.DBackup 方便简单使用.
GitHub 地址: Powers.DbBackup
配置 DbBackup
1.In Startup.cs(Net5):
appsettings.json:
"DbBackupOptions": { // remote server "ServerInstance": "192.168.31.36", // database username "Username": "sa", // password "Password": "sa123.", // ddatabase name "DatabaseName": "PumInfoShop", // output options "ScriptingOptions": { "DriAll": false, "ScriptSchema": true, "ScriptData": true, "ScriptDrops": false }, // match rules /** * Include 3 rules: * 1. Full name: UserTable * 2. Start with: Sys* * 3. End with: *Table */ "FormatTables": [] }
复制代码
OR
services.AddDbBackup(opts =>{ opts.ServerInstance = "127.0.0.1"; opts.Username = "sa"; opts.Password = "123456"; opts.DatabaseName = "TestDb"; opts.ScriptingOptions = new ScriptingOptions { DriAll = true, ScriptSchema = true, ScriptData = true, ScriptDrops = false }; /** * Include 3 rules: * 1. Full name: UserTable * 2. Start with: Sys* * 3. End with: *Table */ opts.FormatTables = new string[] { "Sys*", "Log*", "UserTable", "*Table" };});// Or this way//services.AddDbBackup(opts => new DbBackupOptions//{// ServerInstance = "127.0.0.1",// Username = "sa",// // .....//});
复制代码
2. In Program.cs(Net6):
builder.Services.AddDbBackup();
复制代码
appsettings.json:
"DbBackupOptions": { "ServerInstance": "192.168.31.36", "Username": "sa", "Password": "sa123.", "DatabaseName": "PumInfoShop", "ScriptingOptions": { "DriAll": false, "ScriptSchema": true, "ScriptData": true, "ScriptDrops": false }, "FormatTables": [] }
复制代码
OR
builder.Services.AddDbBackup(opts =>{ opts.ServerInstance = "127.0.0.1"; opts.Username = "sa"; opts.Password = "123456"; opts.DatabaseName = "TestDb"; opts.ScriptingOptions = new ScriptingOptions { DriAll = true, ScriptSchema = true, ScriptData = true, ScriptDrops = false }; /** * Include 3 rules: * 1. Full name: UserTable * 2. Start with: Sys* * 3. End with: *Table */ opts.FormatTables = new string[] { "Sys*", "Log*", "UserTable", "*Table" };});
// Or this way//builder.Services.AddDbBackup(opts => new DbBackupOptions//{// ServerInstance = "127.0.0.1",// Username = "sa",// // .....//});
复制代码
使用方法
[HttpGet]public async Task<ActionResult> StartDbBackup(){ var rootPath = "D:/"; var fileName = DateTime.Now.ToString("yyyyMMddhhmmss"); // No ".sql" suffix is required. var (path, size) = await DbBackupExtensions.StartBackupAsync(rootPath, fileName);// path is full path
return Ok(new { Path = path, Size = size });}
[HttpGet]public async Task<ActionResult> DeleteDbBackup(string filePath){ var (res, msg) = await DbBackupExtensions.DeleteBackup(filePath);
if (res) { return Ok(msg); } else { return NotFound(msg); }}
复制代码
评论