c# - 如何在 .NET Core 中恢复 SQL Server 备份

Question

我想使用 .NET Core 恢复 SQL Server 数据库 (.bak)。这是我在 GitHub 上的空网站这样您就可以看到当前的配置。

在完整的 .NET Framework 中恢复数据库相当简单 - 可以看出 here .

有没有办法直接从 .NET Core 执行此操作，还是我需要引用 .NET Framework 并使用 .NET Framework 类库？

无论我如何尝试，我都无法让它工作。

编辑

我尝试添加 SQLManagementObject，但不能。我正在使用 .NET Core 2.0。

编辑 2

我们的旧项目主要是 ADO.NET。他们(广泛地)使用了以下我无法带入我的 .NET Core 项目的 DLL:

• Microsoft.SqlServer.ConnectionInfo
• 微软.SqlServer.Smo
• Microsoft.SqlServer.SmoExtended
• Microsoft.SqlServer.Management.Sdk.Sfc

Answer 1

更新:在 .Net Core 2.0 中，您可以使用 Microsoft.SqlServer.SqlManagementObjects (140.17265.0)。 SQL Server Management Objects (SMO) Framework您可以在 Windows 和 Linux 下使用 SQL SMO。

Microsoft.SqlServer.SqlManagementObjects 依赖于 System.Data.SqlClient (4.5.0)

简单的 SMO 备份示例:

            ServerConnection serverConnection = new ServerConnection("192.168.1.1", "user", "password");
            Server server = new Server(serverConnection);
            Database database = server.Databases["AdventureWorks"];
            Backup backup = new Backup();
            backup.Action = BackupActionType.Database;
            backup.BackupSetDescription = "AdventureWorks - full backup";
            backup.BackupSetName = "AdventureWorks backup";
            backup.Database = "AdventureWorks";

            BackupDeviceItem deviceItem = new BackupDeviceItem("AdventureWorks_Full_Backup.bak", DeviceType.File);
            backup.Devices.Add(deviceItem);
            backup.Incremental = false;
            backup.LogTruncation = BackupTruncateLogType.Truncate;
            backup.SqlBackup(server);

在 .NetCore 中，要备份/恢复 SQL Server 数据库，您可以使用常见的 ADO.NET SqlConnection 和 SqlCommand 对象。要自定义备份/恢复，您需要了解 T-SQL BACKUP/RESTORE 语句的语法。请咨询

RESTORE Statements (T-SQL)

BACKUP Statements (T-SQL)

using System;
using System.Data;
using System.Data.SqlClient;

namespace BackupRestore
{
    class Program
    {
        static void Main(string[] args)
        {
            BackupDatabase("test", @"C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\test.bak");
            RestoreDatabase("test", @"C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\test.bak");
        }

        private static void RestoreDatabase(string databaseName, string backupPath)
        {
            string commandText = $@"USE [master];
    ALTER DATABASE [{databaseName}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    RESTORE DATABASE [{databaseName}] FROM DISK = N'{backupPath}' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 5;
    ALTER DATABASE [{databaseName}] SET MULTI_USER;";

            SqlConnectionStringBuilder connectionStringBuilder = new SqlConnectionStringBuilder
            {
                DataSource = "localhost",
                InitialCatalog = "master",
                IntegratedSecurity = true
            };
            using (SqlConnection connection = new SqlConnection(connectionStringBuilder.ConnectionString))
            {
                connection.Open();
                connection.InfoMessage += Connection_InfoMessage;
                using (SqlCommand command = connection.CreateCommand())
                {
                    command.CommandText = commandText;
                    command.CommandType = CommandType.Text;
                    command.ExecuteNonQuery();
                }
            }
        }

        private static void BackupDatabase(string databaseName, string backupPath)
        {
            string commandText = $@"BACKUP DATABASE [{databaseName}] TO DISK = N'{backupPath}' WITH NOFORMAT, INIT, NAME = N'{databaseName}-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10";

            SqlConnectionStringBuilder connectionStringBuilder = new SqlConnectionStringBuilder
            {
                DataSource = "localhost",
                InitialCatalog = "master",
                IntegratedSecurity = true
            };
            using (SqlConnection connection = new SqlConnection(connectionStringBuilder.ConnectionString))
            {
                connection.Open();
                connection.InfoMessage += Connection_InfoMessage;
                using (SqlCommand command = connection.CreateCommand())
                {
                    command.CommandText = commandText;
                    command.CommandType = CommandType.Text;
                    command.ExecuteNonQuery();
                }
            }
        }

        private static void Connection_InfoMessage(object sender, SqlInfoMessageEventArgs e)
        {
            Console.WriteLine(e.Message);
        }
    }
}

要使用新名称恢复数据库，例如 newtest，您需要执行下一条语句

RESTORE DATABASE [newtest] 
FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\test.bak' WITH  FILE = 1,  
MOVE N'test' TO N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\newtest.mdf',  
MOVE N'test_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\newtest_log.ldf',  NOUNLOAD,  STATS = 5