0

Please I developed a software with VB.NET and SQL Server. The backup section works fine during development but gives errors after I deployed the software. Below are the codes are used for the backup"

Try

        Cursor = Cursors.WaitCursor
        Timer2.Enabled = True
        If mysqlconnectionstring.State = ConnectionState.Closed Then
            mysqlconnectionstring.Open()
        End If

        If (Not System.IO.Directory.Exists("C:\LotSMSBackup")) Then
            System.IO.Directory.CreateDirectory("C:\LotSMSBackup")
        End If
        Dim destdir As String = "C:\LotSMSBackup\LotSMSBackup " & DateTime.Now.ToString("dd-MM-yyyy_HH-mm-ss") & ".bak"



        Dim cb As String = "backup database [" & System.Windows.Forms.Application.StartupPath & "\Data" & "\Schooldb.mdf] to disk='" & destdir & "'with init,stats=10"

        Dim cmd As SqlCommand = New SqlCommand(cb)
        cmd.Connection = mysqlconnectionstring
        cmd.ExecuteReader()

        MessageBox.Show("Successfully performed", "Database Backup", MessageBoxButtons.OK, MessageBoxIcon.Information)

    Catch ex As Exception
        MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)

    Finally

        mysqlconnectionstring.Close()
    End Try

Below is the error I am getting;

The identifier that starts with ‘C:\Users\Tawiah Lot\AppData\Local\Apps\2.0\MYZD1.GJK\VN8DB86Q.X24\sms…tion_f2ac7542c8a7d9f7_0001.0000_74e25af910bfcd28\Data’ is too long. Maximum length is 128.

lotwan
  • 3
  • 4
  • What is not clear about the error? The path "C:\Users\Tawiah Lot\AppData\Local\Apps\....." is too long. – Alex Aug 11 '17 at 07:03
  • See this as well: https://stackoverflow.com/questions/35788119/how-to-backup-a-sql-server-2014-express-localdb-mdf-file-programmatically – Alex Aug 11 '17 at 07:10
  • See this as well: https://stackoverflow.com/questions/36512709/how-to-take-back-up-of-localdb-c-sharp-my-database-file-name-is-mydatabase-mdf – Alex Aug 11 '17 at 08:04

2 Answers2

1

Here is your error:

"backup database [" & System.Windows.Forms.Application.StartupPath & "\Data" & "\Schooldb.mdf] to disk='" & destdir & "'with init,stats=10"

You should provide database name here, not the path

sepupic
  • 8,409
  • 1
  • 9
  • 20
  • I don't think you are right. He is using LocalDB and it is referenced by path. – Alex Aug 11 '17 at 07:54
  • Ok, I'll remove it, but it's not path that is too long too – sepupic Aug 11 '17 at 07:54
  • I put it back. By no means the path can substitute db_name – sepupic Aug 11 '17 at 07:59
  • I am not 100% sure myself (MSDN does not mention it in), but the question I linked to, uses the same method as OP. – Alex Aug 11 '17 at 08:04
  • Here: https://blogs.technet.microsoft.com/aadsyncsupport/2014/07/28/reference-how-to-backup-the-backend-sql-db-on-localdb/ I see that the backup is successfully completed when only database name was passed in. And this syntax is documented. While I could not find any BACKUP DATABASE description that accepts the path instead of database name – sepupic Aug 11 '17 at 08:08
  • @Seputic I removed the path and I got an error that says schooldb.mdf does not exist. I also renamed the database to sdb.mdf, but still get the same error – lotwan Aug 11 '17 at 09:29
  • Use schooldb as the name of your database, not the file name: backup database schooldb to disk=... – sepupic Aug 11 '17 at 09:31
  • Dim cb As String = "backup database [schooldb] to disk='" & dir & "'with init,stats=10" .Error persist ..'schooldb does not exist' – lotwan Aug 11 '17 at 09:35
  • No please...I still an error that says 'schooldb does not exist – lotwan Aug 11 '17 at 09:37
  • Can you execute select db_name() just to be sure the db name is correct? – sepupic Aug 11 '17 at 09:38
  • C:\USERS\TAWIAH LOT\DOCUMENTS\VISUAL STUDIO 2013\PROJECTS\SMS\SMS\DATA\SCHOOLDB.MDF Please is the results I got – lotwan Aug 11 '17 at 10:04
  • In this case you should pass this as databasename, and Alex was right. But this is different from what you was passing in: it is only 83 characters long that is <128 and cannot cause the error above – sepupic Aug 11 '17 at 10:06
  • Can you pass in the result of db_name() directly to your backup code? Or may be you can save it into a variable and pass this in that variable? – sepupic Aug 11 '17 at 10:08
  • After passing the whole path thus C:\USERS\TAWIAH LOT\DOCUMENTS\VISUAL STUDIO 2013\PROJECTS\SMS\SMS\DATA\SCHOOLDB.MDF..It worked.. But my question is what happens if I deploy the application onto a different machine – lotwan Aug 11 '17 at 10:24
  • Maybe before doing backup you should evaluate len(db_name()) and raise an error saying that until the file will be moved to another location wtih shorter path, the backup is impossible? Or write it in the guide for your application? – sepupic Aug 11 '17 at 10:28
0

I used click once to deploy the application which makes the application run from C:\Users\Tawiah Lot\AppData\Local\Apps\2.0\MYZD1.GJK\VN8DB86Q.X24\sms…tion_f2ac7542c8a7d9f7_0001.0000_74e25af910bfcd28\Data’ This makes the path longer thereby making the length more than the maximum of 128. In other to solve it, I user a third party software "advance installers". This creates a folder with the company name under program files. Thank you all for your support.

lotwan
  • 3
  • 4