Add a database filegroup and file to an existing database using PowerShell

Majority of the examples I could find online to add database filegroups and/or files using PowerShell were for new databases. They made use of methods that didn’t work for what I was after. What I needed to do was add a database filegroup and file to an existing database. Examples, and until recently, documentation, I couldn’t find so easily online.

The below script is an example of how to create a database filegroup and file (with some additional options) on an existing database. It’s pretty straight forward.

$server = New-Object ('Microsoft.SqlServer.Management.Smo.Server') 'localhost'
$database = $server.Databases['DemoDatabase']
$filegroup = New-Object ('Microsoft.SqlServer.Management.Smo.FileGroup') ($database, 'SecondaryFGName')
$filegroup.Create()

$data_file = New-Object ('Microsoft.SqlServer.Management.Smo.DataFile') ($filegroup, 'SecondaryFileName')
$filegroup.Files.Add($data_file)
$data_file.FileName = 'C:\Data\DemoDatabase\SecondaryFileName.ndf'
$data_file.Size = [double]1024000.0
$data_file.GrowthType = 'KB'
$data_file.Create()

Leave a Reply

Your email address will not be published. Required fields are marked *