Create from template database?

I have a database that needs to be created over and over (with dozens of pre-populated tables & procedures) for various users of a software application. These databases must remain distinct. Currently, the table/procedure data is maintained in Create and Insert scripts (approx 15,000 lines total) that we run as needed, but maintaining these scripts in unwieldy at best.

Ideally, I would like to create each new database from a template database. This way, we could forget the scripts entirely, and make future modifications directly to the template, and have them automatically bubble into all future databases created from it.

I know this is how the "model" system database works, and think it is great! Make some change in model, and every new database is created with that change. Perfect, except that we cannot guarantee that our software application will be the only one using some instance of SQL (SS2K5), or that our databases will be the only ones inheriting from model.

So, instead of having EVERY new database inherit from model, is there a way to specify that only certain databases inherit from 'model' while others inherit from 'custom_model'? Is there a way to specify a database to use some template in the create process (maybe similar to in postgresql)? Ideas? Thanks, -Matthew_53

Unfortunately, there is no 'conditional' way to use multiple [Model] databases.

However, you can easily create your 'template' database, and when you need another database derived from that template. In the object explorer of SSMS, right-click on the template database and select [Tasks], then select [Copy Database...]. Follow the steps throught the Copy Database Wizard.

|||Right, but is there a way to do this programatically, from within our application, whenever we need to generate a 'new' database?|||Programmatically, you could DETACH the template database, then xcopy it with a new db name, and then re-ATTACH both of them. (Actually, unless you need to continually 'tweak' the template database, it doesn't even need to be attached. Just copy the *.mdf file to a new name and ATTACH the new database.)

Create from template database

I have a database that needs to be created over and over (with dozens of pre-populated tables & procedures) for various users of a software application. These databases must remain distinct. Currently, the table/procedure data is maintained in Create and Insert scripts (approx 15,000 lines total) that we run as needed, but maintaining these scripts in unwieldy at best.

Ideally, I would like to create each new database from a template database. This way, we could forget the scripts entirely, and make future modifications directly to the template, and have them automatically bubble into all future databases created from it.

I know this is how the "model" system database works, and think it is great! Make some change in model, and every new database is created with that change. Perfect, except that we cannot guarantee that our software application will be the only one using some instance of SQL (SS2K5), or that our databases will be the only ones inheriting from model.

So, instead of having EVERY new database inherit from model, is there a way to specify that only certain databases inherit from 'model' while others inherit from 'custom_model'? Is there a way to specify a database to use some template in the create process (similar to in postgresql)? Ideas? Thanks, -Matthew_53

Just thinking out loud really, but you could install a second SQL Server instance (possibly on the same server - limiting the memory available to the second instance, if appropriate) and maintain the model database within that instance.

Your database creation process would then be to create the database on the new instance, detach, then attach to the new instance - you could do this by running just one script per instance or even by setting up a linked server and performing the actions with a single script. If you set the default Data and Log folders to the same folders as the existing instance then you wouldn't even have to move any files around.

If you lock down the security on the second instance you can limit who has access to modify the model database.

Chris

|||Better yet, why not just create the template/model database on the one instance, then detach it and store the data and log files away for safe keeping? Then any time you wanted to clone it, it would only be a matter of duplicating these files into the appropriate data directories, and attaching them with the desired database name.

You could also achieve the same effect using a backup/restore, as long as you're using the WITH MOVE clause during the restore. This way, you could leave the template database online and maintain it easily, at the expense of increased cloning times.

Either approach could be made pretty turn-key with some scripts containing an appropriate mix of BACKUP, RESTORE, sp_attach, sp_detach, xp_cmdshell (to copy files), etc.

Create Database using C#

Hi,
I write software in C# that should create (by itself) a new database.
The SQL server should be installed on the customer's computers silently
(I still need to figure how to do this part)
How can my software create a database that only it can access? Do I
need to create a special user account from the software?
What user account do I use to open a connection to the SQL server in
the first place when I create the database (taking under consideration
that the SQL server was installed silently and as far as the user
concern he has no idea my software uses SQL server).
To summarize:
1.The install of my software should install and configure the SQL
server silently (appreciate any help here)
2.The software needs to access the local SQL server and create a
database that only it can access when the software runs under a simple
"USER" account in Win2K/XP and not "ADMINISTRATOR" (my big problem -
any idea?)
Thanks in advance,
Oren
"orenbt78" <orenbt78@.googlemail.com> wrote in message
news:1164123542.578586.6280@.j44g2000cwa.googlegrou ps.com...
> Hi,
> I write software in C# that should create (by itself) a new database.
> The SQL server should be installed on the customer's computers silently
> (I still need to figure how to do this part)
> How can my software create a database that only it can access? Do I
> need to create a special user account from the software?
> What user account do I use to open a connection to the SQL server in
> the first place when I create the database (taking under consideration
> that the SQL server was installed silently and as far as the user
> concern he has no idea my software uses SQL server).
> To summarize:
> 1. The install of my software should install and configure the SQL
> server silently (appreciate any help here)
> 2. The software needs to access the local SQL server and create a
> database that only it can access when the software runs under a simple
> "USER" account in Win2K/XP and not "ADMINISTRATOR" (my big problem -
> any idea?)
> Thanks in advance,
> Oren
>
See
Embedding SQL Server Express into Custom Applications
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsse/html/emsqlexcustapp.asp
User Instances for Non-Administrators
http://msdn2.microsoft.com/en-us/library/ms143684.aspx
David
|||Thanx for the reply.
The "Embedding SQL Server Express into Custom Applications" seems to be
very helpful.
However, I can't figure out yet how to solve my problem with creating
databases as a simply user.
I plan to create a new database every year and let the user to view
last year's data while I am updating this year's data. That means 2
connections at the same time.
In the "User Instances for Non-Administrators" document it says "One
user can only have one user instance". So how can I do that?
And how do I do it in C# code?
Thanks again,
Oren
|||"orenbt78" <orenbt78@.googlemail.com> wrote in message
news:1164565596.943159.13410@.14g2000cws.googlegrou ps.com...
> Thanx for the reply.
> The "Embedding SQL Server Express into Custom Applications" seems to be
> very helpful.
> However, I can't figure out yet how to solve my problem with creating
> databases as a simply user.
> I plan to create a new database every year and let the user to view
> last year's data while I am updating this year's data. That means 2
> connections at the same time.
> In the "User Instances for Non-Administrators" document it says "One
> user can only have one user instance". So how can I do that?
> And how do I do it in C# code?
An instance is an instance of the database engine. A single instance of the
database engine can have multiple databases.
And in C# you just send TSQL commands using a
System.Data.SqlClient.SqlCommand object. You can get the syntax for the
commands from SQL Server Books Online.
TDavid
|||I have managed to create the database file (code below). I am not sure
it is the right way.
I would like to create a password for these database so only my
software will be able to control it (change data). how do i do that?
tmpConn.ConnectionString = "Data Source=(local); DATABASE =
master;Integrated Security=True; user instance=true";
sqlCreateDBQuery = " CREATE DATABASE " + DBParam.DatabaseName + " ON
PRIMARY "
+ " (NAME = " + DBParam.DataFileName +", "
+ " FILENAME = '" + DBParam.DataPathName +"', "
+ " SIZE = 5MB,"
+ "FILEGROWTH =" + DBParam.DataFileGrowth +") "
+ " LOG ON (NAME =" + DBParam.LogFileName +", "
+ " FILENAME = '" + DBParam.LogPathName + "', "
+ " SIZE = 1MB, "
+ "FILEGROWTH =" + DBParam.LogFileGrowth +") ";
SqlCommand myCommand = new SqlCommand(sqlCreateDBQuery, tmpConn);
try
{
tmpConn.Open();
MessageBox.Show(sqlCreateDBQuery);
myCommand.ExecuteNonQuery();
MessageBox.Show("Database has been created successfully!", "Create
Database", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (System.Exception ex)
{
MessageBox.Show(ex.ToString(), "Create Database",
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally
{
tmpConn.Close();
}
|||anyone? any idea?

Create Database using C#

Hi,
I write software in C# that should create (by itself) a new database.
The SQL server should be installed on the customer's computers silently
(I still need to figure how to do this part)
How can my software create a database that only it can access? Do I
need to create a special user account from the software?
What user account do I use to open a connection to the SQL server in
the first place when I create the database (taking under consideration
that the SQL server was installed silently and as far as the user
concern he has no idea my software uses SQL server).
To summarize:
1. The install of my software should install and configure the SQL
server silently (appreciate any help here)
2. The software needs to access the local SQL server and create a
database that only it can access when the software runs under a simple
"USER" account in Win2K/XP and not "ADMINISTRATOR" (my big problem -
any idea?)
Thanks in advance,
Oren"orenbt78" <orenbt78@.googlemail.com> wrote in message
news:1164123542.578586.6280@.j44g2000cwa.googlegroups.com...
> Hi,
> I write software in C# that should create (by itself) a new database.
> The SQL server should be installed on the customer's computers silently
> (I still need to figure how to do this part)
> How can my software create a database that only it can access? Do I
> need to create a special user account from the software?
> What user account do I use to open a connection to the SQL server in
> the first place when I create the database (taking under consideration
> that the SQL server was installed silently and as far as the user
> concern he has no idea my software uses SQL server).
> To summarize:
> 1. The install of my software should install and configure the SQL
> server silently (appreciate any help here)
> 2. The software needs to access the local SQL server and create a
> database that only it can access when the software runs under a simple
> "USER" account in Win2K/XP and not "ADMINISTRATOR" (my big problem -
> any idea?)
> Thanks in advance,
> Oren
>
See
Embedding SQL Server Express into Custom Applications
http://msdn.microsoft.com/library/d...>
xcustapp.asp
User Instances for Non-Administrators
http://msdn2.microsoft.com/en-us/library/ms143684.aspx
David|||Thanx for the reply.
The "Embedding SQL Server Express into Custom Applications" seems to be
very helpful.
However, I can't figure out yet how to solve my problem with creating
databases as a simply user.
I plan to create a new database every year and let the user to view
last year's data while I am updating this year's data. That means 2
connections at the same time.
In the "User Instances for Non-Administrators" document it says "One
user can only have one user instance". So how can I do that?
And how do I do it in C# code?
Thanks again,
Oren|||"orenbt78" <orenbt78@.googlemail.com> wrote in message
news:1164565596.943159.13410@.14g2000cws.googlegroups.com...
> Thanx for the reply.
> The "Embedding SQL Server Express into Custom Applications" seems to be
> very helpful.
> However, I can't figure out yet how to solve my problem with creating
> databases as a simply user.
> I plan to create a new database every year and let the user to view
> last year's data while I am updating this year's data. That means 2
> connections at the same time.
> In the "User Instances for Non-Administrators" document it says "One
> user can only have one user instance". So how can I do that?
> And how do I do it in C# code?
An instance is an instance of the database engine. A single instance of the
database engine can have multiple databases.
And in C# you just send TSQL commands using a
System.Data.SqlClient.SqlCommand object. You can get the syntax for the
commands from SQL Server Books Online.
TDavid|||I have managed to create the database file (code below). I am not sure
it is the right way.
I would like to create a password for these database so only my
software will be able to control it (change data). how do i do that?
tmpConn.ConnectionString = "Data Source=(local); DATABASE =
master;Integrated Security=True; user instance=true";
sqlCreateDBQuery = " CREATE DATABASE " + DBParam.DatabaseName + " ON
PRIMARY "
+ " (NAME = " + DBParam.DataFileName +", "
+ " FILENAME = '" + DBParam.DataPathName +"', "
+ " SIZE = 5MB,"
+ " FILEGROWTH =" + DBParam.DataFileGrowth +") "
+ " LOG ON (NAME =" + DBParam.LogFileName +", "
+ " FILENAME = '" + DBParam.LogPathName + "', "
+ " SIZE = 1MB, "
+ " FILEGROWTH =" + DBParam.LogFileGrowth +") ";
SqlCommand myCommand = new SqlCommand(sqlCreateDBQuery, tmpConn);
try
{
tmpConn.Open();
MessageBox.Show(sqlCreateDBQuery);
myCommand.ExecuteNonQuery();
MessageBox.Show("Database has been created successfully!", "Create
Database", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (System.Exception ex)
{
MessageBox.Show(ex.ToString(), "Create Database",
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally
{
tmpConn.Close();
}|||anyone? any idea?

Create Database using C#

Hi,
I write software in C# that should create (by itself) a new database.
The SQL server should be installed on the customer's computers silently
(I still need to figure how to do this part)
How can my software create a database that only it can access? Do I
need to create a special user account from the software?
What user account do I use to open a connection to the SQL server in
the first place when I create the database (taking under consideration
that the SQL server was installed silently and as far as the user
concern he has no idea my software uses SQL server).
To summarize:
1. The install of my software should install and configure the SQL
server silently (appreciate any help here)
2. The software needs to access the local SQL server and create a
database that only it can access when the software runs under a simple
"USER" account in Win2K/XP and not "ADMINISTRATOR" (my big problem -
any idea?)
Thanks in advance,
Oren"orenbt78" <orenbt78@.googlemail.com> wrote in message
news:1164123542.578586.6280@.j44g2000cwa.googlegroups.com...
> Hi,
> I write software in C# that should create (by itself) a new database.
> The SQL server should be installed on the customer's computers silently
> (I still need to figure how to do this part)
> How can my software create a database that only it can access? Do I
> need to create a special user account from the software?
> What user account do I use to open a connection to the SQL server in
> the first place when I create the database (taking under consideration
> that the SQL server was installed silently and as far as the user
> concern he has no idea my software uses SQL server).
> To summarize:
> 1. The install of my software should install and configure the SQL
> server silently (appreciate any help here)
> 2. The software needs to access the local SQL server and create a
> database that only it can access when the software runs under a simple
> "USER" account in Win2K/XP and not "ADMINISTRATOR" (my big problem -
> any idea?)
> Thanks in advance,
> Oren
>
See
Embedding SQL Server Express into Custom Applications
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsse/html/emsqlexcustapp.asp
User Instances for Non-Administrators
http://msdn2.microsoft.com/en-us/library/ms143684.aspx
David|||Thanx for the reply.
The "Embedding SQL Server Express into Custom Applications" seems to be
very helpful.
However, I can't figure out yet how to solve my problem with creating
databases as a simply user.
I plan to create a new database every year and let the user to view
last year's data while I am updating this year's data. That means 2
connections at the same time.
In the "User Instances for Non-Administrators" document it says "One
user can only have one user instance". So how can I do that?
And how do I do it in C# code?
Thanks again,
Oren|||"orenbt78" <orenbt78@.googlemail.com> wrote in message
news:1164565596.943159.13410@.14g2000cws.googlegroups.com...
> Thanx for the reply.
> The "Embedding SQL Server Express into Custom Applications" seems to be
> very helpful.
> However, I can't figure out yet how to solve my problem with creating
> databases as a simply user.
> I plan to create a new database every year and let the user to view
> last year's data while I am updating this year's data. That means 2
> connections at the same time.
> In the "User Instances for Non-Administrators" document it says "One
> user can only have one user instance". So how can I do that?
> And how do I do it in C# code?
An instance is an instance of the database engine. A single instance of the
database engine can have multiple databases.
And in C# you just send TSQL commands using a
System.Data.SqlClient.SqlCommand object. You can get the syntax for the
commands from SQL Server Books Online.
TDavid|||I have managed to create the database file (code below). I am not sure
it is the right way.
I would like to create a password for these database so only my
software will be able to control it (change data). how do i do that?
tmpConn.ConnectionString = "Data Source=(local); DATABASE =master;Integrated Security=True; user instance=true";
sqlCreateDBQuery = " CREATE DATABASE " + DBParam.DatabaseName + " ON
PRIMARY "
+ " (NAME = " + DBParam.DataFileName +", "
+ " FILENAME = '" + DBParam.DataPathName +"', "
+ " SIZE = 5MB,"
+ " FILEGROWTH =" + DBParam.DataFileGrowth +") "
+ " LOG ON (NAME =" + DBParam.LogFileName +", "
+ " FILENAME = '" + DBParam.LogPathName + "', "
+ " SIZE = 1MB, "
+ " FILEGROWTH =" + DBParam.LogFileGrowth +") ";
SqlCommand myCommand = new SqlCommand(sqlCreateDBQuery, tmpConn);
try
{
tmpConn.Open();
MessageBox.Show(sqlCreateDBQuery);
myCommand.ExecuteNonQuery();
MessageBox.Show("Database has been created successfully!", "Create
Database", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (System.Exception ex)
{
MessageBox.Show(ex.ToString(), "Create Database",
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally
{
tmpConn.Close();
}|||anyone? any idea?