Maybe you can try this:
SQL Server 2008 - Backup and Restore Databases using SMO[
^]
If you are using a newer version of SQL Server this can give problems as in my experience SMO and it's dll's can change between versions.
So the best advice I can give if you want your code to be futureproof is:
Don't use SMO !
Here is some code that shows how to do it without SMO:
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;
command.ExecuteNonQuery();
}
}
To restore:
var connectionString = connectionStringWithoutCatalog;
connectionString = connectionString.Replace("Connection Timeout=" + SqlConnectionTimeout + ";", "Connection Timeout=60;");
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
{
string logicalName;
string logicalNameLog;
if (DatabaseRestoreFilelistonly(connectionString, fullBackupFileName, out logicalName, out logicalNameLog))
{
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;
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"))
{
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);
}
}
}
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))
{
connection.Open();
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";
string sqlGetLogicalName = string.Format(formatString, backupFileName);
var command = connection.CreateCommand();
command.CommandText = sqlGetLogicalName;
var sqlDataReader = command.ExecuteReader();
if (sqlDataReader.HasRows)
{
sqlDataReader.Read();
logicalName = sqlDataReader.GetString(0);
logicalNameLog = sqlDataReader.GetString(1);
}
connection.Close();
}
if (string.IsNullOrEmpty(logicalName) || string.IsNullOrEmpty(logicalNameLog))
{
ErrorMessageEvent.Raise(EventSource, null, "DatabaseRestoreFilelistonly() " + backupFileName + " failed.");
return false;
}
return true;
}
And now you may understand why we don't use SQL Server anymore and switched to PostgreSQL :)