You know, sometimes things that are really confusing become amazingly simple once someone explains them.
On Friday I'd attended a session at Tech Ed about SMO. One of the things I've found frustrating was the inability to specify database location and size properties before creating the database. I could create the database and then alter it, but the objects were already created in default locations by then and that's less than optimal. I also had to resort to building a Transact-SQL string to create a snapshot database. I mentioned these frustrations to Richard Hundhausen, the presenter of the session and a Microsoft Regional Director. He tried a couple of things and had the same results I did.
Richard sent an email to Euan Garden, his source for a lot of solutions to SQL Server 2005 problems. Euan is no longer working on SQL Server, but directed us to Michiel Wories and James Howey. I explained my application requirements to them, and Michiel sent me some sample code showing how to solve both of these issues.
Dim strDBName As String
strDBName = "TestDatabase"
Dim dbCopy As Database
Dim dbFG As FileGroup
Dim dbFile As DataFile
Dim dbLogFile As LogFile
Dim dbSnap As Database
dbCopy = New Database(srv, strDBName) 'Instantiate the new database
dbFG = New FileGroup(dbCopy, "PRIMARY") 'Instantiate the PRIMARY filegroup
dbCopy.FileGroups.Add(dbFG) 'Add the FileGroup
dbFile = New DataFile(dbFG, strDBName + "_Data") 'Instantiate the data file within the filegroup
dbFG.Files.Add(dbFile) 'Add the File
dbFile.FileName = "D:\MSSQL.1\MSSQL\Data\" + strDBName + "_Data.mdf" 'Define the actual file system name
dbFile.Size = 25.0 * 1024.0 'Define the size of the file
dbFile.GrowthType = FileGrowthType.Percent 'Define the Growth Type
dbFile.Growth = 25.0 'Define the Growth Percent
dbFile.MaxSize = 100.0 * 1024.0 'Define the Max database size
dbLogFile = New LogFile(dbCopy, strDBName + "_Log") 'Instantiate the log file (no filegroup for log files)
dbCopy.LogFiles.Add(dbLogFile) 'Add the log file
dbLogFile.FileName = "L:\MSSQL.1\MSSQL\Data\" + strDBName + "_Log.ldf" 'Define the log file system name
dbLogFile.Size = 10.0 * 1024.0 'Define the size of the log file
dbLogFile.GrowthType = FileGrowthType.Percent 'Define the Growth Type
dbLogFile.Growth = 25.0 'Define the Growth Percent
dbCopy.Create() 'Create the database
Michiel's example defined multiple filegroups and data files but my needs were simpler. His example also is in C#, and I mostly work in VB. (As soon as he posts his examples I'll link to them.)
Building the snapshot database was also very straightforward.
dbSnap = New Database(srv, strDBName + "_snap") 'Instantiate the new database
Dim fgSnap As FileGroup
For Each fgSnap In dbCopy.FileGroups 'Step through the existing filegroups
Dim dbFGSnap As FileGroup
Dim dbFileSnap As DataFile
dbFGSnap = New FileGroup(dbSnap, fgSnap.Name) 'Instantiate a new filegroup to match the base database's filegroup
dbSnap.FileGroups.Add(dbFGSnap) 'Add the FileGroup
For Each dbFileSnap In fgSnap.Files 'For each file in the group, create a corresponding snap file
dbFGSnap.Files.Add(New DataFile(dbFGSnap, dbFileSnap.Name, dbFileSnap.FileName + ".snap"))
Next
Next
dbSnap.DatabaseSnapshotBaseName = strDBName 'Define the base database for the snapshot
dbSnap.Create() 'Create the database
As you can see, when it's explained by someone who knows what they're doing it's easy!
Thanks so much to Richard, Euan and especially to Michiel Wories, for helping me get over this hump.
Allen