SQL Server Restore не удастся с «ошибкой на уровне транспорта» (поставщик общей памяти, ошибка: 0 - Процесс не на другом ⇐ C#

[Anonymous]

Я работаю с SQL Server, и я могу успешно создать резервную копию базы данных."error": { "code": null, "message": "A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)", "details": "SqlException: A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)\\r\\nWin32Exception: No process is on the other end of the pipe.\\r\\n", "data": { "HelpLink.ProdName": "Microsoft SQL Server", "HelpLink.EvtSrc": "MSSQLServer", "HelpLink.EvtID": "233", "HelpLink.BaseHelpUrl": "https://go.microsoft.com/fwlink", "HelpLink.LinkId": "20476" }, "validationErrors": null } }
my appsettings Строка подключения:
"ConnectionStrings": {
"Default": "User Id=*****;Password=******;Initial Catalog=*******;Data Source=.;TrustServerCertificate=True"
}
< /code>
Что я попробовал, и что я ожидал < /p>
Я называю Createbackupasync (...), и он успешно создает файл .bak, сжимает его. Этот метод: < /p>
загружает .gz с хранилища Blob. Multi_user. < /P>
Я ожидал, что восстановление завершится, а DB будет доступен впоследствии. < /P>
Вместо этого восстановление не удается с помощью SQLEXCEPTION на уровне транспорта (WIN32Exception: «Процесс не находится на другом конце трубы»., Ошибка 233). Соединение падает во время/после восстановления, и в журналах появляется ошибка JSON. SQL Server может быть удаленным. Создание резервного копирования работает из приложения, но шаг восстановления сбои с ошибкой общей памяти/трубы.
// Usings (include these at top of your SO snippet)
using Microsoft.Data.SqlClient;
using System;
using System.IO;
using System.IO.Compression;
using System.Threading.Tasks;

// -----------------------------
// Create backup + helpers
// -----------------------------
public async Task CreateBackupAsync(int? retentionDays, string createdBy)
{
_logger.LogInformation("Creating database backup. RetentionDays: {RetentionDays},CreatedBy: {CreatedBy}, TenantId: {TenantId}", retentionDays, createdBy, CurrentTenant.Id);
var connectionString = await _connectionStringResolver.ResolveAsync(
ConnectionStrings.DefaultConnectionStringName
);
await using var connection = new SqlConnection(connectionString);
await connection.OpenAsync();
var timestamp = DateTime.UtcNow.ToString("yyyyMMdd-HHmmss");
var fileName = $"{connection.Database}-db-{timestamp}.bak";
var blobStoragePath = _configuration["BlobStorage:BlobStoragePath"];
var tenantId = CurrentTenant.Id?.ToString() ?? "host";
var tenantFolder = Path.Combine(blobStoragePath, "db-backups", tenantId);

var storedFileName = Path.Combine(tenantId, fileName + ".gz").Replace("\\", "/");

Directory.CreateDirectory(tenantFolder);

var backupPath = Path.Combine(tenantFolder, fileName);

try
{
await CreateBackupFileAsync(backupPath);

var compressedPath = CompressFileGzip(backupPath);

var container = _blobContainerFactory.Create("db-backups");
await using (var fileStream = File.OpenRead(compressedPath))
{
await container.SaveAsync(storedFileName, fileStream);
}

var fileInfo = new FileInfo(compressedPath);
var backup = new DatabaseBackup(
Guid.NewGuid(),
Path.GetFileName(storedFileName),
Path.GetFileName(storedFileName),
createdBy ?? "system",
fileInfo.Length,
retentionDays)
{
TenantId = CurrentTenant.Id
};

using var uow = _unitOfWorkManager.Begin();
await _backupRepository.InsertAsync(backup);
await uow.CompleteAsync();

_logger.LogInformation("Database backup created. BackupId: {BackupId}, FileName: {FileName}, TenantId: {TenantId}", backup.Id, backup.FileName, backup.TenantId);
return backup;
}
catch (Exception ex)
{
_logger.LogError(ex, "Error creating database backup. TenantId: {TenantId}", CurrentTenant.Id);
throw;
}
finally
{
try { if (File.Exists(backupPath)) File.Delete(backupPath); } catch { }
try { if (File.Exists(backupPath + ".gz")) File.Delete(backupPath + ".gz"); } catch { }
}
}

