CODEWITHSUNDEEP
vb.netsql-server-2012database-backups
Ali
Ali

Reputation: 684

Backup of SQL Server database using VB.NET

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

Upvotes: 3

Views: 22126

Answers (4)

user4820369
user4820369

Reputation: 21

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()

Upvotes: 2

MAMPRO
MAMPRO

Reputation: 69

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. ^_^

Upvotes: 1

Vivek S.
Vivek S.

Reputation: 21885

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'

Upvotes: 0

JPJ
JPJ

Reputation: 1

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

Upvotes: 0

Related Questions