none
Restore is failed after completion of 90 % using smo in VB.NET. How to fix this problem?

    Question

  • I am deveploping sql server management project for backup a database , restore a database, running scripts , shrink database ,... through a windows service using vb.net.

    I have a trouble with restoring process.

    Restore is failed after completion of 90 % .
    ( I had faced events 3401 and 3402 while restore.)
    why it is happend ? how to fix this ?
    I had used this code:

    Try
                'connect to server
                WriteLog("TestApplication:RestoreDatabase", "Restore process start for database " & txtRestoreDatabaseName.Text)
                Dim oServerConnection As New ServerConnection(txtRestoreServerName.Text, txtRestoreUsername.Text, txtRestorePassword.Text)
                oServerConnection.ConnectTimeout = 216000
                Dim svrDatabase As New Server(oServerConnection)
                Dim dbYukon As New Database
                WriteLog("TestApplication:RestoreDatabase", "Check if dataabse exists or not")
                'get database
                For Each dbList As Database In svrDatabase.Databases
                    If dbList.Name.ToLower = txtRestoreDatabaseName.Text.ToLower Then
                        dbYukon = dbList
                        Exit For
                    End If
                Next

                If dbYukon.Name Is Nothing Then
                    WriteLog("TestApplication:RestoreDatabase", "Database " & txtRestoreUsername.Text & " does not exist")
                    Exit Sub
                End If

                WriteLog("TestApplication:RestoreDatabase", "Restore process started")
                'restore the DB
                svrDatabase.KillAllProcesses(txtRestoreDatabaseName.Text)
                Dim smoRestoreDatabase As Restore = New Restore()
                smoRestoreDatabase.Action = RestoreActionType.Database
                smoRestoreDatabase.Devices.AddDevice(txtRestoreDBPath.Text, DeviceType.File)
                smoRestoreDatabase.Database = txtRestoreDatabaseName.Text
                smoRestoreDatabase.ReplaceDatabase = True
                smoRestoreDatabase.PercentCompleteNotification = 10
                smoRestoreDatabase.ContinueAfterError = True
                smoRestoreDatabase.NoRecovery = False


                AddHandler smoRestoreDatabase.PercentComplete, AddressOf ProgressEventHandler
                'To read backup file database details
                Dim dtBackupFileDatabase As DataTable = smoRestoreDatabase.ReadFileList(svrDatabase)

                'To set Physical and Logical file name of database from exiting db
                smoRestoreDatabase.RelocateFiles.Add(New RelocateFile(dtBackupFileDatabase.Rows(0)("LogicalName"), dbYukon.PrimaryFilePath & "\" & dbYukon.Name & ".mdf"))
                smoRestoreDatabase.RelocateFiles.Add(New RelocateFile(dtBackupFileDatabase.Rows(1)("LogicalName"), dbYukon.PrimaryFilePath & "\" & dbYukon.Name & "_log.ldf"))

                smoRestoreDatabase.SqlRestore(svrDatabase)
                WriteLog("TestApplication:RestoreDatabase", "Restore process completed")
                Try
                    'To take backup log file of database
                    WriteLog("TestApplication:RestoreDatabase", "Take backup of log file for restored DB on YUKON and truncate log file")
                    Dim dbBackupLogDatabase As New Backup()
                    dbBackupLogDatabase.Action = BackupActionType.Log
                    dbBackupLogDatabase.BackupSetDescription = "Log file of " & txtRestoreDatabaseName.Text
                    dbBackupLogDatabase.BackupSetName = txtRestoreDatabaseName.Text + " Backup log"
                    dbBackupLogDatabase.MediaDescription = "Disk"
                    dbBackupLogDatabase.Database = txtRestoreDatabaseName.Text
                    dbBackupLogDatabase.NoRecovery = False
                    dbBackupLogDatabase.LogTruncation = BackupTruncateLogType.NoTruncate

                    dbBackupLogDatabase.Devices.AddDevice(My.Settings.YUKON_SERVER_TEMP_DIRECTORY_PATH & "\log.bak", DeviceType.File)
                    dbBackupLogDatabase.SqlBackup(svrDatabase)

                    dbYukon.ExecuteNonQuery(" DBCC SHRINKFILE (N'" & DirectCast(dbYukon.LogFiles(0), Microsoft.SqlServer.Management.Smo.LogFile).Name & "' , 1)")

                    File.Delete(My.Settings.YUKON_SERVER_TEMP_DIRECTORY_PATH & "\log.bak")
                    WriteLog("TestApplication:RestoreDatabase", My.Settings.YUKON_SERVER_TEMP_DIRECTORY_PATH & "\log.bak" & " deleted")
                Catch ex As Exception
                    WriteLog("TestApplication:RestoreDatabase", "Backup log file error -" & ex.Message)
                End Try

            Catch ex As Exception
                WriteLog("TestApplication:RestoreDatabase", ex.Message)
            End Try
        End Sub


    Private Sub ProgressEventHandler(ByVal sender As Object, ByVal e As PercentCompleteEventArgs)
            WriteLog("TestApplication:RestoreDatabase", e.Percent.ToString + "% restored")    
        End Sub


    Saturday, November 19, 2011 6:08 AM

All replies

  • Can you complete a restore using the same file and raw tsql through the management studio, looking at the events it looks like you backup is not valid.
    http://www.virtualrealm.com.au - SQL Server DBA, MVP and Lightswitch Enthusiast.
    Sunday, November 20, 2011 12:22 AM
  • Yes .I did a restore using the same file through management studio. It was restored perfectly... Im trying your idea now... thank you for your ideas ....
    ________________________________________

     


    John M Mukesh
    Sunday, November 20, 2011 7:03 AM
  • Hello,

    Please, could you have a look at this thread ?

    http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/d6206cc2-1b3c-4ccf-b2f4-0a4ccb07f906

    It indicates how to find the root error of your 3401 error ( in this thread not enough space for the backup )

    I will try to do a research for the 3402 error

    Have a nice day


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.
    Sunday, November 20, 2011 7:54 PM
    Moderator