הערה
הגישה לדף זה מחייבת הרשאה. באפשרותך לנסות להיכנס או לשנות מדריכי כתובות.
הגישה לדף זה מחייבת הרשאה. באפשרותך לנסות לשנות מדריכי כתובות.
Question
Wednesday, August 13, 2014 11:55 AM | 1 vote
I'm using this code for backup and restore but it doesn't work correctly.
It doesn't save the generated backup file and doesn't restore backup file to database.
Please point out the errors and give your ideas too.
Imports System.Data.SqlClient
Public Class ImportDB
Dim con As SqlConnection = New SqlConnection("Data Source = .; Integrated Security = True; Database = Master")
Dim cmd As SqlCommand
Dim dr As SqlDataReader
Private Sub ImportDB_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
End Sub
Sub server(ByVal str As String)
con.Open()
cmd = New SqlCommand("select * from sysservers where srvproduct='SQL Server'", con)
dr = cmd.ExecuteReader
While dr.Read
ComboBoxserver.Items.Add(dr(2))
End While
dr.Close()
End Sub
Sub connection()
con = New SqlConnection("Data Source=.;Initial Catalog=SLIC;Integrated Security=True" & Trim(ComboBoxserver.Text) & ";Database=Master;integrated security=SSPI;")
con.Open()
ComboBoxdb.Items.Clear()
cmd = New SqlCommand("select * from sysdatabases", con)
dr = cmd.ExecuteReader
While dr.Read
ComboBoxdb.Items.Add(dr(0))
End While
dr.Close()
End Sub
Private Sub ComboBoxserver_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBoxserver.SelectedIndexChanged
connection()
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
If ProgressBar1.Value = 100 Then
Timer1.Enabled = False
ProgressBar1.Visible = False
MsgBox("Successfully Done")
Else
ProgressBar1.Value = ProgressBar1.Value + 5
End If
End Sub
Sub blank(ByVal str As String)
If ComboBoxserver.Text = "" Or ComboBoxdb.Text = "" Then
MsgBox("Server Name & Database Blank Field")
Exit Sub
Else
If str = "backup" Then
SaveFileDialog1.FileName = ComboBoxdb.Text
SaveFileDialog1.ShowDialog()
Timer1.Enabled = True
ProgressBar1.Visible = True
Dim s As String
s = SaveFileDialog1.FileName
query("backup database " & ComboBoxdb.Text & " to disk='" & s & "'")
ElseIf str = "restore" Then
OpenFileDialog1.ShowDialog()
Timer1.Enabled = True
ProgressBar1.Visible = True
query("RESTORE DATABASE " & ComboBoxdb.Text & " FROM disk='" & OpenFileDialog1.FileName & "'")
End If
End If
End Sub
Private Sub btn_backup_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_backup.Click
blank("backup")
End Sub
Private Sub btn_restore_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_restore.Click
blank("restore")
End Sub
End Class
All replies (5)
Thursday, August 14, 2014 10:19 AM ✅Answered | 1 vote
And probably it never will. To make a backup or a restore all users have to be disconnected to the server. And if you do that using VB that VB program is connected to the server.
Success
Cor
Hi Habib,
I've made some testing based on your code, on my side, the export operation worked well.
To restore database correctly, in addition to Cor Ligthert's suggestion, we could drop this database first before restoring it:
ElseIf str = "restore" Then
OpenFileDialog1.FileName = ""
OpenFileDialog1.Filter = "BAK Files (.bak)|*.bak|All Files (*.*)|*.*"
OpenFileDialog1.ShowDialog()
Timer1.Enabled = True
ProgressBar1.Visible = True
query("DROP DATABASE " & ComboBoxdb.Text)
query("RESTORE DATABASE " & ComboBoxdb.Text & " FROM disk='" & OpenFileDialog1.FileName & "'")
End If
On my side, it worked well.
We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.
Thursday, August 14, 2014 10:53 AM | 1 vote
@Franklin,
I never got this running, but maybe was it also caused because of the fact that SQLServer has normally its own administrator users.
I remove my message.
Success
Cor
Thursday, August 14, 2014 1:25 PM
You may also want to consider using the SMO Namespace:
http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.backup.sqlbackup.aspx
http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.restore(v=sql.105).aspx
Paul ~~~~ Microsoft MVP (Visual Basic)
Friday, August 15, 2014 10:04 AM
Thnx Franklin Chen - MSFT.
With the changes you have done, it still doesn't work. the problems I'm facing are.
1) When I get Backup, it works functionally but when I go to the location, I find no file of backup saved there.
2) When I try to restore, it works functionally but after viewing the tables I find the Database empty(No tables), like it wasn't restored. I checked up my backup file(which I'm trying to restore) is 100% ok.
I'm using Windows 8.1(I think this shouldn't be the problem).
Tuesday, August 19, 2014 9:52 AM | 1 vote
Thnx Franklin Chen - MSFT.
With the changes you have done, it still doesn't work. the problems I'm facing are.
1) When I get Backup, it works functionally but when I go to the location, I find no file of backup saved there.
2) When I try to restore, it works functionally but after viewing the tables I find the Database empty(No tables), like it wasn't restored. I checked up my backup file(which I'm trying to restore) is 100% ok.
I'm using Windows 8.1(I think this shouldn't be the problem).
Hi Habib,
As Cor mentioned, if you want to make it worked correctly, the current user must be the administrator of Sql Server Database, in my testing, it worked well if I'm the sysadmin.
#Server-Level Roles
http://msdn.microsoft.com/en-us/library/ms188659.aspx
We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.