Мобильная версияsqlexception: при отправке запроса на сервер возникла ошибка на уровне транспорта. (Поставщик: поставщик общей памяти, ошибка: 0 - нет процесса на другом конце трубы."ConnectionStrings": {
"Default": "User Id=*****;Password=******;Initial Catalog=*******;Data Source=.;TrustServerCertificate=True"
}
Что я попробовал и что я ожидал
- Я называю Createbackupasync (...) и успешно создает файл. вызовать RESTORABACKEPASYNC (ID) . Этот метод: < /p>
Загружает .gz из Blob Storage < /li>
Декомпрессирует его < /li>
копирует файл резервного копирования в Database Server Server. Заменить , затем изменить базу данных ... Установить multi_user .
Я ожидал, что восстановление завершится, а база данных будет доступна после
вместо этого не удастся с помощью переноса
, восстановление с удалением с транспортом
. /> sqlexception (win32exception: «Процесс не находится на другом конце трубы»., Ошибка 233). < /p>
< /blockquote>
Соединение падает во время /после восстановления, и ошибка 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 database 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
