[SQLServer]NetCore中将SQLServer数据库备份为Sql脚本

NetCore中将SQLServer数据库备份为Sql脚本

描述:

最近写项目收到了一个需求, 就是将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.

• NetCore中将SQLServer数据库备份为Sql脚本
  • 基本使用
  • 开箱即用(封装库Powers.DbBackup)
    • 配置DbBackup
      • 1. In Startup.cs(Net5):
      • 2. In Program.cs(Net6):
    • 使用方法

By: 胖纸不争
NetCore??群: 743336452

基本使用

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):

services.AddDbBackup();

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