Create new database based on a template using SMO

Hi All,

I'm working on a web application where the user needs to be able to create and name new databases that are identical in structure to other existing databases (that is, all tables, stored procedures, functions, indexes, etc.). This is so that they can create a new database for each client and need to be able to do this through the web application. Having hunted around a fair bit, I've established that SMO is capable of doing pretty much everything that I want. The only problem is that everything I do seems to be based on the actual SQL Server and associated databases rather than the ones I have created in the App_Data folder.

The relevant code (so far) is:

Dim sqlServerAs New Server()With sqlServer.ConnectionContext .ServerInstance ="(local)" .Connect() .Disconnect()End WithFor Each dbAs DatabaseIn sqlServer.Databases ListView1.Items.Add(db.Name)NextDim newDatabaseAs New Database(sqlServer, DbName.Text.ToString)newDatabase.Create()

This does actaully create a new database, just not where I want it! Can anyone point me in the right direction as to how I can create a copy of a database in the App_Data folder?

Thanks & regards,

Paul

One general question first, will the server be running nothing but these databases? If so you may well be able to simplify the process by creating a template database within the model database. When a new database is created, it will be populated using objects in model.

The second issue is one of security as effectively sa permissions are required to create a new database. Your security concerns may be insufficient for this to be an issue, however you would be well advised to employ a level of indrection. Instead of letting the users directly trigger the create process, set up a queue table in a suitable location and have a windows service monitor this queue and create a database as required.

To find out what is required in the way of TSQL, just generate a database create script for an existing database inside (Enterprise Manager for SQL2000 and SQL Server Management Studio for SQL2005).

|||

Hi,

Thank you for your reply. I appreciate any help as I've struggled on this whole problem for a couple of days and making very little progress...

Anyway, at the moment the SQL Server is only being used for the client databases in this application, but I don't know how long that will continue to be the case.

As for the security issue, only Administrators on the Active Directory account will ultimately be able to access the page for creating databases. At the moment I'm just using site security, but will be changing this later to Active Directory.

I had already created a script file, but as this was several thousand lines, I'm rather hoping for a more manageable solution!

Thanks again,

Paul

|||Are you able to use multiple SQL Instances on that server? If so create an instance just for this application and you could use the model approach. I am glad that you have already considered security - for many applications, secuirity is an afterthought if it is thought of at all.|||

Hi,

I know that I should know, but I have no idea if I can create multiple instances of the server or not. Assuming that I can, what exactly is the model approach? How do I make fresh copies of the amended 'Model' database?

Thanks again,

Paul