Copy Template Database

[CodeNoob1]

I am currently in the process of trying to develop an application in VB.NET 2008 with a SQL express DB. I have created the DB and feel I have all my tables, relationships etc ready to go.



This is what I am trying to do and I want to see if this is possible. I will need as much help with this as I can get.



1. When the user opens the program they will have an option to either 'Create a New' or 'Open Existing' project. What I need the program to do is (A) If the user chooses to 'Create a New' project, the template DB will be copied and the user will name this new DB based on string they input in textbox. Once this is completed then the connection is to this newly named DB. (B) If the user choses to 'Open Existing" then a form with listbox opens showing all DB names, the user selects the DB Name they want, and they click 'Open', this will open that specific database.



Note - I plan to have the SQL and program on the local computer, which will make this a stand alone program, requiring no internet or network connections. I could specify and create a specific folder at installation for all DB's to be stored on the computer, therefore having a static location.



Thanks in advance for all of your help.



Mark

 

[CodeNoob1]

I have been researching and have found the 'Attach' and 'Detach' SQL commands.

So to recap the above, it may be possible to follow this logic:

To Create a NEW DB from Template, I could 'Attach' Template DB to SQL Server, but I want to rename it based on string typed in textbox the user types. Then it makes that connection active.

To 'Open Existing', I need to query DB names on local SQL Instance, and when the DB name is selected, this makes that Database the active connection.

 

[Stonkie]

If all you need is the structure, you can use SQL Server Management Studio to export the structure as a script. The option is somewhere in the contextual menu when you right click on the database.

That doesn't work with the data though, so you will have to find or write some utility for that.

You can then run the script using the classes in the Microsoft.SqlServer.Management.Smo namespace (http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.aspx).

I think you can also use those classes to run sql queries like an ALTER DATABASE to change the database's name to that specified by the user.

One more thing, I've found that running massive scripts all at once seems to crash with an out of memory exception so you will have to split the scripts in smaller chunks.

I think you could also use those same utilities to copy the detached database files, attach them and rename the database to the name you like but I never tried that. Using scripts seemed more flexible.

 

[JohnH]

Sql servers have auto-attach, so copying the template db file and connecting to it by setting connection string should do it. See for example SQL Server (user) instances and AutoAttach DB blog post.