public static string CreateFileGroup(string connectionString, string path, string groupName) { try { if (!AllowFileAccess(connectionString)) { return(null); } using (var conn = new System.Data.SqlClient.SqlConnection()) { conn.ConnectionString = connectionString; conn.Open(); //Check and create the file group var da = new SqlDataAdapter($"select * from sys.filegroups where name = '{groupName}'", conn); var ds = new DataSet(); da.Fill(ds); if (ds.Tables[0].Rows.Count > 0) { return(null); //already exists } da = new SqlDataAdapter("select * from sys.master_files where database_id = DB_ID() and type = 0", conn); ds = new DataSet(); da.Fill(ds); var fileID = ds.Tables[0].Rows[0]["name"].ToString(); var builder = new SqlConnectionStringBuilder(connectionString); //Create the new file group using (var command = new SqlCommand($"ALTER DATABASE {builder.InitialCatalog} ADD FILEGROUP {groupName}", conn)) { SqlServers.ExecuteCommand(command); } //If no path specified then just add to the data path if (string.IsNullOrEmpty(path)) { path = GetDataFilePath(connectionString); } //Create N "groupName" files in new file group for (var ii = 1; ii <= 4; ii++) { var newfileName = Path.Combine(path, $"{builder.InitialCatalog}_{groupName}{ii}.ndf"); using (var command = new SqlCommand($"ALTER DATABASE {builder.InitialCatalog} ADD FILE (NAME = {groupName}{ii}, FILENAME = '{newfileName}', SIZE = 64MB, FILEGROWTH = 64MB) TO FILEGROUP {groupName}", conn)) { SqlServers.ExecuteCommand(command); } } return(path); } } catch (Exception ex) { throw; } }
public static void SplitDbFiles(string connectionString) { try { if (!AllowFileAccess(connectionString)) { return; } using (var conn = new System.Data.SqlClient.SqlConnection()) { conn.ConnectionString = connectionString; conn.Open(); var da = new SqlDataAdapter("select * from sys.master_files where database_id = DB_ID() and type = 0", conn); var ds = new DataSet(); da.Fill(ds); if (ds.Tables[0].Rows.Count != 1) { return; } var fileID = ds.Tables[0].Rows[0]["name"].ToString(); var fileName = ds.Tables[0].Rows[0]["physical_name"].ToString(); var fi = new FileInfo(fileName); //Create N more data files var builder = new SqlConnectionStringBuilder(connectionString); for (var ii = 2; ii <= 4; ii++) { var newfileName = Path.Combine(fi.DirectoryName, $"{builder.InitialCatalog}_Data{ii}.ndf"); using (var command = new SqlCommand($"ALTER DATABASE {builder.InitialCatalog} ADD FILE (NAME = data{ii}, FILENAME = '{newfileName}', SIZE = 64MB, FILEGROWTH = 64MB)", conn)) { SqlServers.ExecuteCommand(command); } } //Set original file to grow at same rate using (var command = new SqlCommand($"ALTER DATABASE {builder.InitialCatalog} MODIFY FILE (NAME = {fileID}, FILEGROWTH = 64MB)", conn)) { SqlServers.ExecuteCommand(command); } } } catch (Exception ex) { throw; } }