问题描述
我有一种情况，我想更新目标数据库的架构以匹配已知的源架构，我想使用vsdbcmd.exe自动执行它。
I have a situation where I want to update the schema of a target database to match a known source schema and I'd like to automate it using the vsdbcmd.exe.
我在针对SQL Server 2008数据库使用VS2010 Ultimate。 一切都很有效，除了架构更新脚本包括以下内容：
I'm using VS2010 Ultimate against a SQL Server 2008 database. Everything works great except the schema update script includes the following:
ALTER
数据库 [$（DatabaseName）]
DATABASE [$(DatabaseName)]
ADD
FILE
（ NAME
= [TrivialDB] ，
FILENAME
=
'$（DefaultDataPath）$（DatabaseName）.mdf' ，
SIZE = 3072 KB ，
FILEGROWTH = 1024 KB ）
TO FILEGROUP [PRIMARY] ;
ADDFILE(NAME= [TrivialDB],FILENAME='$(DefaultDataPath)$(DatabaseName).mdf', SIZE = 3072 KB, FILEGROWTH = 1024 KB)TO FILEGROUP [PRIMARY];
在SQLCMD模式的MSSSMS查询窗口中运行时会产生以下错误：
and this generates the following error when run in a MSSSMS Query Window in SQLCMD Mode:
Msg 1834，Level 16，State 1，Line 1
Msg 1834, Level 16, State 1, Line 1
文件'C：\Program Files \ Microsoft SQL Server \ MSSQL10.MSSQLSERVER \ MSSQL \DATA \ TrivialDB_2.mdf'无法覆盖。数据库'TrivialDB_2'正在使用它。
The file 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TrivialDB_2.mdf' cannot be overwritten. It is being used by database 'TrivialDB_2'.
使用VS2010架构比较向导，我可以重现问题，这一切都归结为.scmp文件中存在此条目：
Using the VS2010 Schema Comparison wizard, I can reproduce the problem and it all boils down to the existence of this entry in the .scmp file:
< PropertyElementName>
< Name> Microsoft.Data.Schema.Sql.SchemaModel.ISqlFile< / Name>
< Value> ExcludedType< / Value>
< / PropertyElementName>
当一个人打开时，这个PropertyElement会被添加到.scmp Schema Compare Options并检查Object Types选项卡上的"SQL Files"复选框。这会导致比较操作忽略.mdf和.ldf文件名差异，因此
省略了ALTER DATABASE。 ..来自架构更新脚本。
This PropertyElement is added to the .scmp when one opens the Schema Compare Options and checks the "SQL Files" checkbox on the Object Types tab. This causes the comparison operation to ignore the .mdf and .ldf file name differences and, therefore, omits the ALTER DATABASE ... from the schema update script.
不幸的是，我无法弄清楚如何告诉vsdbcmd.exe执行相同的操作并忽略SQL文件。我已经尝试过.deploymanifest 属性
IgnoreFileAndLogFilePath ，但这与告知比较忽略实际数据库（和日志）文件名不同。（.scmp文件包含
IgnoreFileAndLogFilePath 和
Microsoft.Data.Schema.Sql.SchemaModel。 ISqlFile as
distinct PropertyElementName s所以它们必须表示不同的东西。） 我已经搜索了一个看起来会起作用的不同选项，甚至尝试指定
Microsoft.Data.Schema.Sql.SchemaModel.ISqlFile 以.deploymanifest中的各种形式，都没有成功。
Unfortunately, I cannot figure out how to tell the vsdbcmd.exe to do the same thing and ignore SQL Files. I've experimented with the .deploymanifest propertyIgnoreFileAndLogFilePath, but that is not the same as telling the comparison to ignore the actual database (and log) file names. (The .scmp file contains bothIgnoreFileAndLogFilePath and Microsoft.Data.Schema.Sql.SchemaModel.ISqlFile as distinct PropertyElementNames so they must mean different things.) I've searched all over for a different option that looks like it would work and even tried specifyingMicrosoft.Data.Schema.Sql.SchemaModel.ISqlFile in various forms in the .deploymanifest, all without success.
有没有人想过如何在使用vsdbcmd时关闭架构更新脚本中的ALTER DATABASE ... ADD FILE语句？
Has anyone figured this out how to turn off the ALTER DATABASE ... ADD FILE statements in a schema update script when working with vsdbcmd?
推荐答案
从sys.master_files中选择*
Select * from sys.master_files
查看哪个数据库占用了实际的物理文件TrivialDB_2.mdf
to see which database is occupying the actual physical file TrivialDB_2.mdf
VSDBCMD无法知道SQL Server中与您要部署的实际数据库有关的任何内容。此外，如果您首先使用TrivialDB_1创建文件，则将其重命名为TrivialDB_2，然后再添加另一个文件名为
TrivialDB_2，这会导致错误。移动，rena ming文件更像是一个管理事物而不是模式部署更改。
VSDBCMD cannot know of anything in SQL Server not concerning the actual database you want to deploy to. In addition if you create a file first with TrivialDB_1, then renamed it to TrivialDB_2 and lateron want to add another file which also has the name of TrivialDB_2, this will cause an error to. Moving, renaming files is more an administrative thing rather than a schema deployment change.
-Jens
这篇关于无法关闭由vsdbcmd生成的架构更新脚本中的ALTER DATABASE ... ADD FILE语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！