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