Unable to backup database in UserInstance

Question

I've been struggling with this for a few days now, so I guess it's time to ask for help.  I'm working on a VB Express 2005 Single form windows application that uses a SQL Express 2005 two table data base.  I can read and write to the database with no problems.  I just can't back it up.  I haven't tried restores yet.  I created the database in SQL Server Management Studio Express and then connected to it from the VB Express application.

My.Settings.UsersConnectionString is

Data Source=.\sqlexpress;AttachDbFilename="C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Users.mdf";Integrated Security=True;User Instance=True

I used  the following code to establish a connection to the server that is the userinstance

Dim SqlConnection As SqlClient.SqlConnection = New SqlClient.SqlConnection(My.Settings.UsersConnectionString)

Dim ServerConnection As ServerConnection = New Microsoft.SqlServer.Management.Common.ServerConnection(SqlConnection)

Dim srv As Server = New Server(ServerConnection)

I am able to confirm the connection to the UserInstance via a message box that displays the instance name

MsgBox(srv.InstanceName.ToString)

Then this code follows:

Dim db As Database

db = srv.Databases("USERS")

Dim bk As New Backup

'Specify the type of backup, the description, the name, and the database to be backed up.

bk.Action = BackupActionType.Database

bk.BackupSetDescription = "Full backup of Users"

bk.BackupSetName = "Users Backup"

bk.Database = "USERS"

'Declare a BackupDeviceItem by supplying the backup device file name in the constructor, and the type of device is a file.

Dim bdi As BackupDeviceItem

bdi = New BackupDeviceItem(AppDir & "Test_Full_Backup1.bak", DeviceType.File)

'Add the device to the Backup object.

bk.Devices.Add(bdi)

bk.Initialize = True

'Set the Incremental property to False to specify that this is a full database backup.

bk.Incremental = False

'Set the expiration date.

Dim backupdate As New Date

backupdate = New Date(2008, 10, 5)

bk.ExpirationDate = backupdate

'Specify that the log must be truncated after the backup is complete.

bk.LogTruncation = BackupTruncateLogType.Truncate

'Run SqlBackup to perform the full database backup on the instance of SQL Server.

bk.SqlBackup(srv)

'Inform the user that the backup has been completed.

MsgBox("Full Backup complete.")

Unfortunately, the line bk.SLQBackup(srv) errors off advising that -  Microsoft.SqlServer.Management.Common.ExecutionFailureException {"An exception occurred while executing a Transact-SQL statement or batch."} Microsoft.SqlServer.Management.Common.ExecutionFailureException

and that

