如何在.NET Core中还原SQL Server备份 [英] How to restore a SQL Server backup in .NET Core
问题描述
我想使用.NET Core还原SQL Server数据库(.bak).这是我在 GitHub 上的空网站，因此您可以看到当前的配置.
I want to restore a SQL Server database (.bak) using .NET Core. Here is my empty website on GitHub so you can see the current config.
在整个.NET Framework中恢复数据库非常简单-可以在此处看到.
Restoring a database is fairly simple in the full .NET Framework - as can bee seen here.
是否可以直接从.NET Core中进行操作，还是需要引用.NET Framework并使用.NET Framework类库?
Is there a way to do it from .NET Core directly, or will I need to reference the .NET Framework and use a .NET Framework class library?
无论我如何尝试，我都无法正常工作.
No matter how I try, I can't get it to work.
编辑
我尝试添加SQLManagementObject，但不能.我在.NET Core 2.0上.
I tried adding SQLManagementObject, but can't. I'm on .NET Core 2.0.
编辑2
我们的旧项目主要是ADO.NET.他们使用(广泛地)下列我无法带入.NET Core项目的DLL:
Our old projects are largely ADO.NET. They use (extensively) the following DLL's that I cannot bring into my .NET Core Project:
- Microsoft.SqlServer.ConnectionInfo
- Microsoft.SqlServer.Smo
- Microsoft.SqlServer.SmoExtended
- Microsoft.SqlServer.Management.Sdk.Sfc
推荐答案
更新:使用.Net Core 2.0，您可以使用Microsoft.SqlServer.SqlManagementObjects(140.17265.0). SQL Server管理对象(SMO)框架 您可以在Windows和Linux下使用SQL SMO.
UPDATE: With .Net Core 2.0 you can use Microsoft.SqlServer.SqlManagementObjects (140.17265.0). SQL Server Management Objects (SMO) Framework You can use SQL SMO under Windows and Linux.
Microsoft.SqlServer.SqlManagementObjects取决于System.Data.SqlClient(4.5.0)
Microsoft.SqlServer.SqlManagementObjects depends on System.Data.SqlClient (4.5.0)
简单的SMO备份示例:
Simple SMO backup example:
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语句的语法.请咨询
In .NetCore to backup/restore SQL Server database you can use common ADO.NET SqlConnection and SqlCommand objects. To customize backup/restore you need know the syntax of T-SQL BACKUP/RESTORE statements. Please consult with
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 )还原数据库，您需要执行下一条语句
To RESTORE a database with new name for example newtest, you need execute next statement
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
这篇关于如何在.NET Core中还原SQL Server备份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
