RickZeeland
Может быть, вы можете попробовать это: SQL Server 2008 - резервное копирование и восстановление баз данных с помощью SMO[^]
Если вы используете более новую версию SQL Server, это может привести к проблемам, как в моем опыте SMO, и его dll-файлы могут меняться между версиями.
Поэтому лучший совет, который я могу дать, если вы хотите, чтобы ваш код был защищен от будущего, - это:
Не используйте SMO !
Вот некоторый код, который показывает, как это сделать без SMO:
// Make SQL Server backup.
// Use a longer connection timeout of 60 s for connecting instead of the default 15 s.
var connectionString = connectionStringWithoutCatalog;
connectionString = connectionString.Replace("Connection Timeout=" + SqlConnectionTimeout + ";", "Connection Timeout=60;");
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
if (connection.State == ConnectionState.Open)
{
SqlCommand command;
if (connection.Database.ToLower().Equals("master"))
{
command = new SqlCommand(string.Format(@"BACKUP DATABASE [{0}] TO DISK = '{1}' WITH FORMAT, NAME = 'Full Backup of {0}';", catalog, backupFileName), connection);
}
else
{
command = new SqlCommand(string.Format(@"USE Master; BACKUP DATABASE [{0}] TO DISK = '{1}' WITH FORMAT, NAME = 'Full Backup of {0}';", catalog, backupFileName), connection);
}
command.CommandTimeout = 0; // No Timeout: avoid "Timeout expired" error as large backup operations can take long to complete.
command.ExecuteNonQuery();
}
}
Восстанавливать:
// SQL Server, use a longer connection timeout of 60 s for connecting instead of the default 15 s.
var connectionString = connectionStringWithoutCatalog;
connectionString = connectionString.Replace("Connection Timeout=" + SqlConnectionTimeout + ";", "Connection Timeout=60;");
// First check if file exists in the default backup location, e.g. C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup
var backupPath = GetDatabaseDefaultBackupPath(connectionString);
var errorMessage = string.Empty;
if (!File.Exists(Path.Combine(backupPath, fullBackupFileName)))
{
ErrorMessageEvent.Raise(EventSource, null, "DatabaseRestore() " + fullBackupFileName + " backup file not found.");
return false;
}
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
if (connection.State == ConnectionState.Open)
{
string sqlMaster = string.Empty;
string sqlString;
try
{
if (!connection.Database.ToLower().Equals("master"))
{
sqlMaster = @"USE Master; ";
}
if (DatabaseExists(connectionString, catalog))
{
sqlString = string.Format(@"{0} ALTER DATABASE [{1}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;", sqlMaster, catalog);
var commandAlter = new SqlCommand(sqlString, connection);
commandAlter.CommandTimeout = SqlCommandTimeout;
commandAlter.ExecuteNonQuery();
}
if (GetSqlServerVersionYear(connectionString) < 2008)
{
string formatString = @"RESTORE DATABASE [{0}] FROM DISK = '{1}'; ";
formatString += @"ALTER DATABASE [{0}] SET MULTI_USER WITH ROLLBACK IMMEDIATE;";
sqlString = string.Format(formatString, catalog, fullBackupFileName);
}
else
{
// From SQL Server 2008 the RESTORE command is different.
// Retrieve the logical file names of the database from backup file first
// with the new DatabaseRestoreFilelistonly() method.
string logicalName;
string logicalNameLog;
if (DatabaseRestoreFilelistonly(connectionString, fullBackupFileName, out logicalName, out logicalNameLog))
{
// Now construct the RESTORE command with the correct logical file names.
string formatString = @"RESTORE DATABASE [{0}] FROM DISK = N'{1}' ";
formatString += @"WITH FILE = 1, MOVE N'{3}' TO N'{2}{0}.mdf', ";
formatString += @"MOVE N'{4}' TO N'{2}{0}_log.LDF', NOUNLOAD, STATS = 5; ";
formatString += @"ALTER DATABASE [{0}] SET MULTI_USER WITH ROLLBACK IMMEDIATE;";
sqlString = string.Format(formatString, catalog, fullBackupFileName, backupPath.Replace(@"\Backup", @"\DATA\"), logicalName, logicalNameLog);
}
else
{
return false;
}
}
sqlString = sqlMaster + sqlString;
Debug.Print(sqlString);
var command = new SqlCommand(sqlString, connection);
command.CommandTimeout = 0; // No Timeout: avoid "Timeout expired" error.
command.ExecuteNonQuery();
WarningMessageEvent.Raise(EventSource, null, "DatabaseRestore() " + fullBackupFileName + " succes.");
return true;
}
catch (Exception ex)
{
Debug.Print(EventSource + " " + ex.Message);
if (ex.Message.Contains("media family"))
{
// Give a more user friendly message than "The media family on device ... is incorrectly formed".
errorMessage = @"The backup was probably made with a higher SQL Server version, and can not be restored.";
}
else
{
errorMessage = @"It might be needed to set Server Properties - Connections - Remote query timeout to 0 in SSMS.";
}
ErrorMessageEvent.Raise(EventSource, ex, errorMessage);
}
}
}
/// <summary>
/// Retrieve the logical file name of the database from backup file with RESTORE FILELISTONLY FROM DISK.
/// This is needed for SQL Server 2008 and higher.
/// </summary>
private static bool DatabaseRestoreFilelistonly(string connectionString, string backupFileName, out string logicalName, out string logicalNameLog)
{
logicalName = string.Empty;
logicalNameLog = string.Empty;
using (var connection = new SqlConnection(connectionString))
{
// Open the database connection
connection.Open();
// Create a temporary table to buffer the output from RESTORE FILELISTONLY FROM DISK.
string formatString =
@"DECLARE @Table TABLE (LogicalName varchar(128),[PhysicalName] varchar(128), [Type] varchar,
[FileGroupName] varchar(128), [Size] varchar(128), [MaxSize] varchar(128),
[FileId]varchar(128), [CreateLSN]varchar(128), [DropLSN]varchar(128),
[UniqueId]varchar(128), [ReadOnlyLSN]varchar(128), [ReadWriteLSN]varchar(128),
[BackupSizeInBytes]varchar(128), [SourceBlockSize]varchar(128), [FileGroupId]varchar(128),
[LogGroupGUID]varchar(128), [DifferentialBaseLSN]varchar(128), [DifferentialBaseGUID]varchar(128),
[IsReadOnly]varchar(128), [IsPresent]varchar(128), [TDEThumbprint]varchar(128)) ";
formatString += @"DECLARE @Path varchar(1000)='{0}'
DECLARE @LogicalNameData varchar(128),@LogicalNameLog varchar(128)
INSERT INTO @table EXEC('RESTORE FILELISTONLY FROM DISK=''' +@Path+ '''')
SET @LogicalNameData=(SELECT LogicalName FROM @Table WHERE Type='D')
SET @LogicalNameLog=(SELECT LogicalName FROM @Table WHERE Type='L')
SELECT @LogicalNameData,@LogicalNameLog";
// SQL query
string sqlGetLogicalName = string.Format(formatString, backupFileName);
// Get the logicalNames from the database
var command = connection.CreateCommand();
command.CommandText = sqlGetLogicalName;
var sqlDataReader = command.ExecuteReader();
if (sqlDataReader.HasRows)
{
sqlDataReader.Read();
logicalName = sqlDataReader.GetString(0);
logicalNameLog = sqlDataReader.GetString(1);
}
// Close database connection
connection.Close();
}
if (string.IsNullOrEmpty(logicalName) || string.IsNullOrEmpty(logicalNameLog))
{
ErrorMessageEvent.Raise(EventSource, null, "DatabaseRestoreFilelistonly() " + backupFileName + " failed.");
return false;
}
return true;
}
И теперь вы можете понять, почему мы больше не используем SQL Server и перешли на PostgreSQL :)