Adding a secondary data file to a filegroup

This recipe walks you through how to add a secondary data file to a filegroup using PowerShell and SMO.

Getting ready

In this recipe, we will add a secondary data file to the FGActive filegroup we created for the TestDB database in the previous recipe. If you don't have this filegroup yet, you can follow the recipe, Creating a filegroup. Alternatively, you can execute the following T-SQL statement in SQL Server Management Studio to create the filegroup:

ALTER DATABASE [TestDB]
ADD FILEGROUP [FGActive]
GO

In this recipe, we will accomplish this T-SQL equivalent:

ALTER DATABASE [TestDB]
ADD FILE (
NAME = N'datafile1',
FILENAME = N'C:\Temp\datafile1.ndf')
TO FILEGROUP [FGActive]
GO

How to do it...

To add secondary data files to an existing filegroup, follow these steps:

1. Open PowerShell ISE as administrator.

2. Import the SQLPS module and create a new SMO Server object:

   #import SQL Server module
   Import-Module SQLPS -DisableNameChecking
   
   #replace this with your...