0

I am using VB.NET with SQL Server 2012 Express in a software.

I provided facility to take a backup of a database from application itself with following code

Dim con = New SqlConnection("Data Source=.\SQLEXPRESS;Database=Master;User id=sa;password=admin;")
con.Open()
Dim str as string="backup database OFFICEMANAGEMENT to disk='C:\OM.bak'"
Dim cmd as new SqlCommand(str,con)
cmd.ExecuteNonQuery
con.Close()

When above code is run, no backup file gets created, and also no error is shown.

If I run the backup command with T-SQL in the SQL Server Management Studio, the backup file is successfully created.

Can anyone help with this?

Thanks

Hemal
  • 3,682
  • 1
  • 23
  • 54
  • You never ***open*** the connection - try `con.Open()` **before** the `cmd.ExecuteNonQuery` call – marc_s Jun 06 '19 at 11:04
  • Added the code, but still backup file is not generated. Its works from TSQL in SQL Server Management Studio. – Hemal Jun 06 '19 at 14:49

1 Answers1

0

You should change your code as follows

  1. Your connection should be open before execution command.

  2. T-SQL statement to backup the database should contain 'WITH INIT' keywords. If you execute your code multiple time, your BAK file will continue to grow.

    So, try this one 

    Using con = New SqlConnection("Data Source=.\SQLEXPRESS;User id=sa;password=admin;")

    con.Open()

    Dim str As String = "backup database OFFICEMANAGEMENT to disk='C:\TMP\OM.bak' WITH INIT"

    Using cmd = New SqlCommand(str, con)
        cmd.ExecuteNonQuery()
    End Using

    con.Close()

End Using
  • Its not about opening a connection, backup file is still not generated after adding that line. – Hemal Jun 06 '19 at 14:48
  • And there is no exceptions? Very strange. Try changing the path. Put the backup file somewhere else. –  Jun 06 '19 at 16:26
  • Tried creating files in D drive and E drive also. I doubt if its a permission kind of issue. – Hemal Jun 07 '19 at 13:29