Restore Database

Kill Connection

Page 1 of 1

2 Replies - 9119 Views - Last Post: 08 September 2008 - 07:10 AM Rate Topic: -----

#1 MrWobbles  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 31
  • View blog
  • Posts: 328
  • Joined: 11-April 08

Restore Database

Posted 05 September 2008 - 09:23 AM

I am trying to restore a database, it will most likely be the one that is currently open, but a previous version of it or something along those lines.

The error I get is

System.Data.SqlClient.SqlException: Cannot detach the database 'SomethingToTest' because it is currently in use.

The function to kill the database is:

Private Sub killConnections(ByVal DBname As String, ByVal myConn As SqlConnection)
		Dim sql As String = "USE [master]"

		Dim myCommand As SqlCommand = New SqlCommand(sql, myConn)

		Try
			myConn.Open()
			myCommand.ExecuteNonQuery()

		Catch ex As Exception
			MessageBox.Show("Database Kill Connection Failure: " & vbCr & vbCr & ex.ToString())
			Exit Sub
		Finally
			If (myConn.State = ConnectionState.Open) Then
				myConn.Close()
			End If
		End Try

		sql = "EXEC master.dbo.sp_detach_db @dbname = N'" & DBname & "', @keepfulltextindexfile=N'true' "

		myCommand = New SqlCommand(sql, myConn)

		Try
			myConn.Open()
			myCommand.ExecuteNonQuery()

		Catch ex As Exception
			MessageBox.Show("Database Kill Connection Failure: " & vbCr & vbCr & ex.ToString())
			Exit Sub
		Finally
			If (myConn.State = ConnectionState.Open) Then
				myConn.Close()
			End If
		End Try
	End Sub


The function to restore the database is:

Private Sub restoreDatabase(ByVal dbName As String)
		Dim sql As String

		Dim myConn As SqlConnection = New SqlConnection("Server=.\SQLExpress;Trusted_Connection=Yes;database=master")

		Dim path As String = My.Settings.dbFilePath

		My.Settings.databaseName = dbName.Substring(0, dbName.IndexOf("_"))
		dbName = dbName.Substring(0, dbName.IndexOf("."))

		MsgBox(path & "/" & dbName)

		killConnections(dbName.Substring(0, dbName.IndexOf("_")), myConn)

		sql = "RESTORE DATABASE " & dbName.Substring(0, dbName.IndexOf("_")) & " " & _
			"FROM DISK = N'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\" & dbName & ".bak' " & _
			"WITH REPLACE"

		Dim myCommand As SqlCommand = New SqlCommand(sql, myConn)

		Try
			myConn.Open()
			myCommand.ExecuteNonQuery()

		Catch ex As Exception
			MessageBox.Show("Database Restore Failure: " & vbCr & vbCr & ex.ToString())
			Exit Sub
		Finally
			If (myConn.State = ConnectionState.Open) Then
				myConn.Close()
			End If
		End Try

		MsgBox("Database Restoration Successful")

		closeForm()
	End Sub


Any help on this is appreciated.

Edit to ask, how do you put the line numbers in the code box?

This post has been edited by MrWobbles: 05 September 2008 - 09:24 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Restore Database

#2 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 9187
  • View blog
  • Posts: 34,489
  • Joined: 12-June 08

Re: Restore Database

Posted 05 September 2008 - 12:56 PM

View PostMrWobbles, on 5 Sep, 2008 - 11:23 AM, said:

Edit to ask, how do you put the line numbers in the code box?


To do this at the code tag header have it do code=vb (or: =sql, =java,=cpp).
Private Sub killConnections(ByVal DBname As String, ByVal myConn As SqlConnection)
		Dim sql As String = "USE [master]"

		Dim myCommand As SqlCommand = New SqlCommand(sql, myConn)

		Try
			myConn.Open()
			myCommand.ExecuteNonQuery()

		Catch ex As Exception
			MessageBox.Show("Database Kill Connection Failure: " & vbCr & vbCr & ex.ToString())
			Exit Sub
		Finally
			If (myConn.State = ConnectionState.Open) Then
				myConn.Close()
			End If
		End Try




as for your problem, I assume you searched for that error string?

http://74.125.95.104...b...;cd=1&gl=us

This post has been edited by modi123_1: 05 September 2008 - 01:01 PM

Was This Post Helpful? 1
  • +
  • -

#3 MrWobbles  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 31
  • View blog
  • Posts: 328
  • Joined: 11-April 08

Re: Restore Database

Posted 08 September 2008 - 07:10 AM

Thanks modi, while I didn't use the SQLDMO Object, in the end my answer did come from your post, I realized that dropping the database would be much easier than trying to disconnect from it - and since it won't be dropped unless there is a back up of it then nothing should go wrong :P

Of course my life goes by Murphy's law, and therefore I have to be extra careful.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1