Create new database as a user instance

Hello...
Is there any way to create a new database directly as a user instance. I guess this means creating a new mdf/ldf pair which is detached from the server after its created.
Thank you...

hi,

yse you can, but this does not mean the created database is detached... it's attached and available...

Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click Dim con As New SqlClient.SqlConnection("Server=.\SQLExpress;Database=master;Trusted_Connection=Yes;") con.Open() Dim cmd As New SqlClient.SqlCommand With cmd .CommandType = CommandType.Text .CommandTimeout = 5 .CommandText = "CREATE DATABASE UserInstanceDatabase;" .Connection = con End With cmd.ExecuteNonQuery() cmd.Dispose() cmd = Nothing con.ChangeDatabase("UserInstanceDatabase") cmd = New SqlClient.SqlCommand With cmd .CommandType = CommandType.Text .CommandTimeout = 5 .CommandText = "SELECT 1 AS [Id], 'Test' AS [Name] INTO dbo.Table1;" .Connection = con End With cmd.ExecuteNonQuery() cmd.Dispose() cmd = Nothing cmd = New SqlClient.SqlCommand With cmd .CommandType = CommandType.Text .CommandTimeout = 5 .CommandText = "SELECT * FROM dbo.Table1;" .Connection = con End With Dim rdr As SqlClient.SqlDataReader = cmd.ExecuteReader(CommandBehavior.SequentialAccess) While rdr.Read For iField As Integer = 0 To rdr.FieldCount - 1 Debug.WriteLine(rdr(iField)) Next Debug.WriteLine("") End While rdr.Close() rdr = Nothing cmd.Dispose() cmd = Nothing con.ChangeDatabase("master") cmd = New SqlClient.SqlCommand With cmd .CommandType = CommandType.Text .CommandTimeout = 5 .CommandText = "DROP DATABASE UserInstanceDatabase;" .Connection = con End With cmd.ExecuteNonQuery() cmd.Dispose() cmd = Nothing con.Dispose() con = Nothing End Sub

you can use the created database as soon as you created it..

BTW, the here used CREATE DATABASE syntax does not use the full CREATE DATABASE syntax to specify the actual files position, so they are created in the "standard" Data folder of the SQLExpress instance and not in the user's folder... you have to use the full syntax accordingly to your needs is you like the database to be "placed" in your account's folder..

regards

|||

hi and thanks for your help.

Your sample does not create a user instance but a plain attached database.

This means that is there is another database with the same name, I will get an error.

Aren't I right?

Isn't there a way to create the database without having it attached?

Thanks again...

|||

hi,

papadi wrote:

hi and thanks for your help.

Your sample does not create a user instance but a plain attached database.

nope... it creates a user instance bound to an existing database, in this case the master system database...

This means that is there is another database with the same name, I will get an error.

Aren't I right?

yep... you're rigth... but if you already have a database with the required name, you should not worry about creating it... you can just co right ahead and use it...

so, in a kinda of scenario, you start your application in your application's master database context (not the system master database), using the AttachDbFileName=|DataDirectory|\yourMasterApplicationDatabase.Mdf syntax of your connection string... you then verify the "alternate" database is available and eventually attach it (as at user instance start up it will be eventually available, but not attached) or create it if the physical files are not available...

at next application start up, you use again the AttachDbFileName=|DataDirectory|\yourMasterApplicationDatabase.Mdf syntax for the main connection and AttachDbFileName=Directory_Of\yourAdditionalDatabase.Mdf syntax for the additional database... I'd check for it's presence before opening the connection, but this should be the way to go..

Isn't there a way to create the database without having it attached?

nope..

SQL Server databases are not just files, they are a set of files bound to a logical, registered database in the master (instance system database) database.. you eventually have to create one and detach it ...

regards

|||

papadi wrote:

Hello...
Is there any way to create a new database directly as a user instance. I guess this means creating a new mdf/ldf pair which is detached from the server after its created.
Thank you...

why u need this....r u sure u want to create database per user ?..... or u want tables per user....

Madhu

|||

Dimitrius,

Could you give a bit more information about what you are trying to accomplish and why you want to use User Instances? User Instances are only available to a single user and you cannon connect to them remotely. If you are storing data for a single user application then User Instances may be right for you, but if you want to share your data, then you don't want to use User Instances.

One way to create a User Instance is simple to create a database as part of a VS project. Use the Add New Item functionality to create a database. This automatically creates the database in a User Instance and saves the database into your application project so that it gets deployed along with your code.

Mike

|||

