We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Backup database in a deployed vb.net application

Medium Priority
359 Views
Last Modified: 2013-11-26
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!

       
Comment
Watch Question

path should be 128 characters only

Commented:
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"

Author

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?!

Commented:
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

Author

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.

Commented:
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
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.
       

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Commented:
Just curious... what does "con.Database.ToString()" resolve to?

Author

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.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.