vb.net 使用VB.NET备份SQL Server数据库

Backup of SQL Server database using VB.NET

提问by Ali

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

在 vb.net 中，我试图备份我的 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

回答by user4820369

You need to execute a backup SQL command:

您需要执行一个备份 SQL 命令：

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

回答by MAMPRO

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

我想你只是错过了 SQL 服务器实例名称，它在 IP 地址反斜杠后面直接“\”：========>“172.16.9.26\SQLSERVER;”

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

尝试此连接，因为您使用的是 IP 地址并且您还有用户名和密码：

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

So, make your SqlConnection like this:

所以，让你的 SqlConnection 像这样：

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

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

我希望这会有所帮助。^_^

回答by Vivek S.

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

如果您的本地计算机名称是duo-1并且备份文件路径是D:\Apps\Medlabs i9\DATA BACKUP\Medlabs.bak那么您应该在备份查询中使用 UNC 路径。它应该是

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

回答by JPJ

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

它将进行备份，但备份文件将进入服务器上的某个目录，而不是本地客户端