private async Task CreateBackupFileAsync(string backupPath)
{
var connectionString = await _connectionStringResolver.ResolveAsync(
ConnectionStrings.DefaultConnectionStringName
);

await using var connection = new SqlConnection(connectionString);
await connection.OpenAsync();

await using var command = connection.CreateCommand();
command.CommandTimeout = 300;

command.CommandText = $@"
BACKUP DATABASE [{connection.Database}]
TO DISK = '{backupPath}'
WITH FORMAT, STATS = 10, NAME = 'LimsBand Full Backup'";

await command.ExecuteNonQueryAsync();
}

private static string CompressFileGzip(string sourcePath)
{
var destPath = sourcePath + ".gz";
using (var source = File.OpenRead(sourcePath))
using (var destination = File.Create(destPath))
using (var gzip = new GZipStream(destination, CompressionLevel.Optimal))
{
source.CopyTo(gzip);
}
return destPath;
}

// -----------------------------
// Restore backup + helpers
// -----------------------------
public async Task RestoreBackupAsync(Guid id)
{
_logger.LogInformation("Restoring database backup. BackupId: {BackupId}, TenantId: {TenantId}", id, CurrentTenant.Id);
var backup = await _backupRepository.GetAsync(id);
if (backup.Status != DatabaseBackupStatus.Active)
{
throw new InvalidOperationException("Cannot restore from non-active backup");
}

var tempPath = Path.GetTempPath();
var localBlobPath = Path.Combine(tempPath, backup.FileName);

try
{
var container = _blobContainerFactory.Create("db-backups");
var tenantId = CurrentTenant.Id?.ToString() ?? "host";
var blobPath = $"{tenantId}/{backup.FileName}";
await using (var stream = await container.GetAsync(blobPath))
await using (var fileStream = File.Create(localBlobPath))
{
await stream.CopyToAsync(fileStream);
}

string backupFileForSql = localBlobPath;

if (Path.GetExtension(localBlobPath).Equals(".gz", StringComparison.OrdinalIgnoreCase))
{
backupFileForSql = DecompressGzipFile(localBlobPath);

try { if (File.Exists(localBlobPath)) File.Delete(localBlobPath); } catch { }
}

// Copy the file to a directory SQL Server can access (on the DB server)
var sqlBackupPath = await CopyToSqlServerDirectoryAsync(backupFileForSql);

// Perform the restore with exclusive access handling
await RestoreDatabaseFromFileAsync(sqlBackupPath);
}
catch (Exception ex)
{
_logger.LogError(ex, "Error restoring database backup. BackupId: {BackupId}, TenantId: {TenantId}", id, CurrentTenant.Id);
throw;
}
finally
{
try { if (File.Exists(localBlobPath)) File.Delete(localBlobPath); } catch { }
}

_logger.LogInformation("Database backup restored. BackupId: {BackupId}, TenantId: {TenantId}", id, CurrentTenant.Id);
}

private async Task CopyToSqlServerDirectoryAsync(string sourceBackupPath)
{
var configured = _configuration.GetConnectionString("Default");
var builder = new SqlConnectionStringBuilder(configured)
{
InitialCatalog = "master"
};

using var connection = new SqlConnection(builder.ToString());
await connection.OpenAsync();

using var pathCommand = connection.CreateCommand();
pathCommand.CommandText = @"
DECLARE @BackupDirectory NVARCHAR(4000);
EXEC master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'BackupDirectory',
@BackupDirectory OUTPUT;
SELECT ISNULL(@BackupDirectory, 'C:\\Program Files\\Microsoft SQL Server\\MSSQL15.MSSQLSERVER\\MSSQL\\Backup');";

var result = await pathCommand.ExecuteScalarAsync();
var defaultPath = result?.ToString() ?? @"C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup";
var sqlServerBackupPath = Path.Combine(defaultPath, Path.GetFileName(sourceBackupPath));

// Copy to the SQL Server backup directory. IMPORTANT: If SQL Server runs on a different machine,
// this local File.Copy won't place the file on the DB host. For remote DB servers you must copy to a UNC share
// the SQL Server service account can read, or use an out-of-band copy to the DB host.
File.Copy(sourceBackupPath, sqlServerBackupPath, true);

_logger.LogInformation("Copied backup file to SQL Server backup directory. Source: {Source}, Dest: {Dest}", sourceBackupPath, sqlServerBackupPath);

return sqlServerBackupPath;
}

