Allen White

Create Databases using SMO without resorting to Transact-SQL

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