SMO database creation exception: "The PRIMARY filegroup must have at least one file"

Question

I'm trying to create a database using the SQL Server SMO classes but the line that creates the db throws a FailedOperationException.

The inner exception (SmoException) has the message "The PRIMARY filegroup must have at least one file.".

But I'm setting the PRIMARY filegroup in code (or at least I think I am) and I'm adding the data file (with .IsPrimaryFile=True) to the group.

I'm sure I'm missing something obvious/simple.

Any help would be appreciated :-)...

Imports Microsoft.SqlServer.Management.Smo

Dim serverName = "(local)"
Dim databaseName = "TestNew3"

Dim sourceSrv = New Server(serverName)

Dim db As Database
db = New Database(sourceSrv, databaseName)

db.AutoCreateStatisticsEnabled = True
db.AutoUpdateStatisticsEnabled = True
db.AutoUpdateStatisticsAsync = True

Dim fileGroup = New FileGroup(db, "PRIMARY")
fileGroup.IsDefault = True
db.FileGroups.Add(fileGroup)

Dim dataFile = New DataFile(
                           fileGroup,
                           databaseName,
                           String.Format(
                                        "{0}\{1}.mdf",
                                        sourceSrv.MasterDBPath,
                                        databaseName))
dataFile.GrowthType = FileGrowthType.KB
dataFile.Growth = 10240
dataFile.IsPrimaryFile = True

Dim logFile = New LogFile(
                          db,
                          databaseName,
                          String.Format(
                                        "{0}\{1}_log.ldf",
                                        sourceSrv.MasterDBPath,
                                        databaseName))
logFile.GrowthType = FileGrowthType.KB
logFile.Growth = 10240

db.LogFiles.Add(logFile)

db.Create() '<-- THROWS EXCEPTION ("The PRIMARY filegroup must have at least one file")

Answer 1

Looks like the dataFile has to explicitly be added to the fileGroup (it doesn't add itself even though it's passed the fileGroup as the first parameter of its constructor).

dataFile.Growth = 10240
dataFile.IsPrimaryFile = True

fileGroup.Files.Add(dataFile)

There was also a minor bug in the original code (the .ldf name was not unique and the setting of fileGroup.IsDefault to True caused errors).

Here's the full working code:

Dim serverName = "(local)"
Dim databaseName = "TestNew5"

Dim sourceSrv = New Server(serverName)

Dim db As Database
db = New Database(sourceSrv, databaseName)

db.AutoCreateStatisticsEnabled = True
db.AutoUpdateStatisticsEnabled = True
db.AutoUpdateStatisticsAsync = True

Dim fileGroup = New FileGroup(db, "PRIMARY")

Dim dataFile = New DataFile(
                           fileGroup,
                           databaseName,
                           String.Format(
                                        "{0}\{1}.mdf",
                                        sourceSrv.MasterDBPath,
                                        databaseName))
dataFile.GrowthType = FileGrowthType.KB
dataFile.Growth = 10240
dataFile.IsPrimaryFile = True

fileGroup.Files.Add(dataFile)

db.FileGroups.Add(fileGroup)

Dim logFile = New LogFile(
                          db,
                          databaseName + "_log",
                          String.Format(
                                        "{0}\{1}_1.ldf",
                                        sourceSrv.MasterDBPath,
                                        databaseName))
logFile.GrowthType = FileGrowthType.KB
logFile.Growth = 10240

db.LogFiles.Add(logFile)

db.Create()