Hi all,

I've got my restore database code working good but was wondering if there is anyway to get some feedback from the SQL server during the restore?

Dim sqlstr As String

        sqlstr = "restore database " + dbNameFinal + " from disk ='" + tbBAKpath.Text + "' with move '" + originalDBname + "' to '" + tbRestorePath.Text + "\" + dbNameFinal + ".MDF' , move '" + origianlLOGname + "' to '" + tbRestorePath.Text + "\" + dbLogNameFinal + ".LDF'"

        Dim SQLConn As New SqlConnection() 'The SQL Connection
        Dim SQLCmd As New SqlCommand() 'The SQL Command
        SQLCmd.CommandTimeout = 600
        SQLConn.ConnectionString = ConnString 'Set the Connection String
        SQLConn.Open() 'Open the connection

        SQLCmd.Connection = SQLConn 'Sets the Connection to use with the SQL Command
        SQLCmd.CommandText = sqlstr 'Sets the SQL String
        'MessageBox.Show(sqlstr)
        Try
            Dim dr1 As SqlDataReader
            dr1 = SQLCmd.ExecuteReader() 'Executes SQL Commands Non-Querys only
      
            While dr1.Read
                lblSQLcommandStatus.Visible = True
                lblSQLcommandStatus.Text = dr1.ToString


            End While
            Me.Cursor = Cursors.Default
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            Me.Cursor = Cursors.Default
        End Try

I've tried this back it doesnt do anything.

Thanks in advance...

Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlClient
Imports System.Data.SqlTypes

Public Class frmRestore

	Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
		Dim connStr As String = "Data Source=apex2006sql;Initial Catalog=master;Integrated Security=True;"
		Dim query As String = "RESTORE DATABASE [ServMan] FROM  DISK = N'K:\Volume3\BackupSqlDbo\ServMan_backup_200908302125.bak' WITH  FILE = 1,  MOVE N'ServMan' TO N'D:\Microsoft SQL Server\Data\ServMan.mdf',  MOVE N'ServMan_log' TO N'D:\Microsoft SQL Server\Data\ServMan_log.ldf',  NOUNLOAD,  REPLACE,  STATS = 10"
		Dim conn As New SqlConnection(connStr)
		conn.FireInfoMessageEventOnUserErrors = True
		conn.Open()
		AddHandler conn.InfoMessage, AddressOf onInfoMessage
		Dim cmd As New SqlCommand(query, conn)
		cmd.ExecuteNonQuery()
		conn.Close()
		conn.Dispose()
	End Sub

	Private Sub onInfoMessage(ByVal sender As Object, ByVal e As SqlInfoMessageEventArgs)
		MessageBox.Show(e.Message)
	End Sub

End Class
Comments
thanks man!!

Cool, thanks man. I'm assuming you can use the same handler call for most SQL query's?

What do you mean? Most SQL calls don't return progress back like a restore operation. Unless you mean the connection.open -- command.execute methods, then yes. That is how you interface with the SQL Server.

Yeah but as far as I know those don't report progres... they just execute and finish. The only thing I know to report progress is a backup/restore. But you can subscribe to the events and take a look :)

This question has already been answered. Start a new discussion instead.