如何在 VSTS DB Edition Deploy 中自定义 CREATE DATABASE 语句？

发布于 2024-07-09 21:01:11 字数 836 浏览 6 评论 0原文

我正在使用 VSTS 数据库版本 GDR 版本 9.1.31024.02

我有一个项目，当客户添加到系统时，我们将动态创建具有相同架构的多个数据库。每个客户一个 DB。我想我应该能够使用部署脚本来执行此操作。不幸的是，我总是得到 CREATE DATABASE 语句中指定的完整文件名。例如：

CREATE DATABASE [$(DatabaseName)]
ON 
PRIMARY(NAME = [targetDBName], FILENAME = N'$(DefaultDataPath)targetDBName.mdf')
LOG ON (NAME = [targetDBName_log], FILENAME = N'$(DefaultDataPath)targetDBName_log.ldf')
GO

我期望更多类似这样的东西

CREATE DATABASE [$(DatabaseName)]
ON 
PRIMARY(NAME = [targetDBName], FILENAME = N'$(DefaultDataPath)$(DatabaseName).mdf')
LOG ON (NAME = [targetDBName_log], FILENAME = N'$(DefaultDataPath)$(DatabaseName)_log.ldf')
GO

，或者甚至

CREATE DATABASE [$(DatabaseName)]

我不会持续运行这个，所以我想让下一个人尽可能简单。项目属性中有很多部署选项，但我无法让它按照我想要的方式工作。

有人知道如何设置吗？

原文

I'm using VSTS Database Edition GDR Version 9.1.31024.02

I've got a project where we will be creating multiple databases with identical schema, on the fly, as customers are added to the system. It's one DB per customer. I thought I should be able to use the deploy script to do this. Unfortunately I always get the full filenames specified on the CREATE DATABASE statement. For example:

CREATE DATABASE [$(DatabaseName)]
ON 
PRIMARY(NAME = [targetDBName], FILENAME = N'$(DefaultDataPath)targetDBName.mdf')
LOG ON (NAME = [targetDBName_log], FILENAME = N'$(DefaultDataPath)targetDBName_log.ldf')
GO

I'd expected something more like this

CREATE DATABASE [$(DatabaseName)]
ON 
PRIMARY(NAME = [targetDBName], FILENAME = N'$(DefaultDataPath)$(DatabaseName).mdf')
LOG ON (NAME = [targetDBName_log], FILENAME = N'$(DefaultDataPath)$(DatabaseName)_log.ldf')
GO

Or even

CREATE DATABASE [$(DatabaseName)]

I'm not going to be running this on an on-going basis so I'd like to make it as simple as possible, for the next guy. There are a bunch of options for deployment in the project properties, but I can't get this to work the way I'd like.

Any one know how to set this up?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

沫雨熙 2024-07-16 21:01:11

迟到总比不到好，我知道如何从第二个示例中获取 $(DefaultDataPath)$(DatabaseName) 文件名。

您在第一个代码片段中显示的 SQL 表明您没有用于在 VSTS:DB 项目中创建数据库文件的脚本，可能是故意将它们从您所做的任何架构比较中排除。我发现它有点违反直觉，但解决方案是让 VSTS:DB 在您的开发环境中编写 MDF 和 LDF 脚本，然后编辑这些脚本以使用 SQLCMD 变量。

在您的数据库项目中，转到文件夹Schema Objects > 数据库级对象> 存储> 文件。在其中添加以下两个文件：

Database.sqlfile.sql

ALTER DATABASE [$(DatabaseName)]
ADD FILE (NAME = [$(DatabaseName)], 
    FILENAME = '$(DefaultDataPath)$(DatabaseName).mdf',
    SIZE = 2304 KB, MAXSIZE = UNLIMITED, FILEGROWTH = 1024 KB) 
    TO FILEGROUP [PRIMARY];

Database_log.sqlfile.sql

ALTER DATABASE [$(DatabaseName)]
    ADD LOG FILE (NAME = [$(DatabaseName)_log], 
    FILENAME = '$(DefaultDataPath)$(DatabaseName)_log.ldf', 
    SIZE = 1024 KB, MAXSIZE = 2097152 MB, FILEGROWTH = 10 %);

VSTS:DB 或 VSDBCMD.exe 的完整数据库创建脚本，生成现在将使用 SQLCMD 变量来命名 MDF 和 LDF 文件，允许您在命令行或 MSBuild 中指定它们。

Better late than never, I know how to get the $(DefaultDataPath)$(DatabaseName) file names from your second example.

The SQL you're showing in your first code snippet suggests that you don't have scripts for creating the database files in your VSTS:DB project, perhaps by deliberately excluded them from any schema comparisons you've done. I found it a little counter-intuitive, but the solution is to let VSTS:DB script the MDF and LDF in you development environment, then edit those scripts to use the SQLCMD variables.

In your database project, go to the folder Schema Objects > Database Level Objects > Storage > Files. In there, add these two files:

Database.sqlfile.sql

ALTER DATABASE [$(DatabaseName)]
ADD FILE (NAME = [$(DatabaseName)], 
    FILENAME = '$(DefaultDataPath)$(DatabaseName).mdf',
    SIZE = 2304 KB, MAXSIZE = UNLIMITED, FILEGROWTH = 1024 KB) 
    TO FILEGROUP [PRIMARY];

Database_log.sqlfile.sql

ALTER DATABASE [$(DatabaseName)]
    ADD LOG FILE (NAME = [$(DatabaseName)_log], 
    FILENAME = '$(DefaultDataPath)$(DatabaseName)_log.ldf', 
    SIZE = 1024 KB, MAXSIZE = 2097152 MB, FILEGROWTH = 10 %);

The full database creation script that VSTS:DB, or for that matter VSDBCMD.exe, generates will now use the SQLCMD variables for naming the MDF and LDF files, allowing you to specify them on the command line, or in MSBuild.

回复收藏 0 原文