+  InnerException {"Could not locate entry in sysdatabases for database 'USERS'. No entry found with that name. Make sure that the name is entered correctly.
BACKUP DATABASE is terminating abnormally."} System.Exception

So I went to SQL Server Management Studio Express and ran the query

use master

select * from sysdatabases

Among other lines the query returned

USERS 5 0x010500000000000515000000358A021AFA4F0C2F828BA628AD8A0200 0 1073807369 1627389952 2007-03-16 10:04:42.320 1900-01-01 00:00:00.000 0 90 C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Users.mdf NULL

so it appears to me that the entry for database USERS is really there.

Any ideas as to what I need to do so that I can add a program feature to backup the data are greatly appreciated.

Thank you,

MF

Answer 1

Hi MF,

You've mixed your coding between a User Instance and the parent instance, which is why you're having problems. A user instance is a completely separate process of SQL Express that doesn't not share anything with the parent instance.

I created the database in SQL Server Management Studio Express and then connected to it from the VB Express application.

SSMSE is connecting to the parent instance, which is where you database was created. This is not the User Instance.

My.Settings.UsersConnectionString is Data Source=.\sqlexpress;AttachDbFilename="C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Users.mdf";Integrated Security=True;User Instance=True

This is where things are getting mixed up. The database created was in the parent instance as I said, which defaults to the Data directory you see here. When I followed the steps of creating a database in SSMSE and then trying to make a connection to it from VS as you've suggested, I actually get an error stating that the database is in use, which is exactly what should happen. I'm not sure how you were even able to create this connection in the first place.

bk.Database = "USERS"

This is where the problem occurs. The connection string you specified does not create a database named 'USERS' in the user instance. When you use AttachDbFilename, SQL Express generates a name for the database on the fly based on the source path for the file. When your code tries to connect to this database by name, it fails because the database doesn't exist. To do this in a user instance, you will need to determine the name of the database at runtime.

So I went to SQL Server Management Studio Express and ran the query use master select * from sysdatabases Among other lines the query returned USERS 5 0x010500000000000515000000358A021AFA4F0C2F828BA628AD8A0200 0 1073807369 1627389952 2007-03-16 10:04:42.320 1900-01-01 00:00:00.000 0 90 C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Users.mdf NULL so it appears to me that the entry for database USERS is really there.

As I pointed out earlier, you are not connected to the user instance here, SSMSE connects to the parent instance, which is different. Check out this blog post I wrote about how to connect to a user instance from SSMSE. Hopefully this will give you enough background to sort out when instance you are using and when.

Mike

Answer 2

Thank you Mike.  I read the blog post and think that I now understand a bit better about the relation (or lack thereof) between the parent instance and the userinstance.  Now, comes the task of cleaning up my mess.  Am I correct in thinking that the correct solution is to now:

1) Delete the database files from both my VB Project and from SSMSE

2) Recreate the database in the Visual Basic IDE with the attachdb syntax and making it a Userinstance

Thanks,

MF

Answer 3

That will work, but you don't actually have to take such dramatic action as deleting the database, especially if you have data in it.

1. Open up SSMSE as normal (pointing to the parent instance).
2. Detach the USERS database from the parent isntance.
3. Jump over to VB and use the Project | Add Existing Item menu command.
4. Navigate to the database you just detached. It's still in the Data directory.
5. Follow through the rest of the wizard.

This will actually make a copy of the USERS database into your project directory and build a DataSet against it. You can throw away the DataSet if you don't need it. Once you've done that, you'll have a private copy of the database for use in your project and VB will have created a connection string for it.

Mike

Answer 4

Thanks, Mike

I opened SSMSE and detached the database.  As I also have a copy of the database in my C:\Documents and Settings\Mark\My Documents\Visual Studio 2005\Projects\Working copy of NCS\NCS\NCS.mdf which is still working with my application, I deleted the copy in the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data folder. 

I don't think that I need to do steps 3, 4 and 5 as I think this part of the VB app is still good.  If not, please advise and I'll delete and readd the mdf file.

However, I'm still confused.  You said that:

"This is where the problem occurs. The connection string you specified does not create a database named 'USERS' in the user instance. When you use AttachDbFilename, SQL Express generates a name for the database on the fly based on the source path for the file. When your code tries to connect to this database by name, it fails because the database doesn't exist. To do this in a user instance, you will need to determine the name of the database at runtime."

How do I determine the name of the database at runtime and connect to that darabase?  It was my difficulty in doing that that led me into this mess.

Thanks for your help.  I really appreciate your time.  I was about to buy Rogaine so I'd have some more hair to pull out.

MF

Answer 5

I was afraid you were going to ask that, cause I don't know... Give me a couple minutes, I'll go figure it out...

Mike

Answer 6

OK, easier than I thought, this should work:

Dim SqlConnection As SqlClient.SqlConnection = New SqlClient.SqlConnection(My.Settings.UsersConnectionString)
SqlConnection.Open()
MsgBox(SqlConnection.Database.ToString())

That should actually return the autoname that was assigned to the database when it was attached. Pardon if my code isn't exact, I'm translating from C# to VB on the fly.

Mike

Answer 7

Thank you, Mike.  Your VB looks fine, but I'm still having a problem.  I should mention that I'm home now and using a different machine that has the same problem.  In the office I was using the Users database.  Here, I'm using the NCS database, but the situation and the problem is the same.  I used the following code:

