powershellsmolocaldb

LocalDb and SMO - how to choose where to put database files?

I've started to experiment with the SQL Server 2012 feature LocalDb. I'm doing some powershell scripting with SMO against the instances I create, such as creating databases. To do this, I have this function:

Function New-Database {
    Param(
        $server,
        $databaseName
    )
    $serverHandle = New-Object Microsoft.SqlServer.Management.Smo.Server($server)
    if($serverHandle.Databases.Name.Contains($databaseName)) {
        throw "Database $databaseName already exists"
    }

    $databaseHandle = New-Object Microsoft.SqlServer.Management.Smo.Database($serverHandle, $databaseName)
    $databaseHandle.Create()
}

When using LocalDb, the mdf and ldf files are created in C:\Users\<username>\. I want to override this, but I'm failing to find out how. The $databaseHandle object has a property PrimaryFilePath, but that is read-only. It seems like something that should be dead-simple to do, but I just can't find out how...

Solution Led by the answer below, here is the Powershell calls I needed:

$fileGroup = New-Object Microsoft.SqlServer.Management.Smo.FileGroup($databaseHandle, "PRIMARY")
$dataFile = New-Object Microsoft.SqlServer.Management.Smo.DataFile($fileGroup, "DataFile", $filePath)
$fileGroup.Files.Add($dataFile)
$databaseHandle.FileGroups.Add($fileGroup)
Solution

  • I think this thread from the SMO Forum contains the answer. You just need to translate it to SQL PowerShell syntax (which I believe is just a wrapper to SMO at this point).

    Here's the VBScript snippet:

    databaseHandle.FileGroups.Add(New FileGroup(db, "PRIMARY"))
databaseHandle.FileGroups(0).Files.Add(
    New DataFile(db.FileGroups(0), "TestName", "D:\SqlBancos\Teste\Teste1.mdf"))