从存储过程中创建新数据库

时间:2021-03-09 19:21:57

Due to an employee quitting, I've been given a project that is outside my area of expertise.

由于员工辞职,我获得了一个超出我专业领域的项目。

I have a product where each customer will have their own copy of a database. The UI for creating the database (licensing, basic info collection, etc) is being outsourced, so I was hoping to just have a single stored procedure they can call, providing a few parameters, and have the SP create the database. I have a script for creating the database, but I'm not sure the best way to actually execute the script.

我有一个产品,每个客户都有自己的数据库副本。用于创建数据库的UI(许可,基本信息收集等)正在外包,因此我希望只有一个可以调用的存储过程,提供一些参数,并让SP创建数据库。我有一个用于创建数据库的脚本,但我不确定实际执行脚本的最佳方法。

From what I've found, this seems to be outside the scope of what a SP easily can do. Is there any sort of "best practice" for handling this sort of program flow?

从我发现的情况来看,这似乎超出了SP容易做到的范围。处理这种程序流程有什么“最佳实践”吗?

2 个解决方案

#1


3  

Generally speaking, SQL scripts - both DML and DDL - are what you use for database creation and population. SQL Server has a command line interface called SQLCMD that these scripts can be run through - here's a link to the MSDN tutorial.

一般来说,SQL脚本(DML和DDL)都是用于数据库创建和填充的脚本。 SQL Server有一个名为SQLCMD的命令行界面,可以运行这些脚本 - 这里是MSDN教程的链接。

Assuming there's no customization to the tables or columns involved, you could get away with using either attach/reattach or backup/restore. These would require that a baseline database exist - no customer data. Then you use either of the methods mentioned to capture the database as-is. Backup/restore is preferrable because attach/reattach requires the database to be offline. But users need to be sync'd before they can access the database.

假设没有对所涉及的表或列进行自定义,您可以使用attach / reattach或backup / restore来逃避。这些将要求存在基线数据库 - 没有客户数据。然后使用上述任一方法按原样捕获数据库。备份/恢复是首选,因为附加/重新附加需要数据库脱机。但是用户需要先同步才能访问数据库。

#2


0  

If you got the script to create database, it is easy for them to use it within their program. Do you have any specific pre-requisite to create the database & set permissions accordingly, you can wrap up all the scripts within 1 script file to execute.

如果你有脚本来创建数据库,他们很容易在他们的程序中使用它。您是否有任何特定的先决条件来创建数据库并相应地设置权限,您可以将所有脚本包装在1个脚本文件中以执行。

#1


3  

Generally speaking, SQL scripts - both DML and DDL - are what you use for database creation and population. SQL Server has a command line interface called SQLCMD that these scripts can be run through - here's a link to the MSDN tutorial.

一般来说,SQL脚本(DML和DDL)都是用于数据库创建和填充的脚本。 SQL Server有一个名为SQLCMD的命令行界面,可以运行这些脚本 - 这里是MSDN教程的链接。

Assuming there's no customization to the tables or columns involved, you could get away with using either attach/reattach or backup/restore. These would require that a baseline database exist - no customer data. Then you use either of the methods mentioned to capture the database as-is. Backup/restore is preferrable because attach/reattach requires the database to be offline. But users need to be sync'd before they can access the database.

假设没有对所涉及的表或列进行自定义,您可以使用attach / reattach或backup / restore来逃避。这些将要求存在基线数据库 - 没有客户数据。然后使用上述任一方法按原样捕获数据库。备份/恢复是首选,因为附加/重新附加需要数据库脱机。但是用户需要先同步才能访问数据库。

#2


0  

If you got the script to create database, it is easy for them to use it within their program. Do you have any specific pre-requisite to create the database & set permissions accordingly, you can wrap up all the scripts within 1 script file to execute.

如果你有脚本来创建数据库,他们很容易在他们的程序中使用它。您是否有任何特定的先决条件来创建数据库并相应地设置权限,您可以将所有脚本包装在1个脚本文件中以执行。