private async Task RestoreDatabaseFromFileAsync(string backupPath)
{
var connectionString = _configuration.GetConnectionString("Default");
var connectionStringBuilder = new SqlConnectionStringBuilder(connectionString);
var databaseName = connectionStringBuilder.InitialCatalog ?? throw new InvalidOperationException("No InitialCatalog in connection string.");

var masterConnectionString = await GetWorkingMasterConnectionStringAsync();

await ExecuteRestoreWithExclusiveAccessAsync(masterConnectionString, databaseName, backupPath);
}

private async Task GetWorkingMasterConnectionStringAsync(int? explicitPort = null)
{
var configured = _configuration.GetConnectionString("Default");
if (string.IsNullOrWhiteSpace(configured)) throw new InvalidOperationException("Default connection string not configured.");

var originalBuilder = new SqlConnectionStringBuilder(configured)
{
InitialCatalog = "master"
};

if (await CanOpenConnectionAsync(originalBuilder.ToString()))
{
_logger.LogInformation("Using original master connection string (no tcp forced).");
return originalBuilder.ToString();
}

var tcpDs = TryBuildTcpPrefixedDataSource(originalBuilder.DataSource);
if (!string.Equals(tcpDs, originalBuilder.DataSource, StringComparison.OrdinalIgnoreCase))
{
var tcpBuilder = new SqlConnectionStringBuilder(originalBuilder.ToString())
{
DataSource = tcpDs,
Pooling = false
};

if (await CanOpenConnectionAsync(tcpBuilder.ToString()))
{
_logger.LogInformation("Using tcp-prefixed master connection string: {DataSource}", tcpBuilder.DataSource);
return tcpBuilder.ToString();
}
}

if (explicitPort.HasValue)
{
var host = originalBuilder.DataSource;
if (host.Contains("\\"))
{
host = host.Split('\\')[0];
}

var hostPort = $"{host},{explicitPort.Value}";
var portBuilder = new SqlConnectionStringBuilder(originalBuilder.ToString())
{
DataSource = hostPort,
Pooling = false
};

if (await CanOpenConnectionAsync(portBuilder.ToString()))
{
_logger.LogInformation("Using host,port master connection string: {DataSource}", portBuilder.DataSource);
return portBuilder.ToString();
}
}

_logger.LogError("Could not connect with original, tcp-prefixed or explicit-port connection string variants. Original DataSource: {DataSource}", originalBuilder.DataSource);
throw new InvalidOperationException("Could not connect to SQL Server using known connection string variants. Check TCP/IP, firewall, instance name and port.");
}

private string TryBuildTcpPrefixedDataSource(string dataSource)
{
if (string.IsNullOrWhiteSpace(dataSource)) return dataSource;

if (dataSource.StartsWith("tcp:", StringComparison.OrdinalIgnoreCase)
|| dataSource.Contains(",")
|| dataSource.Contains("\\"))
{
return dataSource;
}

return "tcp:" + dataSource;
}

private async Task CanOpenConnectionAsync(string connectionString)
{
try
{
var csb = new SqlConnectionStringBuilder(connectionString)
{
ConnectTimeout = 5
};
using var testConn = new SqlConnection(csb.ToString());
await testConn.OpenAsync();
await testConn.CloseAsync();
return true;
}
catch (Exception ex)
{
_logger.LogWarning(ex, "Connectivity test failed for connection string variant.");
return false;
}
}

