LocalDb和SMO-如何选择将数据库文件放在何处?

问题描述

我已经开始尝试使用SQL Server 2012功能LocalDb.我正在针对我创建的实例(例如创建数据库)使用SMO进行一些powershell脚本编写.为此，我具有以下功能:

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()
}

使用LocalDb时，在C:\Users\<username>\中创建mdf和ldf文件.我想重写它，但是我找不到方法. $databaseHandle对象具有属性PrimaryFilePath，但这是只读的.看起来应该做起来很简单，但我只是不知道如何...

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...

解决方案在以下答案的引导下，这是我需要的Powershell调用:

SolutionLed 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)

推荐答案

我认为来自SMO论坛的线程包含了答案.您只需要将其转换为SQL PowerShell语法即可(目前我认为这只是SMO的包装).

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).

这是VBScript代码段:

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"))

这篇关于LocalDb和SMO-如何选择将数据库文件放在何处?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！