Dim SqlConnection As SqlClient.SqlConnection = New SqlClient.SqlConnection(My.Settings.NCSConnectionString)

SqlConnection.Open()

MsgBox(SqlConnection.Database.ToString())

and got the following

C:\Documents and Settings\Marcos\My Documents\Visual Studio 2005\Projects\Working Copy of NCS\NCS\Bin\Debug\NCS,MDF

The rest of the code is

Dim srv As Server

srv = New Server(SqlConnection.Database.ToString())

Dim bk As New Backup

'Specify the type of backup, the description, the name, and the database to be backed up.

bk.Action = BackupActionType.Database

bk.BackupSetDescription = "Full backup of NCS"

bk.BackupSetName = "NCS Backup"

bk.Database = "NCS"

'Declare a BackupDeviceItem by supplying the backup device file name in the constructor, and the type of device is a file.

Dim bdi As BackupDeviceItem

bdi = New BackupDeviceItem(My.Settings.DefaultBackUpPath & "\NCS_Full_Backup1.bak", DeviceType.File)

'Add the device to the Backup object.

bk.Devices.Add(bdi)

'Set the Incremental property to False to specify that this is a full database backup.

bk.Incremental = False

'Set the expiration date.

Dim backupdate As New Date

backupdate = New Date(2008, 10, 5)

bk.ExpirationDate = backupdate

'Specify that the log must be truncated after the backup is complete.

bk.LogTruncation = BackupTruncateLogType.Truncate

'Run SqlBackup to perform the full database backup on the instance of SQL Server.

bk.SqlBackup(srv)

'Inform the user that the backup has been completed.

MsgBox("Full Backup complete.")

Unfortunately, the end result is still an error at the line bk.SqlBackup(srv).  Here's the error info:

-  InnerException {"Failed to connect to server C:\DOCUMENTS AND SETTINGS\MARCOS\MY DOCUMENTS\VISUAL STUDIO 2005\PROJECTS\WORKING COPY OF NCS\NCS\BIN\DEBUG\NCS.MDF."} System.Exception

-  InnerException {"An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)"} System.Exception

Thanks,

MF

 

 

Answer 8

Hi MF,

You've got your Server and Database context mixed up. The code I proved will return the name of the Database, which you should use to set the Database property of the Server object. You've used the database name to try and set the Server object.

Problem is here: srv = New Server(SqlConnection.Database.ToString())

You had this line correct in the first batch of sample code you provided, change it back. The place to use the name of the database is when setting the database for the backup object, so...

bk.Database = "NCS"

should be...

bk.Database = SqlConnection.Database.ToString()

Just to clarify, there is no database on the user instance named NCS, the database is actually named usng the full path to the database file. The Server name is different and from your previous code sample you were correctly returning that. Good luck.

Mike

Answer 9

Thank you very much, Mike.  I was hopelessly confused about how to use the User Instance.  I had read the Tech article SQL Server 2005 Express Edition User Instances page 4, Opening a User Instance Connection and got to thinking that I needed to use the parent instance and a named pipe.  Thanks to your guidance, I was able to backup the User Instance database.  In retrospect, it seems simple.  It looks like all I really had to do was:

Create a SQLConnection using the path specified in My.Settings for the data.

Create a SQLServer.Management.Common.ServerConnection that uses that SQLConnection

Create a Server that uses that SQLServer.Management.Common.ServerConnection

and then to obtain the name of the database all I would need to do is get the filesystem path that the SQLConnection was using by using the code SQLConnection.Database.ToString

Thanks again, Mike

MF

 

Answer 10

Glad to have helped. It was a good excersize to go through since creating back-ups is tremendously important. Now your effort is immortalized on the forum for everyone to benefit from.

Mike

Answer 11

Mike Wachal - MSFT wrote:

Hi MF, You've mixed your coding between a User Instance and the parent instance, which is why you're having problems. A user instance is a completely separate process of SQL Express that doesn't not share anything with the parent instance. I created the database in SQL Server Management Studio Express and then connected to it from the VB Express application. SSMSE is connecting to the parent instance, which is where you database was created. This is not the User Instance. My.Settings.UsersConnectionString is Data Source=.\sqlexpress;AttachDbFilename="C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Users.mdf";Integrated Security=True;User Instance=True This is where things are getting mixed up. The database created was in the parent instance as I said, which defaults to the Data directory you see here. When I followed the steps of creating a database in SSMSE and then trying to make a connection to it from VS as you've suggested, I actually get an error stating that the database is in use, which is exactly what should happen. I'm not sure how you were even able to create this connection in the first place. bk.Database = "USERS" This is where the problem occurs. The connection string you specified does not create a database named 'USERS' in the user instance. When you use AttachDbFilename, SQL Express generates a name for the database on the fly based on the source path for the file. When your code tries to connect to this database by name, it fails because the database doesn't exist. To do this in a user instance, you will need to determine the name of the database at runtime. So I went to SQL Server Management Studio Express and ran the query use master select * from sysdatabases Among other lines the query returned USERS 5 0x010500000000000515000000358A021AFA4F0C2F828BA628AD8A0200 0 1073807369 1627389952 2007-03-16 10:04:42.320 1900-01-01 00:00:00.000 0 90 C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Users.mdf NULL so it appears to me that the entry for database USERS is really there. As I pointed out earlier, you are not connected to the user instance here, SSMSE connects to the parent instance, which is different. Check out this blog post I wrote about how to connect to a user instance from SSMSE. Hopefully this will give you enough background to sort out when instance you are using and when. Mike

Hi,  I came across this formum after coming across the same problem listed here and am reading what you both have posted but am struggling to understand the concepts.

Are you saying that when I am developing my application that also has a single .mdf file that this is the parent instance?  When I press F5 to run from the IDE I am using a parent instance?

And then when I use clickonce to deploy the application it is then a user instance?

I know the answer to my problem is held within this thread but the problem is trying to understand it?

Any chance you could talk me through it?  I would be very grateful!

Answer 12

I'm not sure exactly where your difficulty lies.  You may want to post some code or some specific questions.  For me, I found some of the problem was some of the terminology.  For instance, the term "parent".  I found that things became easier when I stopped trying to figure out what things were called and started to focus on how they worked.  Here's some of what I learned that may (or may not ) help.  I can't claim a high degree of accuracy here as I'm just learning this too.  Hopefully, someone will correct any mistake I've made.  

 

 SQLExpress, once installed, provides the underlying database engine, much in the same way Jet did for MS Access.  After installing it, I didn't need to launch SQL Server Management Studio Express.  I found it best to avoid using any connection strings that had anything to do with mdf files in the MSSQL data folder and finally learned not to attempt to use any MDF files that were attached to SQLExpress.  I believe that this underlying SQLExpress installation is the parent but that the individual mdf files we use in our apps are instances.   If you really want to see what's going on in the userinstances, don't use SSMSE, use the SSEUtil.

 

 In developing my application, it took a while to learn that there were multiple copies of my mdf file.  In the interim, I couldn't tell if my database updates worked or not.  I finally caught on that the mdf in my application development folder was overwriting the copy in the debug folder each time I hit f5 and that if I wanted to see what was really going on in the database while debugging, I had to add the debug folder mdf to my database explorer in VB Express 2005.

 

When it came time to write my restore code, I learned that I needed to use the master database as indicated in the preceding part of this thread.

 

When it came time to deploy via Clickonce, I had decided that in order to avoid overwriting existing data when doing version updates and to faciliate database schema changes, that I would not include my mdf file as a distributable.  I chose to use SMO and T-SQL code to create the database as a part of the app start up.  When I did that, I needed to code connecting to .\SQLExpress master DB with UserInstance-true and then running the code to create the database and table.  After creating the db and tables, I had to use the commands to detach the database and then to reconnect to the mdf file using again UserInstance=true.