private async Task ExecuteRestoreWithExclusiveAccessAsync(string masterConnectionString, string databaseName, string backupPath)
{
_logger.LogInformation("Starting restore helper. Database: {Db}, BackupPath: {Path}", databaseName, backupPath);

try
{
SqlConnection.ClearAllPools();
}
catch (Exception ex)
{
_logger.LogWarning(ex, "Could not clear SQL pools - continuing anyway.");
}

using (var conn = new SqlConnection(masterConnectionString))
{
await conn.OpenAsync();

try
{
using (var cmd = conn.CreateCommand())
{
cmd.CommandTimeout = 600;
cmd.CommandText = $@"
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'{databaseName}')
BEGIN
ALTER DATABASE [{databaseName}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
END";
await cmd.ExecuteNonQueryAsync();
}

await Task.Delay(500);

using (var restoreCmd = conn.CreateCommand())
{
restoreCmd.CommandTimeout = 3600; // long for large DBs
restoreCmd.CommandText = $@"
RESTORE DATABASE [{databaseName}]
FROM DISK = N'{backupPath}'
WITH REPLACE, STATS = 10;";
await restoreCmd.ExecuteNonQueryAsync();
}

var setMultiUserSucceeded = false;
for (int attempt = 0; attempt < 6 && !setMultiUserSucceeded; attempt++)
{
try
{
using var cmd2 = conn.CreateCommand();
cmd2.CommandTimeout = 300;
cmd2.CommandText = $@"ALTER DATABASE [{databaseName}] SET MULTI_USER;";
await cmd2.ExecuteNonQueryAsync();
setMultiUserSucceeded = true;
}
catch (SqlException)
{
await Task.Delay(2000);
}
}

if (!setMultiUserSucceeded)
{
try
{
using var recoveryConn = new SqlConnection(masterConnectionString);
await recoveryConn.OpenAsync();
using var recoveryCmd = recoveryConn.CreateCommand();
recoveryCmd.CommandTimeout = 300;
recoveryCmd.CommandText = $@"ALTER DATABASE [{databaseName}] SET MULTI_USER;";
await recoveryCmd.ExecuteNonQueryAsync();
}
catch (Exception ex)
{
_logger.LogWarning(ex, "Failed to set DB back to MULTI_USER after restore. The restore likely succeeded but DB may require manual intervention.");
}
}

_logger.LogInformation("Restore completed for database {DatabaseName}", databaseName);
}
catch (SqlException ex) when (ex.Number == 233 || ex.Message?.Contains("No process is on the other end of the pipe") == true)
{
_logger.LogInformation("Connection dropped during restore (expected). Attempting recovery...");

try
{
await Task.Delay(3000);
using var recoveryConnection = new SqlConnection(masterConnectionString);
await recoveryConnection.OpenAsync();

using var recoveryCmd = recoveryConnection.CreateCommand();
recoveryCmd.CommandTimeout = 300;
recoveryCmd.CommandText = $@"
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'{databaseName}')
BEGIN
ALTER DATABASE [{databaseName}] SET MULTI_USER;
END";
await recoveryCmd.ExecuteNonQueryAsync();

_logger.LogInformation("Database set to MULTI_USER after recovery.");
return;
}
catch (Exception recoveryEx)
{
_logger.LogWarning(recoveryEx, "Could not set database to multi-user mode after restore, but restore likely succeeded");
return;
}
}
catch (Exception ex)
{
_logger.LogError(ex, "Error during database restore. Database: {DatabaseName}", databaseName);

try
{
if (conn.State == System.Data.ConnectionState.Open)
{
using var recoveryCmd = conn.CreateCommand();
recoveryCmd.CommandTimeout = 300;
recoveryCmd.CommandText = $@"
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'{databaseName}')
BEGIN
ALTER DATABASE [{databaseName}] SET MULTI_USER;
END";
await recoveryCmd.ExecuteNonQueryAsync();
}
}
catch (Exception recoveryEx)
{
_logger.LogError(recoveryEx, "Could not recover database to MULTI_USER mode.");
}

throw;
}
}
}

private static string DecompressGzipFile(string gzipPath)
{
var destPath = Path.Combine(Path.GetDirectoryName(gzipPath) ?? "", Path.GetFileNameWithoutExtension(gzipPath));
using (var source = File.OpenRead(gzipPath))
using (var destination = File.Create(destPath))
using (var gzip = new GZipStream(source, CompressionMode.Decompress))
{
gzip.CopyTo(destination);
}
return destPath;
}


Подробнее здесь: https://stackoverflow.com/questions/797 ... hared-memo