Hi Mike,

I'm creating a database installer for a client/server application. The installer will run on server and I want to provide the end user (administrator of the application) with the option to create a database registered under an sql server instance (the classic way) or create a database by providing a path for an mdf file where the database will be created as a user instance. I know this database is accessible only by the local machine. This is no problem since only server code (ASP.NET Web Services app) can access the database.

So... one option would be to create an empty database and copy it if the end-user selects the option to create a user instance. The I could appy sql scripts to create my database. I dont want the database to already contain anything since I want my installer to be generic.

But what I'm actually looking for is... that code that creates the mdf/ldf pair from scratch, just like visual studio does when you add a database to an application using the 'Add New Item' functionality.

Do you think it's possible?

|||

hi,

papadi wrote:

But what I'm actually looking for is... that code that creates the mdf/ldf pair from scratch, just like visual studio does when you add a database to an application using the 'Add New Item' functionality.

Do you think it's possible?

a database is not just a pair of files... it is created as a logical "object" (in master database system tables) during the "creation" of the physical files and the operation is atomic... the created database is generated reflecting the destination server's "model" database, thus inheriting all settings and saved objects present in this system database... so you can not "just create" the files only...

so, theoretically, your "installer", depending on the user's choices, could connect to the master database of the "user Instance" or "standard instance"... you can then query the master database's physical position via the sys.master_files catalog view, which can return the actual storage file of the db.. you then strip the file name and get the actual folder...

SET NOCOUNT ON; SELECT REPLACE(physical_name, 'master.mdf','') AS [Data Folder] FROM sys.master_files WHERE database_id = 1 AND type = 0; --<-- Data Folder C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\DATA\ --or, for a User Instance, something similar to C:\Documents and Settings\Andrea\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS\

but this is even not mandatory, as when connecting to a user instance you already are in the "relative" "folder" scope, and actually the following is the result..

Dim con As New SqlClient.SqlConnection("Server=.\SQLExpress;Database=master;Trusted_Connection=Yes;User Instance=true;") con.Open() Dim cmd As New SqlClient.SqlCommand With cmd .CommandType = CommandType.Text .CommandTimeout = 5 .CommandText = "CREATE DATABASE UserInstanceDatabase;" .Connection = con End With cmd.ExecuteNonQuery() cmd.Dispose() cmd = Nothing cmd = New SqlClient.SqlCommand With cmd .CommandType = CommandType.Text .CommandTimeout = 5 .CommandText = "SELECT physical_name FROM sys.master_files WHERE name = 'UserInstanceDatabase';" .Connection = con End With Dim DataFolder As String = cmd.ExecuteScalar() cmd.Dispose() cmd = Nothing Debug.WriteLine(DataFolder) cmd = New SqlClient.SqlCommand With cmd .CommandType = CommandType.Text .CommandTimeout = 5 .CommandText = "DROP DATABASE UserInstanceDatabase;" .Connection = con End With cmd.ExecuteNonQuery() cmd.Dispose() cmd = Nothing con.Dispose() con = Nothing --<- C:\Documents and Settings\Andrea\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS\UserInstanceDatabase.mdf

and the db is created in the actual "User Instance"'s folder... having the User Instance full permissions on that folder you should not experiment permissions problem as well..

if you "turn" to "traditional instances", again, the data folder is respected with the traditional SQLExpress/SQL Server default data folder...

regards

|||

Hi Dimitrius,

What Andrea says is correct with one point of clarification...

The location where a User Instance database is stored changes depending on how you create it. If you handle the creation via SQL tools or using the User Instance connection string into master as Andrea describes above, the database will be created in the folder he specifies. If you create the database using the VS data tools to put the database in your project (Add New Item) the database is actually created in your project folder and deployed along with your application to a per-user file cache created by the VS ClickOnce installer.

Generally this distinction is academic as long as you are not using the |DataDirectory| keyword in your connection string. Using |DataDirectoy| indicates the per user file cache created during a ClickOnce installation, so if you use Andrea's script above, and then call |DataDirectory| as the file path for AttachDbFilename, you will get an error as the file doesn't not exist in that location.

I'm still not certain that User Instances are what you want based on your description. From your description I'm infering that you are creating a Web Service that will read data out of your database. The web service will be running on the same computer where SQL Express is running, so local access should not be an issue. My question is: What advantage do you believe going with User Instances will offer you? Here are my concerns:

A User Instance causes a second process of SQL Express to be running on your computer. When your web service is running, there will be two complete SQL Express instances running on your computer.