public static void AttachDatabase(string xiSqlInstanceName,
SmoAuthenticationMode xiMsSqlAuthentication,
string xiUserId,
string xiPassword,
string xiDatabaseName,
IList <string> xiDatabaseFiles)
{
Assert.NonEmptyString(xiSqlInstanceName, "xiSqlInstanceName");
Assert.NonEmptyString(xiDatabaseName, "xiDatabaseName");
Server sqlServerInstance = null;
try
{
sqlServerInstance = Smo.ConnectToMsSqlServer(xiSqlInstanceName, xiMsSqlAuthentication, xiUserId, xiPassword);
Assert.NonNullReference(sqlServerInstance, "sqlServerInstance");
Logger.Instance.LogTrace("Smo: Attaching DB {0} to {1} with files ...", xiDatabaseName, xiSqlInstanceName);
if (!sqlServerInstance.Databases.Contains(xiDatabaseName))
{
StringCollection DatabaseFiles = new StringCollection();
foreach (string DatabaseFile in xiDatabaseFiles)
{
Logger.Instance.LogTrace("Smo: {0}", DatabaseFile);
DatabaseFiles.Add(DatabaseFile);
}
sqlServerInstance.AttachDatabase(xiDatabaseName, DatabaseFiles, AttachOptions.None);
Logger.Instance.LogTrace("Smo: Attached DB {0} to {1}.", xiDatabaseName, xiSqlInstanceName);
}
else
{
Logger.Instance.LogTrace("Smo: DB {0} already exists in {1}.", xiDatabaseName, xiSqlInstanceName);
}
}
catch (Exception ex)
{
Logger.Instance.LogException(ex, string.Format("Smo: Attach DB {0} to {1} failed, trying direct mode ...", xiDatabaseName, xiSqlInstanceName));
try
{
Smo.AttachDatabaseDirectly(xiSqlInstanceName, xiMsSqlAuthentication, xiUserId, xiPassword, xiDatabaseName, xiDatabaseFiles);
}
catch (Exception)
{
throw;
}
}
finally
{
if (sqlServerInstance != null)
{
sqlServerInstance.ConnectionContext.Disconnect();
}
}
}
public static Server ConnectToMsSqlServer(string xiSqlInstanceName,
SmoAuthenticationMode xiMsSqlAuthentication,
string xiUserId,
string xiPassword)
{
Assert.NonEmptyString(xiSqlInstanceName, "xiSqlInstanceName");
Logger.Instance.LogTrace("Smo: Connecting to {0} in {1} mode as {2} ...", xiSqlInstanceName, xiMsSqlAuthentication.ToString(), xiUserId);
Server result = null;
ServerConnection serverConn = Factory <ServerConnection> .Instance.Create();
serverConn.StatementTimeout = Smo.kStatementTimeout;
serverConn.NonPooledConnection = true;
if (xiMsSqlAuthentication == SmoAuthenticationMode.WindowsAuthentication)
{
serverConn.ServerInstance = xiSqlInstanceName;
}
else if (xiMsSqlAuthentication == SmoAuthenticationMode.SqlServerAuthentication)
{
serverConn.ServerInstance = xiSqlInstanceName;
serverConn.LoginSecure = false;
serverConn.Login = xiUserId;
serverConn.Password = xiPassword;
}
result = Factory <Server> .Instance.Create(new object [] { serverConn });
Logger.Instance.LogTrace("Smo: Connected to {0} in {1} mode as {2}.", xiSqlInstanceName, xiMsSqlAuthentication.ToString(), xiUserId);
return(result);
}
public static void DetachDatabaseDirectly(string xiSqlInstanceName,
SmoAuthenticationMode xiMsSqlAuthentication,
string xiUserId,
string xiPassword,
string xiDatabaseName)
{
Assert.NonEmptyString(xiSqlInstanceName, "xiSqlInstanceName");
Assert.NonEmptyString(xiDatabaseName, "xiDatabaseName");
Logger.Instance.LogTrace("Smo: Detaching DB {0} <<directly>> from {1} ...", xiDatabaseName, xiSqlInstanceName);
SqlConnection sqlConnection = null;
try
{
SqlConnectionStringBuilder sqlConnectionStringBuilder = new SqlConnectionStringBuilder();
sqlConnectionStringBuilder.DataSource = xiSqlInstanceName;
sqlConnectionStringBuilder.IntegratedSecurity = xiMsSqlAuthentication == SmoAuthenticationMode.WindowsAuthentication;
sqlConnectionStringBuilder.InitialCatalog = "master";
if (xiMsSqlAuthentication == SmoAuthenticationMode.SqlServerAuthentication)
{
sqlConnectionStringBuilder.UserID = xiUserId;
sqlConnectionStringBuilder.Password = xiPassword;
}
sqlConnection = new SqlConnection(sqlConnectionStringBuilder.ToString());
sqlConnection.Open();
Assert.Condition(sqlConnection.State == ConnectionState.Open, "sqlConnection.State == ConnectionState.Open");
StringBuilder sqlQuery = new StringBuilder();
sqlQuery.AppendFormat(@"USE [master]{0}", Environment.NewLine);
sqlQuery.AppendFormat(@"ALTER DATABASE [{0}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE{1}", xiDatabaseName, Environment.NewLine);
sqlQuery.AppendFormat(@"EXEC master.dbo.sp_detach_db @dbname = N'{0}', @keepfulltextindexfile=N'true'", xiDatabaseName);
Smo.ExecuteQuery(sqlConnection, sqlQuery.ToString(), null);
Logger.Instance.LogTrace("Smo: Detached DB {0} <<directly>> from {1}.", xiDatabaseName, xiSqlInstanceName);
}
catch (Exception ex)
{
Logger.Instance.LogException(ex, string.Format("Smo: Detach DB {0} <<directly>> from {1} failed.", xiDatabaseName, xiSqlInstanceName));
throw;
}
finally
{
if (sqlConnection != null && (sqlConnection.State != ConnectionState.Closed && sqlConnection.State != ConnectionState.Broken))
{
sqlConnection.Close();
sqlConnection = null;
}
}
}
public static void DetachDatabase(string xiSqlInstanceName,
SmoAuthenticationMode xiMsSqlAuthentication,
string xiUserId,
string xiPassword,
string xiDatabaseName)
{
Assert.NonEmptyString(xiSqlInstanceName, "xiSqlInstanceName");
Assert.NonEmptyString(xiDatabaseName, "xiDatabaseName");
Logger.Instance.LogTrace("Smo: Detaching DB {0} from {1} ...", xiDatabaseName, xiSqlInstanceName);
Server sqlServerInstance = null;
try
{
sqlServerInstance = Smo.ConnectToMsSqlServer(xiSqlInstanceName, xiMsSqlAuthentication, xiUserId, xiPassword);
Assert.NonNullReference(sqlServerInstance, "sqlServerInstance");
if (sqlServerInstance.Databases.Contains(xiDatabaseName))
{
Smo.KillAllProcesses(sqlServerInstance, xiSqlInstanceName, xiDatabaseName);
Smo.DetachDatabase(sqlServerInstance, xiDatabaseName);
Logger.Instance.LogTrace("Smo: Detached DB {0} from {1}.", xiDatabaseName, xiSqlInstanceName);
}
else
{
Logger.Instance.LogTrace("Smo: DB {0} not found in {1}.", xiDatabaseName, xiSqlInstanceName);
}
}
catch (Exception ex)
{
Logger.Instance.LogException(ex, string.Format("Smo: Detach DB {0} from {1} failed.", xiDatabaseName, xiSqlInstanceName));
try
{
Smo.DetachDatabaseDirectly(xiSqlInstanceName, xiMsSqlAuthentication, xiUserId, xiPassword, xiDatabaseName);
}
catch (Exception)
{
throw;
}
}
finally
{
if (sqlServerInstance != null)
{
sqlServerInstance.ConnectionContext.Disconnect();
}
}
}
public static bool HasDatabase(string xiSqlInstanceName,
SmoAuthenticationMode xiMsSqlAuthentication,
string xiUserId,
string xiPassword,
string xiDatabaseName)
{
Assert.NonEmptyString(xiSqlInstanceName, "xiSqlInstanceName");
Assert.NonEmptyString(xiDatabaseName, "xiDatabaseName");
Server sqlServerInstance = Smo.ConnectToMsSqlServer(xiSqlInstanceName, xiMsSqlAuthentication, xiUserId, xiPassword);
Assert.NonNullReference(sqlServerInstance, "sqlServerInstance");
return(sqlServerInstance.Databases.Contains(xiDatabaseName));
}
public static bool TestSqlConnection(string xiSqlInstanceName, SmoAuthenticationMode xiMsSqlAuthentication, string xiUserId, string xiPassword)
{
bool result = true;
try
{
SqlConnectionStringBuilder sqlConnectionStringBuilder = new SqlConnectionStringBuilder();
sqlConnectionStringBuilder.DataSource = xiSqlInstanceName;
sqlConnectionStringBuilder.IntegratedSecurity = xiMsSqlAuthentication == SmoAuthenticationMode.WindowsAuthentication;
sqlConnectionStringBuilder.InitialCatalog = "master";
if (xiMsSqlAuthentication == SmoAuthenticationMode.SqlServerAuthentication)
{
sqlConnectionStringBuilder.UserID = xiUserId;
sqlConnectionStringBuilder.Password = xiPassword;
}
SqlConnection sqlConnection = null;
try
{
sqlConnection = new SqlConnection(sqlConnectionStringBuilder.ToString());
sqlConnection.Open();
result = sqlConnection.State == ConnectionState.Open;
}
finally
{
if (sqlConnection != null && sqlConnection.State != ConnectionState.Closed && sqlConnection.State != ConnectionState.Broken)
{
SqlConnection.ClearPool(sqlConnection);
sqlConnection.Close();
sqlConnection = null;
}
}
}
catch
{
result = false;
}
return(result);
}
public static void AttachDatabaseDirectly(string xiSqlInstanceName,
SmoAuthenticationMode xiMsSqlAuthentication,
string xiUserId,
string xiPassword,
string xiDatabaseName,
IList <string> xiDatabaseFiles)
{
Assert.NonEmptyString(xiSqlInstanceName, "xiSqlInstanceName");
Assert.NonEmptyString(xiDatabaseName, "xiDatabaseName");
SqlConnection sqlConnection = null;
try
{
Logger.Instance.LogTrace("Smo: Attaching DB {0} <<directly>> to {1} with files ...", xiDatabaseName, xiSqlInstanceName);
SqlConnectionStringBuilder sqlConnectionStringBuilder = new SqlConnectionStringBuilder();
sqlConnectionStringBuilder.DataSource = xiSqlInstanceName;
sqlConnectionStringBuilder.IntegratedSecurity = xiMsSqlAuthentication == SmoAuthenticationMode.WindowsAuthentication;
sqlConnectionStringBuilder.InitialCatalog = "master";
if (xiMsSqlAuthentication == SmoAuthenticationMode.SqlServerAuthentication)
{
sqlConnectionStringBuilder.UserID = xiUserId;
sqlConnectionStringBuilder.Password = xiPassword;
}
sqlConnection = new SqlConnection(sqlConnectionStringBuilder.ToString());
sqlConnection.Open();
Assert.Condition(sqlConnection.State == ConnectionState.Open, "sqlConnection.State == ConnectionState.Open");
StringBuilder sqlQuery = new StringBuilder();
sqlQuery.AppendFormat(@"USE [master]{0}", Environment.NewLine);
sqlQuery.AppendFormat(@"CREATE DATABASE [{0}] ON", xiDatabaseName);
for (int i = 0; i < xiDatabaseFiles.Count; i++)
{
sqlQuery.AppendFormat(@" (FILENAME = N'{0}')", xiDatabaseFiles[i]);
if (i < xiDatabaseFiles.Count - 1)
{
sqlQuery.Append(",");
}
}
sqlQuery.Append(" FOR ATTACH");
Smo.ExecuteQuery(sqlConnection, sqlQuery.ToString(), null);
Logger.Instance.LogTrace("Smo: Attached DB {0} <<directly>> to {1}.", xiDatabaseName, xiSqlInstanceName);
}
catch (Exception ex)
{
Logger.Instance.LogException(ex, string.Format("Smo: Attach DB {0} <<directly>> to {1} failed.", xiDatabaseName, xiSqlInstanceName));
throw;
}
finally
{
if (sqlConnection != null && (sqlConnection.State != ConnectionState.Closed && sqlConnection.State != ConnectionState.Broken))
{
sqlConnection.Close();
sqlConnection = null;
}
}
}
public static string GetDatabaseName(string xiPrefix, string xiSqlInstanceName, SmoAuthenticationMode xiMsSqlAuthentication, string xiUserId, string xiPassword)
{
Assert.NonEmptyString(xiPrefix, "xiPrefix");
Assert.NonEmptyString(xiSqlInstanceName, "xiSqlInstanceName");
Server sqlServerInstance = Smo.ConnectToMsSqlServer(xiSqlInstanceName, xiMsSqlAuthentication, xiUserId, xiPassword);
Assert.NonNullReference(sqlServerInstance, "sqlServerInstance");
string result = string.Empty;
for (int i = 1; ; i++)
{
try
{
string databaseName = string.Format(@"{0}-{1}-SP.GRT.TMP.EXCLUDED__FROM__BACKUP", xiPrefix, i);
if (!sqlServerInstance.Databases.Contains(databaseName))
{
result = databaseName;
break;
}
}
catch (Exception ex)
{
Logger.Instance.LogException(ex, "Smo: Failed to get a database name.");
throw;
}
}
return(result);
}
