SQL Restore - Getting feedback from the server...

[Merovingian]

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

[sknake]

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

[Merovingian]

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

[sknake]

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.

[Merovingian]

I ment like create database, delete, attach , detach etc.

[sknake]

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

[Merovingian]

Cool, thanks!