So, I guess that what I am saying is "No".  I think that when you are developing your application that has a single .mdf file and a connection string that says useinstance=true, that this is not the parent instance.  I would say that the SQLExpress engine is the parent instance and that the userinstance is simply leveraging the dlls and services that the SQLExpress install makes available.  It is a userinstance as is any db you use with UserInstance= true in the connection string.  I also think that when you press F5 to run from the IDE, you are still using the same type of Userinstance, except that now, the mdf file is not the one in your development folder, its the one in the debug folder.  When you use clickonce to deploy the application,  it is still a user instance except that now, the mdf file is in yet a third location.  Write down the path to the mdf file in your IDE that the properties box displays.  Then put a message box in your application that displays the value of Appdomain.CurrrentDomain.GetData("DataDirectory") .  Run the app in the debug environment and write that path down.  Use a ClickOnce deployment and write down the path of the mdf you see after deployment.  You will see that the location of your mdf file changes from when you code to when you debug to when you deploy and since they all use the same connection string, they are all userinstances to the same mdf file.  Only the file path changes.

 

Good Luck,

MF

 

Answer 13

Hello Mike

 

I have read everthing in this topic, but I cannot create a backup using the method described in this topic.  The error I get is a FailedOperationException was unhandled:

Backup failed for Server '091B68B7503417C4C8798F2E64D7F3F5_L STUDIO 2005\PROJECTS\PLM\PLM_PERSONAL\BIN\DEBUG\LM_SQL.MDF'.

 

My connection string: Data Source=.\SQLEXPRESS;AttachDbFilename="C:\Documents and Settings\Andre Light\My Documents\Visual Studio 2005\Projects\PLM\PLM_Personal\LM_SQL.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True

 

