SQL Server backup from VB.NET not generating backup file, file not shown in drive

Question

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

Answer 1

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