数据库仍为空

Question

步骤 1：我使用此代码片段备份数据库（效果很好）

Public Shared Sub BackupDatabase()
        Dim sConnect As String = My.Settings.LICConnectionString
        Dim dbName As String

        Using cnn As New SqlConnection(sConnect)
            cnn.Open()
            dbName = cnn.Database.ToString()

            Dim sc As New ServerConnection(cnn)
            Dim sv As New Server(sc)

            ' Check that I'm connected to the user instance
            Console.WriteLine(sv.InstanceName.ToString())

            ' Create backup device item for the backup
            Dim bdi As New BackupDeviceItem("C:\Backup\LIC.bak", DeviceType.File)

            ' Create the backup informaton
            Dim bk As New Backup()
            bk.Devices.Add(bdi)
            bk.Action = BackupActionType.Database
            bk.BackupSetDescription = "SQL Express is a great product!"
            bk.BackupSetName = "SampleBackupSet"
            bk.Database = dbName
            bk.ExpirationDate = New Date(2007, 5, 1)
            bk.LogTruncation = BackupTruncateLogType.Truncate

            ' Run the backup
            bk.SqlBackup(sv)
            MsgBox("Your backup is complete.")
        End Using
    End Sub

步骤 2：我删除表中的所有数据（因此数据库为空）。

步骤 3：使用此代码片段从备份文件恢复数据库（它不会抛出任何错误并且执行正常）

 Private Sub Restore(ByVal ConnectionString As String, ByVal DatabaseFullPath As String, ByVal backUpPath As String)
        Using con As New SqlConnection(ConnectionString)
            con.Open()

            Dim UseMaster As String = "USE master"
            Dim UseMasterCommand As New SqlCommand(UseMaster, con)
            UseMasterCommand.ExecuteNonQuery()

            Dim Alter1 As String = "ALTER DATABASE [" & DatabaseFullPath & "] SET Single_User WITH Rollback Immediate"
            Dim Alter1Cmd As New SqlCommand(Alter1, con)
            Alter1Cmd.ExecuteNonQuery()

            Dim Restore As String = "RESTORE DATABASE [" & DatabaseFullPath & "] FROM DISK = N'" & backUpPath & "' WITH  FILE = 1,  NOUNLOAD,  STATS = 10"
            Dim RestoreCmd As New SqlCommand(Restore, con)
            RestoreCmd.ExecuteNonQuery()

            Dim Alter2 As String = "ALTER DATABASE [" & DatabaseFullPath & "] SET Multi_User"
            Dim Alter2Cmd As New SqlCommand(Alter2, con)
            Alter2Cmd.ExecuteNonQuery()

            MsgBox("Successful")
        End Using
    End Sub

但它不会显示表中的数据...我的意思是在恢复数据库后我应该得到旧的备份文件中的数据...但数据库表仍然是空的..

你能指导我吗？

Answer 1

我相信你的问题是你试图在 SQL 语句中使用数据库路径而不是数据库名称。

假设您的连接字符串正在打开您希望最初恢复到的数据库，则恢复方法可以重写如下：

Private Sub Restore(ByVal ConnectionString As String, ByVal DatabaseFullPath As String, ByVal backUpPath As String)
    Using con As New SqlConnection(ConnectionString)
        con.Open()

        Dim sDatabaseName As String

        sDatabaseName = con.Database

        Dim UseMaster As String = "USE master"
        Dim UseMasterCommand As New SqlCommand(UseMaster, con)
        UseMasterCommand.ExecuteNonQuery()

        Dim Alter1 As String = "ALTER DATABASE [" & sDatabaseName & "] SET Single_User WITH Rollback Immediate"
        Dim Alter1Cmd As New SqlCommand(Alter1, con)
        Alter1Cmd.ExecuteNonQuery()

        Dim Restore As String = "RESTORE DATABASE [" & sDatabaseName & "] FROM DISK = N'" & backUpPath & "' WITH  FILE = 1,  NOUNLOAD,  STATS = 10"
        Dim RestoreCmd As New SqlCommand(Restore, con)
        RestoreCmd.ExecuteNonQuery()

        Dim Alter2 As String = "ALTER DATABASE [" & sDatabaseName & "] SET Multi_User"
        Dim Alter2Cmd As New SqlCommand(Alter2, con)
        Alter2Cmd.ExecuteNonQuery()

        MsgBox("Successful")
    End Using
End Sub