public override void DropDatabase(DiscoveredDatabase database)
{
bool userIsCurrentlyInDatabase = database.Server.GetCurrentDatabase().GetRuntimeName().Equals(database.GetRuntimeName());
var serverConnectionBuilder = new SqlConnectionStringBuilder(database.Server.Builder.ConnectionString);
if (userIsCurrentlyInDatabase)
{
serverConnectionBuilder.InitialCatalog = "master";
}
// Create a new server so we don't mutate database.Server and cause a whole lot of side-effects in other code, e.g. attachers
var server = new DiscoveredServer(serverConnectionBuilder);
var databaseToDrop = database.GetWrappedName();
string sql = "ALTER DATABASE " + databaseToDrop + " SET SINGLE_USER WITH ROLLBACK IMMEDIATE" + Environment.NewLine;
sql += "DROP DATABASE " + databaseToDrop;
using (var con = (SqlConnection)server.GetConnection())
{
con.Open();
using (var cmd = new SqlCommand(sql, con))
cmd.ExecuteNonQuery();
}
SqlConnection.ClearAllPools();
}
public override DirectoryInfo Detach(DiscoveredDatabase database)
{
const string GetDefaultSQLServerDatabaseDirectory = @"SELECT LEFT(physical_name,LEN(physical_name)-CHARINDEX('\',REVERSE(physical_name))+1)
FROM sys.master_files mf
INNER JOIN sys.[databases] d
ON mf.[database_id] = d.[database_id]
WHERE d.[name] = 'master' AND type = 0";
string dataFolder;
// Create a new server so we don't mutate database.Server and cause a whole lot of side-effects in other code, e.g. attachers
var server = database.Server;
var databaseToDetach = database.GetWrappedName();
// set in simple recovery and truncate all logs!
string sql = "ALTER DATABASE " + databaseToDetach + " SET RECOVERY SIMPLE; " + Environment.NewLine +
"DBCC SHRINKFILE (" + databaseToDetach + ", 1)";
using (var con = (SqlConnection)server.GetConnection())
{
con.Open();
using (var cmd = new SqlCommand(sql, con))
cmd.ExecuteNonQuery();
}
// other operations must be done on master
server.ChangeDatabase("master");
// set single user before detaching
sql = "ALTER DATABASE " + databaseToDetach + " SET SINGLE_USER WITH ROLLBACK IMMEDIATE;";
using (var con = (SqlConnection)server.GetConnection())
{
con.Open();
using (var cmd = new SqlCommand(sql, con))
cmd.ExecuteNonQuery();
}
var dbLiteralName = database.Server.GetQuerySyntaxHelper().Escape(database.GetRuntimeName());
// detach!
sql = @"EXEC sys.sp_detach_db '" + dbLiteralName + "';";
using (var con = (SqlConnection)server.GetConnection())
{
con.Open();
using (var cmd = new SqlCommand(sql, con))
cmd.ExecuteNonQuery();
}
// get data-files path from SQL Server
using (var connection = (SqlConnection)server.GetConnection())
{
connection.Open();
using (var cmd = new SqlCommand(GetDefaultSQLServerDatabaseDirectory, connection))
dataFolder = (string)cmd.ExecuteScalar();
}
return(dataFolder == null ? null : new DirectoryInfo(dataFolder));
}
public override void CreateBackup(DiscoveredDatabase discoveredDatabase, string backupName)
{
var server = discoveredDatabase.Server;
using (var con = server.GetConnection())
{
con.Open();
string sql = string.Format(
"BACKUP DATABASE {0} TO DISK = '{0}.bak' WITH INIT , NOUNLOAD , NAME = N'{1}', NOSKIP , STATS = 10, NOFORMAT",
discoveredDatabase.GetWrappedName(), backupName);
using (var cmd = server.GetCommand(sql, con))
cmd.ExecuteNonQuery();
}
}