Here is my code (I think I've included everything from the topic):

<Start Code>

Dim SqlCon As SqlClient.SqlConnection = New SqlClient.SqlConnection(My.Settings.LM_SQLConStr)

SqlCon.Open()

Console.WriteLine(SqlCon.Database.ToString())

'The value of SqlCon.Database.ToString():

' 091B68B7503417C4C8798F2E64D7F3F5_L STUDIO 2005\PROJECTS\PAIDLEAVEMANAGER_PERSONAL\PAIDLEAVEMANAGER_PERSONAL\BIN\DEBUG\LM_SQL.MDF

Dim srv As Server

srv = New Server(SqlCon.Database.ToString())

Dim bk As New Backup

'Specify the type of backup, the description, the name, and the database to be backed up.

bk.Action = BackupActionType.Database

bk.BackupSetDescription = "Full Backup_QL"

bk.BackupSetName = "QL Backup"

bk.Database = SqlCon.Database.ToString()

'Declare a BackupDeviceItem by supplying the backup device file name in the constructor, and the type of device is a file.

Dim bdi As BackupDeviceItem

bdi = New BackupDeviceItem("C:\BackupTest\BackupTest.bak", DeviceType.File)

'Add the device to the Backup object.

bk.Devices.Add(bdi)

'Set the Incremental property to False to specify that this is a full database backup.

bk.Incremental = False

'Set the expiration date.

Dim backupdate As New Date

backupdate = New Date(2008, 10, 5)

bk.ExpirationDate = backupdate

'Specify that the log must be truncated after the backup is complete.

bk.LogTruncation = BackupTruncateLogType.Truncate

'Run SqlBackup to perform the full database backup on the instance of SQL Server.

bk.SqlBackup(srv)                                                                                                   <<<***This is where I get the aforementioned error.

'Inform the user that the backup has been completed.

MsgBox("Full Backup complete.")

<EndCode>

 

Please let me know if I have forgotten something.

Answer 14

Hi ALight,

 

I don't see anything particularly wrong with your code, but I'm confused by the connection string you've documented. Why have you explicitly give the full path to the MDF file in your project directory rather than using the |DataDirectory| macro, which is what VS generates by default? I'm guessing this is the problem. As is mentioned in another post in this forum, there are actually a number of copies of your database, this is a side-effect of how VS works. The database path you specify in your connection string:

Data Source=.\SQLEXPRESS;AttachDbFilename="C:\Documents and Settings\Andre Light\My Documents\Visual Studio 2005\Projects\PLM\PLM_Personal\LM_SQL.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True

is not actually the location of the database when you run your program. When you start debuging, a copy of the database is moved to the Debug directory and that is the one that is used. When you deploy your applciation the database will clearly not even be on your computer anymore, so pointing your connection string directly to you own My Documents folder will not be correct.

Mike

Answer 15

MF is correct in what he writes about how all this works. I'll make one minor correction related to what a User Instance actually is.

MFriedlander wrote:

I believe that this underlying SQLExpress installation is the parent but that the individual mdf files we use in our apps are instances.

Pretty close, but not quite. The gory technical details of User Instances are explianed in this white paper, so I'll just give you the high points. First, you need to understand the concept of an instance of SQL Server. In short, we allow you to install multiple copies of SQL Server onto the same computer. Each copy is called an instance and comprises a completely separate set of binaries and install location. Instances are identified by the instance name which can be used as part of the connection string. There can be one default instance, which is the copy of SQL Server that is reached by just specifying the name of the computer (Data Source=MyComputer or Data Source=(local)) and many named instances, which are any other installations of SQL Server that are accessed using the named of the computer and the instance name (Data Source=MyComputer\MyInstance or Data Source=(local)\MyInstance). While SQL Express can be installed as the default instance, normally it is installed as a named instance, and by default as the instance named SQLEXPRESS. You can change this during installation and you can install up to 16 instances of SQL Express will different names. For SQL Express, this instances is what I refer to as the parent instance because it is the actual installed instance running as a Service.

A user instance is a special kind of SQL Server instance that is only supported by SQL Express. It operates on the same principle of being a completely separate running instance of SQL Express, but you do not install it, it doesn't have it's own set of binaries and it runs as a user process rather than a service. When you specify User Instance=True in your connection string, it triggers a specific chain of events in SQL Express:

1. Connect to the parent instance and specify User Instance=True
2. Parent instances determines if the User Instance exists, and if it doesn't, copies the appropriate system database files to the user instance location. (see the white paper for more about this.)
3. Parent isntance starts the user instance using the credentials of the current user. (This is why it's called a user instance)
4. Pass the database information from AttacheDbFilename and any additional login information to the user instance so the database can be attached and logged into.
5. Parent instance passes the connection to the user instance back to the calling application.

User Instances have a number of unique aspects compared to regular instances of SQL Server:

• They only support Windows Authentication
• They only support local access
• They use a Pipe name for connections rather than a normal instance name
• They run as a user process rather than a service

To summarize, a user instance is not "the database file" but an actual, running process of SQL Express that is started using a special set of instructions in SQL Express. There is only one user instance per user, so if you have multiple applications that implement user instances, they will all be the same user instance with multiple databases attached to it. (This is why the database file itself is not the user instance.)

Hope this helps some of the details of what's going on.

Mike

 

Answer 16

Hi Mike,

 

Sorry, I wasn't clear with my documentation:

 

The connection string that I provided (which you referenced in your last reply) was the one from the database in the Server Explorer window (which doesn't allow me to use the |DataDirectory| macro).

 

The data set based on the main database (LM_SQL.mdf) uses the connection string that is stored in My.Settings (I think?), which is the string that VS created automatically (Data Source=.\SQLEXPRESS;AttachDbFilename="|DataDirectory|\LM_SQL.mdf";Integrated Security=True;Connect Timeout=20;User Instance=True).

 

The error message references the Debug folder ('091B68B7503417C4C8798F2E64D7F3F5_L STUDIO 2005\PROJECTS\PLM\PLM_PERSONAL\BIN\DEBUG\LM_SQL.MDF'), so the My.Settings connection string must be utilized during debugging, right?

 

If the code and connection string are both OK, is there anything else that I might be doing incorrectly?

 

Thank you for your time

 

AL

Answer 17

The error message references the Debug folder ('091B68B7503417C4C8798F2E64D7F3F5_L STUDIO 2005\PROJECTS\PLM\PLM_PERSONAL\BIN\DEBUG\LM_SQL.MDF'), so the My.Settings connection string must be utilized during debugging, right?

Right, the fact that the database is in the debug folder suggests you are using the |DataDirectory| macro from the VS created connection string. Thanks for clarifying.

If the code and connection string are both OK, is there anything else that I might be doing incorrectly?

Now that I look again, I think I see a problem in your code. Somewhere in the back and forth of the thread, the code that MF was posting seems to have lost a line related to creating the server. Here is the code from the originally posted sample:

 

Code Snippet

Dim ServerConnection As ServerConnection = New Microsoft.SqlServer.Management.Common.ServerConnection(SqlConnection)

Dim srv As Server = New Server(ServerConnection)

 

You should notice the difference is that the Server is being created using a ServerConnection object, which is pointing directly back at the connection that references the connection string from the project. In your case, you've attempted to specify the path to the database as the Server. The point of extracting the path to the database was to use later when specifying the Database property of the Backup object.

I actually extracted the specifics of this thread and another into a blog post which you can find here. This post gives a little theory and also provides examples of how to both Backup and Resore withing the context of a User Instance. The blog has a C# example, but once you get into the SMO part, it's pretty much identical regardless of what language you're using. Take a look at the sample in the blog and examine the differences in creating the Server and where the actual database name is used.

This should straighten things out,

Mike

Answer 18

Thank you for setting up that blog, Mike.  That really helps clarify what's going on, and now my backup works fine (I have a question that follows this, however...).

 

Here is my final Backup code:

 

Code Snippet

Dim SqlCon As SqlClient.SqlConnection = New SqlClient.SqlConnection(My.Settings.LM_SQLConStr)

SqlCon.Open()

Dim SC As New ServerConnection(SqlCon)

Dim srv As New Server(SC)

'Verify User Instance connection

Console.WriteLine("User Instance Server: " & srv.InstanceName.ToString)

'Declare a BackupDeviceItem by supplying the backup device file name in the constructor, and the type of device is a file.

Dim bdi As New BackupDeviceItem("C:\BackupTest.bak", DeviceType.File)

'***Set backup details

Dim bk As New Backup

bk.Devices.Add(bdi)

bk.Action = BackupActionType.Database

bk.BackupSetDescription = "Full Backup_QL"

bk.BackupSetName = "QL Backup"

bk.Database = SqlCon.Database.ToString()

Dim backupdate As New Date(2007, 6, 1)

bk.ExpirationDate = backupdate

bk.LogTruncation = BackupTruncateLogType.Truncate

'Set the Incremental property to False to specify that this is a full database backup.

bk.Incremental = False

'Run SqlBackup to perform the full database backup on the user instance of SQL Server.

bk.SqlBackup(srv)

'Inform the user that the backup has been completed.

MsgBox("Full Backup complete.")

  

Here is my somewhat-related question (please let me know if I should post this to another--or a new--thread):

 

Two warnings showed up after making references to the SMO components:

1. Referenced assembly 'C:\WINDOWS\assembly\GAC_32\Microsoft.SqlServer.BatchParser\9.0.242.0__89845dcd8080cc91\Microsoft.SqlServer.BatchParser.dll' targets a different processor than the application.

2. Referenced assembly 'c:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SqlServer.Replication.dll' targets a different processor than the application.

 

How do I fix the problem causing those warnings?  I have tried searching for some of the keywords in the errors, to no avail.

 

Thanks again,

AL

Answer 19

Hi,

i'm having some trouble using the above. SqlConnection.Database.ToString() returns an empty string.

 

In app.config:

 

    <connectionStrings>
      <add name="netTiersConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\gma.mdf;Integrated Security=True;User Instance=True;Connection Timeout=15;" />
    </connectionStrings>

 

This is used for other than the backup and works fine.

 

In code:

 

string con = "";
  ConnectionStringSettingsCollection cssc = WebConfigurationManager.ConnectionStrings;

 

foreach (ConnectionStringSettings css in cssc)
  {
      if (css.Name == "netTiersConnectionString")
      {
        con = css.ConnectionString;
        break;
      }
  }

 

SqlConnection sqlcon = new SqlConnection(con);

 

ServerConnection svrcon = new ServerConnection(sqlcon);

 

Server svr = new Server(svrcon);

 

Backup bkp = new Backup();
  bkp.Action = BackupActionType.Database;
  bkp.Database = sqlcon.Database.ToString();
  bkp.Devices.Add(new BackupDeviceItem("c:\\test.bak", DeviceType.File));

bkp.Initialize = true;
  bkp.Incremental = false;
  bkp.PercentCompleteNotification = 10;
  bkp.PercentComplete += new PercentCompleteEventHandler(bkp_PercentComplete);
  bkp.SqlBackup(svr);

 

any ideas?

 

thanks

Dave

 

Answer 20

Just realised I'm missing

 

sqlcon.Open();

 

doh

 

Now I get the d/b name but the backup fails with a simple 'backup failed' message.

Answer 21

I'm now getting a meaningful message:

 

Cannot open user default database. Login failed.

Login failed for user 'YOUR-D6DCA04BF3\mddbsder'.

 

This happens when I open the sql connection:

 

SqlConnection sqlcon = new SqlConnection(constr);

sqlcon.Open();

 

How can the account I'm running under have a default database?

 

 

 

Answer 22

When we talk about User Instances and use AttachDBFilename in our connection strings then why can't we think about simple XCopy technique and try to figure out the SQL way of backup and restore?

 

I have hit this topic for the same problem but after reading about how to connect to a User Instance, I deviated from the backup method mentioned in the posts above. What I did was;

 

1. Connect to the User Instance

2. Close any open conections to db

3. Detach the db

4. Do what ever you want (copy, zip & copy, copy with a new name, etc etc)

5. Disconnect from the User Instance

6. Re-Open the connection close in step 1 above

 

 

Additional references required:

1. Microsoft.SqlServer.ConnectionInfo.dll

2. Microsoft.SqlServer.smo.dll

 

Additional imports required:

1. Microsoft.SqlServer.Management.Common

2. Microsoft.SqlServer.Management.Smo

 

Code Snippet

Try

Dim strDatabasePath As String = My.Computer.FileSystem.CombinePath(My.Application.Info.DirectoryPath, "dbName.mdf")

Dim strdbLogPath As String = My.Computer.FileSystem.CombinePath(My.Application.Info.DirectoryPath, "dbName_log.ldf")

 

' DB.Connection can be any valid SQLConnection which you might already be using in your application

Dim srvCon As New ServerConnection(DB.Connection)

Dim srv As Server = New Server(srvCon)

If srv.Databases.Contains(strDatabasePath) Then

If Not DB.Connection.State = ConnectionState.Closed Then

DB.Connection.Close()

End If

srv.DetachDatabase(strDatabasePath, True)

My.Computer.FileSystem.CopyFile(strDatabasePath, "e:\backups\dbName.mdf", True)

My.Computer.FileSystem.CopyFile(strdbLogPath, "e:\backups\dbName_log.ldf", True)

MessageBox.Show("Backup taken successfully")

End If

srvCon.Disconnect()

DB.Connection.Open()

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

 

 

Regards,

Adeel Akhtar

Answer 23

Adeel,

How do I find DB.Connection in my application?

Thanks,

Don

Answer 24

raf

Thanks a lot.