• [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.

    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);
        }
    }
    
  • 相关阅读:
    Linux su命令——su默认不修改环境变量 su
    setuid 粘滞位 ——想想passwd这个命令修改shadow文件就知道本质 当普通用户使用passwd更改自己密码的时候,那一瞬间突然灵魂附体了,实际在以passwd命令所有者root的身份在执行
    Control-Flow Integrity(控制流完整性) 的原理 ——本质上就是一个hash表记录持续返回地址 然后运行中对比 发现是否代码被恶意篡改
    内核中do while(0)的巧用 避免goto的方法 linux内核中代码有这样的代码
    Tensorflow 损失函数(loss function)及自定义损失函数(三)
    深度学习最全优化方法总结比较及在tensorflow实现
    深度学习剖根问底: Adam优化算法的由来
    深度排序模型概述(一)Wide&Deep/xDeepFM
    机器学习排序算法:RankNet to LambdaRank to LambdaMART
    主题模型TopicModel:主题模型LDA的应用
  • 原文地址:https://www.cnblogs.com/donpangpang/p/16295417.html
Copyright © 2020-2023  润新知