-
Feb 11th, 2014, 10:46 AM
#1
Thread Starter
Fanatic Member
Restore database from file
can someone help me fix this
i want to select file and restore it
but i got message that
Exclusive access could not be obtained because the database is in use.
RESTORE DATABASE is terminating abnormally.
cmd.ExecuteNonQuery() <---
how to fix it?
Code:
Private Sub Execute(ByVal strAction As Action)
Dim Filename As String
ServerName1 = cmbServerName1.Text.Trim()
UserID1 = txtUserName1.Text.Trim()
Password1 = txtPassword1.Text.Trim()
database = cmbDataBase1.Text
If cmbAuthe1.SelectedIndex = 0 Then
strConn = "Data Source=" & ServerName1 & ";Initial Catalog=" & cmbDataBase1.Text & ";Integrated Security=True"
Else
strConn = "Data Source=" & ServerName1 & "; Initial Catalog=" & cmbDataBase1.Text & ";user id=" & UserID1 & ";password=" & Password1 & ";Integrated Security=false"
End If
con = New SqlConnection(strConn)
con.Open()
Dim strQuery As String
If strAction = Action.BackUp Then
Dim objdlg As New SaveFileDialog
objdlg.FileName = database
objdlg.ShowDialog()
Filename = objdlg.FileName
strQuery = "backup database " & database & " to disk='" & Filename & "'"
Else
Dim objdlg As New OpenFileDialog
objdlg.FileName = database
objdlg.ShowDialog()
Filename = objdlg.FileName
strQuery = "RESTORE DATABASE " & database & " FROM disk='" & Filename & "'"
End If
Dim cmd As SqlCommand
cmd = New SqlCommand(strQuery, con)
cmd.ExecuteNonQuery()
End Sub
-
Feb 11th, 2014, 11:03 AM
#2
Re: Restore database from file
While you can backup a database while connected to it (because it's a read operation) you CANNOT be connected to the database when you restore it (because it's a write operation) ... you have to connect to a DIFFERENT database (preferably the master or default database) in order to issue a restore.
That's a long way of saying your connectionstring needs to change.
-tg
-
Feb 11th, 2014, 11:12 AM
#3
Thread Starter
Fanatic Member
Re: Restore database from file
how can i do it
i must write database name in field and click restore so can connect to the db, how to fix it
take a look on the sourceBackUpAndRestoreSQLDB.zip
need only to fix this to restore db
-
Feb 11th, 2014, 12:12 PM
#4
Re: Restore database from file
As I said... don't connect to the database... connect to a different database, such as Master or Default. I clearly stated that "your connectionstring needs to change."
i must write database name in field and click restore so can connect to the db, how to fix it
No... you need the database name to restore it... NOT to connect to it.
take a look on the sourceBackUpAndRestoreSQLDB.zip
I don't need to.. the code you provided is enough for me to see the problem, even if you can't.
need only to fix this to restore db
You need a different connectionstring for your restore... I don't know how many more times I can say it... "your connectionstring needs to change."
-tg
-
Feb 11th, 2014, 12:37 PM
#5
Thread Starter
Fanatic Member
Re: Restore database from file
how to change the db then..i got fields that i write show me code how to change the db then that is used dont know how to connect it to other
-
Feb 11th, 2014, 12:42 PM
#6
Re: Restore database from file
sigh.... fine....
you connect it to another database BY GIVING it the name of the other database... like "master" ....
Code:
If cmbAuthe1.SelectedIndex = 0 Then
strConn = "Data Source=" & ServerName1 & ";Initial Catalog=" & if(strAction = Action.BackUp, cmbDataBase1.Text, "master") & ";Integrated Security=True"
Else
strConn = "Data Source=" & ServerName1 & "; Initial Catalog=" & if(strAction = Action.BackUp, cmbDataBase1.Text, "master") & ";user id=" & UserID1 & ";password=" & Password1 & ";Integrated Security=false"
End If
there... IF you're doing a back up, it will use the database name, otherwise it will use master as the database...
that's all... as I said, simply connect to a DIFFERENT database for a restore...
-tg
-
Feb 11th, 2014, 12:57 PM
#7
Thread Starter
Fanatic Member
Re: Restore database from file
so i need to edit this:
cmbDataBase1.Items.Add(database.Name)
?
and add the code
Dim cmd As SqlCommand
cmd = New SqlCommand(strQuery, con)
cmd.ExecuteNonQuery()
If cmbAuthe1.SelectedIndex = 0 Then
strConn = "Data Source=" & ServerName1 & ";Initial Catalog=" & If(strAction = Action.BackUp, cmbDataBase1.Text, "master") & ";Integrated Security=True"
Else
strConn = "Data Source=" & ServerName1 & "; Initial Catalog=" & If(strAction = Action.BackUp, cmbDataBase1.Text, "master") & ";user id=" & UserID1 & ";password=" & Password1 & ";Integrated Security=false"
End If
Edit , here is full form1
Code:
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Management.Smo
Public Class Form1
'Backup & Restore of SQL Server database using VB.NET
Private Sub cmbDataBase1_DropDown(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbDataBase1.DropDown
Try
cmbDataBase1.Items.Clear()
Dim serverName As String = cmbServerName1.Text.ToString()
Dim serverConnection As Microsoft.SqlServer.Management.Common.ServerConnection = New Microsoft.SqlServer.Management.Common.ServerConnection()
serverConnection.ServerInstance = serverName
serverConnection.LoginSecure = True
If cmbAuthe1.SelectedIndex = 1 Then
serverConnection.LoginSecure = False
serverConnection.Login = txtUserName1.Text
serverConnection.Password = txtPassword1.Text
End If
Dim server As Server = New Server(serverConnection)
Try
For Each database As Database In server.Databases
cmbDataBase1.Items.Add(database.Name)
Next
Catch ex As Exception
Dim exception As String = ex.Message
End Try
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub
Private Sub cmbServerName1_DropDown(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbServerName1.DropDown
Dim dataTable1 = SmoApplication.EnumAvailableSqlServers(False)
cmbServerName1.ValueMember = "Name"
cmbServerName1.DataSource = dataTable1
End Sub
Dim con As SqlConnection
Dim Connect1 As Integer = 0
Dim ServerName1 As String
Dim UserID1 As String
Dim Password1 As String
Dim strConn As String
Dim database As String
Enum Action
BackUp
Restore
End Enum
Private Sub btnBackUp_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBackUp.Click
Execute(Action.BackUp)
End Sub
Private Sub btnRestore_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRestore.Click
Execute(Action.Restore)
End Sub
Private Sub Execute(ByVal strAction As Action)
Dim Filename As String
ServerName1 = cmbServerName1.Text.Trim()
UserID1 = txtUserName1.Text.Trim()
Password1 = txtPassword1.Text.Trim()
database = cmbDataBase1.Text
If cmbAuthe1.SelectedIndex = 0 Then
strConn = "Data Source=" & ServerName1 & ";Initial Catalog=" & cmbDataBase1.Text & ";Integrated Security=True"
Else
strConn = "Data Source=" & ServerName1 & "; Initial Catalog=" & cmbDataBase1.Text & ";user id=" & UserID1 & ";password=" & Password1 & ";Integrated Security=false"
End If
con = New SqlConnection(strConn)
con.Open()
Dim strQuery As String
If strAction = Action.BackUp Then
Dim objdlg As New SaveFileDialog
objdlg.FileName = database
objdlg.ShowDialog()
Filename = objdlg.FileName
strQuery = "backup database " & database & " to disk='" & Filename & "'"
Else
Dim objdlg As New OpenFileDialog
objdlg.FileName = database
objdlg.ShowDialog()
Filename = objdlg.FileName
strQuery = "RESTORE DATABASE " & database & " FROM disk='" & Filename & "'"
End If
Dim cmd As SqlCommand
cmd = New SqlCommand(strQuery, con)
cmd.ExecuteNonQuery()
If cmbAuthe1.SelectedIndex = 0 Then
strConn = "Data Source=" & ServerName1 & ";Initial Catalog=" & If(strAction = Action.BackUp, cmbDataBase1.Text, "master") & ";Integrated Security=True"
Else
strConn = "Data Source=" & ServerName1 & "; Initial Catalog=" & If(strAction = Action.BackUp, cmbDataBase1.Text, "master") & ";user id=" & UserID1 & ";password=" & Password1 & ";Integrated Security=false"
End If
End Sub
Last edited by diablo21; Feb 11th, 2014 at 01:03 PM.
-
Feb 11th, 2014, 01:23 PM
#8
Re: Restore database from file
Ungh... I'm sorry... I made an assumption that turned out to be wrong.
No... you weren't supposed to add that code... you were supposed to use it to REPLACE the connectionstring building you're doing at the top.
as I said, you need to change your connectionstring... so that you connect to a different database... that's what it does... but if you add it to your code AFTER you try to do the restore... what good does it do?
-tg
-
Feb 11th, 2014, 01:25 PM
#9
Thread Starter
Fanatic Member
Re: Restore database from file
nothing still shows cant use same database,
i enter in the field database: master or muonline and click restore and select .bak file and always got this message dont know
-
Feb 11th, 2014, 01:42 PM
#10
Addicted Member
Re: Restore database from file
What is the error message that the sql server is returning. Have you tried to restore the database in SMSS to make sure your tsql you're using is correct?
-
Feb 11th, 2014, 02:40 PM
#11
Thread Starter
Fanatic Member
Re: Restore database from file
sql server isnt returning nothing only the program show error cant cant run the option
need to fix this when write database name and select db to restore it normal
Last edited by diablo21; Feb 11th, 2014 at 02:47 PM.
-
Feb 11th, 2014, 03:28 PM
#12
Re: Restore database from file
2 things... 1) you need to make sure the backup you're restoring from actually belongs to the database you're trying to restore. 2) do NOT select master as the database in the dropdown... it should ONLY ever be part of the connectionstring.. AND NOTHING ELSE. 3) you need the FULL path to the bak file... AND it needs to BE ON THE SERVER... if you're backing up and restoring your local system, that's fine, but if you're working on a server... then the bak file needs to be on the server. 4) I can't count.
-tg
-
Feb 11th, 2014, 03:33 PM
#13
Thread Starter
Fanatic Member
Re: Restore database from file
the back file in on the same pc..my pc..
i write on the text field MuOnline or master still the same the same error always
If i leave the text field empty and click restore select file and shows me this error:
Incorrect syntax near the keyword 'FROM'. >> cmd.ExecuteNonQuery()
-
Feb 11th, 2014, 03:46 PM
#14
Re: Restore database from file
"i write on the text field" -- what text field.. you should be selecting a database name from a dropdown.
"or master " Gah! I said stop that... master should only ever appear in the connection string IF YOU ARE DOING a restore...
"If i leave the text field empty and click restore select file and shows me this error:
Incorrect syntax near the keyword 'FROM'. >> cmd.ExecuteNonQuery()" -- expected since, yes, having a blank database name would result in bad SQL.
are you selecting a database, clicking Backup... then clicking Restore? with out doing anything else.
-tg
-
Feb 11th, 2014, 04:19 PM
#15
Addicted Member
Re: Restore database from file
Have you looked at what your app built for strQuery? It's saying you have an error in your syntax, I would use to the debugger to grab what app have created for strQuery.
-
Feb 11th, 2014, 04:39 PM
#16
Thread Starter
Fanatic Member
Re: Restore database from file
thats why i posted full source, but i see nobody even look it...
Dim strQuery As String
strQuery = "RESTORE DATABASE " & database & " FROM disk='" & Filename & "'"
-
Feb 11th, 2014, 04:50 PM
#17
Re: Restore database from file
The only time a syntax error was mentioned was when the OP doesn't select a database to restore. Because of the way the SQL is being constructed, of course there's going to be an error... you can't restore w/o specifying the database.
I even addressed this in my last post:
"If i leave the text field empty and click restore select file and shows me this error:
Incorrect syntax near the keyword 'FROM'. >> cmd.ExecuteNonQuery()" -- expected since, yes, having a blank database name would result in bad SQL.
However, that doesn't address the problem that there is a connection open on the database when trying to do a restore. I tried to get more information about that, but if my inquiries are going to go unanswered, then I'm just wasting everyone's time and I'll move on to the next problem.
-tg
-
Feb 11th, 2014, 05:56 PM
#18
Addicted Member
Re: Restore database from file
Originally Posted by techgnome
The only time a syntax error was mentioned was when the OP doesn't select a database to restore. Because of the way the SQL is being constructed, of course there's going to be an error... you can't restore w/o specifying the database.
I even addressed this in my last post:
However, that doesn't address the problem that there is a connection open on the database when trying to do a restore. I tried to get more information about that, but if my inquiries are going to go unanswered, then I'm just wasting everyone's time and I'll move on to the next problem.
-tg
OHhh, I thought he changed to connection string to connect to master and was no getting a syntax error when trying restore the database.
-
Feb 11th, 2014, 05:58 PM
#19
Addicted Member
Re: Restore database from file
Originally Posted by diablo21
thats why i posted full source, but i see nobody even look it...
Dim strQuery As String
strQuery = "RESTORE DATABASE " & database & " FROM disk='" & Filename & "'"
That wasn't what I mean. I meant after that line of code has been executed what are the results? But since TG said the connection hasn't been corrected my post doesn't really matter until that's been fixed.
-
Feb 12th, 2014, 02:26 AM
#20
Thread Starter
Fanatic Member
Re: Restore database from file
still the same if i even change to parametres still cant load it
edit: or someone can change the functions for backup and restore to other code that is working ?
Last edited by diablo21; Feb 12th, 2014 at 04:09 AM.
-
Feb 12th, 2014, 01:59 PM
#21
Addicted Member
Re: Restore database from file
Here is a quick example I threw together.
Code:
Imports System.Data.SqlClient
Imports System.Data.Sql
Private Sub restoreDB()
Dim sqlCmd As New SqlCommand
Dim connstr As String = "Data Source=localhost\clinkdev;Initial Catalog=Master;Integrated Security=SSPI;"
Dim dbName As String = "CLINK_REPORTING"
Dim dbBakFile As String = "C:\temp\CLINK_REPORTING.bak"
Dim sqlQry = "restore database " & dbName & " from disk = '" & dbBakFile & "' WITH REPLACE"
Try
Using sqlCnn As New SqlConnection(connstr)
With sqlCmd 'build sql command
.Connection = sqlCnn
.CommandText = sqlQry
.CommandType = CommandType.Text
End With
sqlCnn.Open()
sqlCmd.ExecuteNonQuery()
End Using
Catch ex As Exception
MessageBox.Show(ex.ToString)
Finally
sqlCmd.Dispose()
End Try
End Sub
-
Feb 13th, 2014, 02:14 AM
#22
Thread Starter
Fanatic Member
Re: Restore database from file
i make it like this but got error again
Code:
Private Sub Execute(ByVal strAction As Action)
Dim Filename As String
Dim sqlCmd As New SqlCommand
ServerName1 = cmbServerName1.Text.Trim()
UserID1 = txtUserName1.Text.Trim()
Password1 = txtPassword1.Text.Trim()
database = cmbDataBase1.Text
If cmbAuthe1.SelectedIndex = 0 Then
strConn = "Data Source=" & ServerName1 & ";Initial Catalog=" & cmbDataBase1.Text & ";Integrated Security=True"
Else
strConn = "Data Source=" & ServerName1 & "; Initial Catalog=" & cmbDataBase1.Text & ";user id=" & UserID1 & ";password=" & Password1 & ";Integrated Security=false"
End If
con = New SqlConnection(strConn)
con.Open()
Dim strQuery As String
If strAction = Action.BackUp Then
Dim objdlg As New SaveFileDialog
objdlg.FileName = database
objdlg.ShowDialog()
Filename = objdlg.FileName
strQuery = "backup database " & database & " to disk='" & Filename & "'"
Else
Dim objdlg As New OpenFileDialog
objdlg.FileName = database
objdlg.ShowDialog()
Filename = objdlg.FileName
strQuery = "RESTORE DATABASE " & database & " FROM disk='" & Filename & "' WITH REPLACE"
End If
Try
Using sqlCnn As New SqlConnection(strConn)
With sqlCmd 'build sql command
.Connection = sqlCnn
.CommandText = strQuery
.CommandType = CommandType.Text
End With
sqlCnn.Open()
sqlCmd.ExecuteNonQuery()
End Using
Catch ex As Exception
MessageBox.Show(ex.ToString)
Finally
sqlCmd.Dispose()
End Try
End Sub
-
Feb 13th, 2014, 09:39 AM
#23
Re: Restore database from file
I'm going to ask one more time... I'm willing to continue with the thread if you can answer it. If not, then I'm done.
Originally Posted by me
are you selecting a database, clicking Backup... then clicking Restore? with out doing anything else.
To further expand on that, is there ANYONE ELSE using this database? Or are you the only user?
-tg
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|