Backup database in a deployed vb.net application

I am writing an application in VB2008 Express Edition and I have included a button on the main menu of my application to allow the user to create a backup of the database (single user instance application).  When debugging I have the following code for the button which works perfectly:

        Me.Cursor = Cursors.WaitCursor
        Dim path1 As String = "C:\ECP1\ECP1\Bin\Debug\Resources\EolasBase.mdf"
        Dim constring As String = My.Settings.EolasBaseConnectionString
        Dim con As SqlConnection = New SqlConnection(constring)
        Dim cmdstring As String = "backup database [" + path1 + "] to disk = 'C:\ECP1\backup.bak' with format"
        Dim cmd As New SqlCommand(cmdstring, con)
        con.Open()
        cmd.ExecuteNonQuery()
        con.Close()
        con.Dispose()
        Me.Cursor = Cursors.Default
        MsgBox("Backup Successful")

However, when I change the path of the database (the string 'path1') for deployment purposes
 (i.e.Dim path1 As String = my.application.deployment.datadirectory + "\Resources\EolasBase.mdf") and then deploy the application on my laptop, I get the following error:

The identifier that starts with 'C:\Documents and Settings\MichaelC\Local Settings\Apps\2.0\Data\8NEVGHNG.015\2EMOTG3Z.767\ecp1..tion_d347718c1e7df98e_0000.0001_' is too long. Maximum length is 128.

I've been searching for an answer on the internet and haven't gotten anywhere. Obviously the path is much longer than it was when debugging but I need a way around it.  It's probably just a problem with syntax somewhere but I would appreciate any direction. Thanks!

       
marvin34Asked:
Who is Participating?
 
marvin34Connect With a Mentor Author Commented:
I've solved it!  I stopped passing in the string with the path of the database and instead used the Database.ToString property of the connection which works perfectly when deployed! Here's the full code:

Me.Cursor = Cursors.WaitCursor
        Dim constring As String = My.Settings.EolasBaseConnectionString
        Dim con As SqlConnection = New SqlConnection(constring)
        con.Open()
        Dim cmdstring As String = "backup database [" + con.Database.ToString + "] to disk = 'C:\ECP1\backup.bak' with format"
        Dim cmd As New SqlCommand(cmdstring, con)
        cmd.CommandType = CommandType.Text
        cmd.ExecuteNonQuery()
        con.Close()
        con.Dispose()
        Me.Cursor = Cursors.Default
        MsgBox("Backup Successful")

Thank you for your other suggestions. Cheers.
       
0
 
RamanaChoudaryCommented:
path should be 128 characters only
0
 
grayeCommented:
Have you tried to use the Database name in lieu of the full path to the MDF file?   That's how it works when there's a real SQL Server instance involved.
 Dim cmdstring As String = "backup database EolasBase to disk = 'C:\ECP1\backup.bak' with format"
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
marvin34Author Commented:
Thanks Ramana, but I know the path should be 128 characters only, problem is that the full path is way more than 128 and I need a workaround.

Graye, I had tried just using the database name in lieu of the full path but I got the following error:
'Could not locate entry in sysdatabases for database 'EolasBase'. No entry found with that name. Make sure that the name is entered correctly'

Any ideas?!
0
 
grayeCommented:
Well, here's a crazy idea...   use the DOS SUBST command to map the full path to a drive letter.     So that your very long path would be reduced to a drive letter.   This could be temporary, where you create the drive letter, perform the backup, and then remove the drive letter.
You could either "shell out" via code to run the DOS command, or perform a similar function using the native DefineDosDevice API http://msdn.microsoft.com/en-us/library/aa363904(VS.85).aspx
0
 
marvin34Author Commented:
Well I tried the dos subst command as follows:
Shell("subst h: " + My.Application.Deployment.DataDirectory + "\Resources", AppWinStyle.MinimizedNoFocus)

My command is now:
Dim cmdstring As String = "backup database [h:\EolasBase.mdf] to disk = 'C:\ECP1\backup.bak' with format"

However, I'm still getting the error:
'Could not locate entry in sysdatabases for database 'H:\EolasBase.mdf'. No entry found with that name. Make sure that the name is entered correctly'

When I run this code...
'SELECT     name
FROM         sys.sysdatabases'
...in query designer to get the sysdatabases, it lists the development database (stored with project files) and a lot of application databases from previous builds (all of which have long paths like before).  So the mapping doesn't get round the problem unfortunately.

Really appreciate your suggestions though, so thanks.
0
 
grayeCommented:
Well, it looks like you'll just have to place the database file somewhere else...   I'd recommend you adotp the C:\ProgramData directory idea from Vista
0
 
grayeCommented:
Just curious... what does "con.Database.ToString()" resolve to?
0
 
marvin34Author Commented:
408ACD2E852D4DC160E9D6A6B76F64B0_EMOTG3Z.767\ECP1..TION_D347718C1E7DF98E_0000.0001_BF33CF2BDAA270EF\DATA\RESOURCES\EOLASBASE.MDF

I added a messagebox to display the resolution when deployed and it resolves to the above which is exactly 128 characters long.  Using 'my.application.deployment.datadirectory' previously, meant that this was prefixed with C:\Documents and settings\MichaelC... etc which made it too long.

Where there's a will, there's a way :-) Cheers.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.