[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Backup database in a deployed vb.net application

Posted on 2009-02-09
9
Medium Priority
?
344 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!

       
0
Comment
Question by:marvin34
  • 4
  • 4
9 Comments
 
LVL 4

Expert Comment

by:RamanaChoudary
ID: 23592800
path should be 128 characters only
0
 
LVL 41

Expert Comment

by:graye
ID: 23593005
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
 

Author Comment

by:marvin34
ID: 23593227
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
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
LVL 41

Expert Comment

by:graye
ID: 23594291
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
 

Author Comment

by:marvin34
ID: 23599502
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
 
LVL 41

Expert Comment

by:graye
ID: 23599729
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
 

Accepted Solution

by:
marvin34 earned 0 total points
ID: 23600302
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
 
LVL 41

Expert Comment

by:graye
ID: 23601696
Just curious... what does "con.Database.ToString()" resolve to?
0
 

Author Comment

by:marvin34
ID: 23601963
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

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month18 days, 9 hours left to enroll

826 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question