Backup of SQL Server database using VB.NET

Question

In vb.net, I'm trying to take a backup of my SQL Server 2012.

When I run my code it gets backup from database correctly (in the local), but when I change the connection to a server (local server), it doesn't do anything.

I think the problem is for the security on the server ...

Any solution regarding to solve this issue is greatly appreciated

My code :

Sub server(ByVal str As String)

    con = New SqlConnection("Data Source=" & str & ";Initial Catalog=DATABASE;Persist       Security Info=True;User ID=username;Password=password")
    con.Open()
    cmd = New SqlCommand("select *  from sysservers  where srvproduct='SQL Server'", con)
    dread = cmd.ExecuteReader
    While dread.Read
    cmbserver.Items.Add(dread(2))
    End While
    dread.Close()

End Sub


Sub connection()

    con = New SqlConnection("Data Source=192.168.0.200;Initial Catalog=database;Persist  Security Info=True;User ID=username;Password=password")
    con.Open()
    cmbdatabase.Items.Clear()
    cmd = New SqlCommand("select * from sysdatabases", con)
    dread = cmd.ExecuteReader
    While dread.Read
       cmbdatabase.Items.Add(dread(0))
    End While
   dread.Close()

   End Sub


Sub query(ByVal que As String)
   On Error Resume Next
   cmd = New SqlCommand(que, con)
   cmd.ExecuteNonQuery()
End Sub



Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick

    Try


        If PRGBackup.Value = 100 Then
            Timer1.Enabled = False
            PRGBackup.Visible = False
            MsgBox("successfully the backup has been done in the specified folder.")
        Else
            PRGBackup.Value = PRGBackup.Value + 5
        End If

    Catch ex As Exception
       MsgBox(ex.Message, , projectTitle)
    End Try


End Sub



Sub blank(ByVal str As String)

    Try


        If cmbserver.Text = "" Or cmbdatabase.Text = "" Then
            MsgBox("Server Name or Database can not be blank.")
            Exit Sub
        Else
            If str = "backup" Then

                SFDBackup.FileName = cmbdatabase.Text
                SFDBackup.ShowDialog()
                Timer1.Enabled = True
                PRGBackup.Visible = True
                Dim s As String
                s = SFDBackup.FileName
                Dim sql As String = "BACKUP DATABASE " & cmbdatabase.Text & " to disk='" & s & "'"
                query(sql)

            ElseIf str = "restore" Then
                OFDBackup.ShowDialog()
                Timer1.Enabled = True
                PRGBackup.Visible = True
                query("RESTORE DATABASE " & cmbdatabase.Text & " FROM disk='" &  OFDBackup.FileName & "'")
            End If
        End If

    Catch ex As Exception
        MsgBox(ex.Message, , projectTitle)
    End Try


End Sub



Private Sub cmbbackup_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)     Handles cmbbackup.Click
blank("backup")
End Sub



Private Sub cmdrestore_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdrestore.Click
blank("restore")
End Sub


Private Sub frmBackup_Load(sender As Object, e As EventArgs) Handles MyBase.Load

Try

    Me.Cursor = Cursors.WaitCursor
    server("192.168.0.200")
    'server(".\sqlexpress")
    Me.Cursor = Cursors.Default

Catch ex As Exception
    MsgBox(ex.Message, , projectTitle)
End Try

End Sub

Can you show us some code, please? Kinda hard to diagnose anything with nothing at all to go on ..... — marc_s
– marc_s, Commented Mar 14, 2014 at 5:52
You shouldn't add your code in a response to your own question - edit the question to include the code! — marc_s
– marc_s, Commented Mar 14, 2014 at 8:31

hoijui · Accepted Answer · 2019-06-25 18:11:43Z

2

You need to execute a backup SQL command:

Dim sqlConnectionString As String = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Locations;Data Source=GIGABYTE-PC\SQLEXPRESS"
    Dim conn As New SqlConnection(sqlConnectionString)
    conn.Open()

    Dim cmd As New SqlCommand
    cmd.CommandType = CommandType.Text
    cmd.CommandText = "BACKUP DATABASE Locations TO DISK='C:\Temp\location.BAK'"
    cmd.Connection = conn
    cmd.ExecuteNonQuery()

edited Jun 25, 2019 at 18:11

hoijui

4,0922 gold badges40 silver badges47 bronze badges

answered Apr 22, 2015 at 16:06

user4820369

212 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

MAMPRO · Accepted Answer · 2019-04-21 21:42:29Z

1

I think you just missed the SQL server instance name, which is after the IP Address backslash sign directly "\": ========> "172.16.9.26\SQLSERVER;"

try this connection since you are using the IP address and you also have a user name and password:

Dim ConnString As String = ("Server=172.16.9.26\SQLSERVER;Database=database;User Id=sa;Password=yourpass")

So, make your SqlConnection like this:

con = New SqlConnection("Data Source=192.168.0.200\SQLSERVER;Initial Catalog=database;Persist Security Info=True;User ID=username;Password=password")

I hope this can help. ^_^

answered Apr 21, 2019 at 21:42

MAMPRO

696 bronze badges

Comments

JPJ · Accepted Answer · 2015-02-03 06:34:28Z

0

It will take the backup but the backup file will goes to some directory on the server and not on the local client

answered Feb 3, 2015 at 6:34

JPJ

1

Comments

Vivek S. · Accepted Answer · 2016-01-15 09:34:01Z

0

If your local computer name is duo-1 and backup file path is D:\Apps\Medlabs i9\DATA BACKUP\Medlabs.bak then then you should use UNC path in backup query.It should be

backup database DB_NAME to disk='\\duo-1\D\Apps\Medlabs i9\DATA BACKUP\Medlabs.bak'

answered Jan 15, 2016 at 9:34

Vivek S.

22.3k9 gold badges71 silver badges87 bronze badges

Collectives™ on Stack Overflow

Backup of SQL Server database using VB.NET

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related