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(); } }