Backup and Restore SQL database with SMO...

[lidds]

I am creating a backup of an SQL Server database using the following code:

Public Sub ExportDataToFile(ByVal strSourceServer As String, ByVal strBakFileName As String, ByVal WindowsAuth As Boolean, ByVal strUsername As String, ByVal strPassword As String, ByVal strDatabase As String)
        Dim sourceConnectionString As String = Nothing

        If WindowsAuth = True And strDatabase <> "" Then
            sourceConnectionString = "Data Source='" & strSourceServer & "';initial catalog='" & strDatabase & "';Trusted_Connection=True;"
        ElseIf WindowsAuth = True And strDatabase = "" Then
            sourceConnectionString = "Data Source='" & strSourceServer & "';Trusted_Connection=True;"
        Else
            sourceConnectionString = "Data Source='" & strSourceServer & "';initial catalog='" & strDatabase & "';user id='" & strUsername & "';password='" & strPassword & "'"
        End If

        Dim sourceConnBuilder As New SqlConnectionStringBuilder(sourceConnectionString)

        Dim serverConnection As ServerConnection
        If sourceConnBuilder.IntegratedSecurity Then
            serverConnection = New ServerConnection(sourceConnBuilder.DataSource)

            ' Windows Authentication
            serverConnection.LoginSecure = True
        Else
            serverConnection = New ServerConnection(sourceConnBuilder.DataSource, sourceConnBuilder.UserID, sourceConnBuilder.Password)
        End If

        server = New Server(serverConnection)
        Dim database As Database = server.Databases(sourceConnBuilder.InitialCatalog)

        'Reference the AdventureWorks2012 database.
        database = server.Databases(strDatabase)

        'Store the current recovery model in a variable.
        Dim recoverymod As Integer
        recoverymod = database.DatabaseOptions.RecoveryModel

        'Define a Backup object variable. 
        Dim bk As New Backup

        'Specify the type of backup, the description, the name, and the database to be backed up.
        bk.Action = BackupActionType.Database
        bk.BackupSetDescription = "Full backup of ReviewInsight " & strDatabase & " Database"
        bk.BackupSetName = strDatabase & " Backup"
        bk.Database = strDatabase
        bk.PercentCompleteNotification = 10

        AddHandler bk.PercentComplete, AddressOf BackupProgressEvent

        'Declare a BackupDeviceItem by supplying the backup device file name in the constructor, and the type of device is a file.
        Dim bdi As BackupDeviceItem
        bdi = New BackupDeviceItem(strBakFileName, DeviceType.File)

        'Add the device to the Backup object.
        bk.Devices.Add(bdi)

        'Set the Incremental property to False to specify that this is a full database backup.
        bk.Incremental = False

        'Specify that the log must be truncated after the backup is complete.
        bk.LogTruncation = BackupTruncateLogType.Truncate

        'Run SqlBackup to perform the full database backup on the instance of SQL Server.
        bk.SqlBackup(server)

        'Remove the backup device from the Backup object.
        bk.Devices.Remove(bdi)

        'Set the database recovery mode back to its original value.
        server.Databases(strDatabase).DatabaseOptions.RecoveryModel = recoverymod

        'Inform the user that the backup has been completed.
        MsgBox("Full Backup complete.")
        objFrmBackup.ProgressBar1.Value = 0
    End Sub

And then using the following code to restore the backup onto a different server and into a different database:

Public Sub RestoreDataFromFile(ByVal strDestServer As String, ByVal strBakFileName As String, ByVal WindowsAuth As Boolean, ByVal strUsername As String, ByVal strPassword As String, ByVal strDatabase As String)
        Dim destinationConnectionString As String = Nothing

        If WindowsAuth = True And strDatabase <> "" Then
            destinationConnectionString = "Data Source='" & strDestServer & "';initial catalog='" & strDatabase & "';Trusted_Connection=True;"
        ElseIf WindowsAuth = True And strDatabase = "" Then
            destinationConnectionString = "Data Source='" & strDestServer & "';Trusted_Connection=True;"
        Else
            destinationConnectionString = "Data Source='" & strDestServer & "';initial catalog='" & strDatabase & "';user id='" & strUsername & "';password='" & strPassword & "'"
        End If

        Dim destConnBuilder As New SqlConnectionStringBuilder(destinationConnectionString)

        Dim serverConnection As ServerConnection
        If destConnBuilder.IntegratedSecurity Then
            serverConnection = New ServerConnection(destConnBuilder.DataSource)

            ' Windows Authentication
            serverConnection.LoginSecure = True
        Else
            serverConnection = New ServerConnection(destConnBuilder.DataSource, destConnBuilder.UserID, destConnBuilder.Password)
        End If

        ' If no database specified then create a new database
        server = New Server(serverConnection)

        'Define a Restore object variable.
        Dim rs As Restore
        rs = New Restore

        'Set the NoRecovery property to true, so the transactions are not recovered.
        rs.NoRecovery = False

        'Verify that backup is not corrupt
        Dim MDFFile As String = Nothing
        Dim LOGFile As String = Nothing

        rs.Devices.AddDevice(strBakFileName, DeviceType.File)
        Dim verifySuccessful As Boolean = rs.SqlVerify(server)

        If verifySuccessful Then
            MessageBox.Show("Backup Verified!", "Info")

            Dim dt As DataTable = rs.ReadFileList(server)
            For Each row As DataRow In dt.Rows
                If row.Item("Type") = "D" Then
                    MDFFile = row.Item("LogicalName")
                ElseIf row.Item("Type") = "L" Then
                    LOGFile = row.Item("LogicalName")
                End If
            Next row
        Else
            MessageBox.Show("ERROR: Backup not verified!", "Error")
        End If

        'Specify the database name.
        rs.Database = strDatabase
        rs.PercentCompleteNotification = 10
        rs.Action = RestoreActionType.Database

        rs.RelocateFiles.Add(New RelocateFile(MDFFile, "C:\" & MDFFile & ".mdf"))
        rs.RelocateFiles.Add(New RelocateFile(LOGFile, "C:\" & LOGFile & ".ldf"))
        rs.ReplaceDatabase = False

        AddHandler rs.PercentComplete, AddressOf BackupProgressEvent

        'Restore the full database backup with no recovery.
        rs.SqlRestore(server)

        'Set te NoRecovery property to False.
        rs.NoRecovery = False

        'Inform the user that the Full Database Restore is complete.
        MsgBox("Full Database Restore complete.")
        objFrmBackup.ProgressBar1.Value = 0
    End Sub

The problem that I have is that I get the following error:

Microsoft.SqlServer.Management.Smo.FailedOperationException: Restore failed for Server 'UKRDGTEST01\RIS2014'. ---> Microsoft.SqlServer.Management.Smo.SmoException: System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing 'RISDEMO' database.

How I want this process to work is the following:

1. Use the above code to create a backup file - This works
2. The user creates a blank database on the new server
3. The user uses the above code to restore the backup file into this newly created database - This gives an error.

Could someone please help and let me know what I am doing wrong.

Thank you in advance

Simon

[techgnome]

Step one is to remove the database name from the connection string. You cannot/mustnot connect to the database prior to restoring it. Doing so will cause the DB to be in use and will prevent the restore from happening.

Step two - you have a verification, but even if there is an error, you're continuing anyways... might want to revisit that logic.

You also told it to not replace the database... but isn't that what you do want?

I'd also look to make sure this is creating the correct path:
rs.RelocateFiles.Add(New RelocateFile(MDFFile, "C:\" & MDFFile & ".mdf"))

Do you really keep your db files right in the C root?

You also only assume a single MDF/LDB, which depending on the control you have may or may not be correct.

Another thing, unless the new DB and the backedup DB have the exact same name, their MDF/LDB file names are going to be different. If you're going to restore to an existing database, then you need to make sure the destination MDF and LDB file names are correct.

-tg

[lidds]

tg,

Thank you for your help.

I have implemented your suggestions, see below:

 Public Sub RestoreDataFromFile(ByVal strDestServer As String, ByVal strBakFileName As String, ByVal WindowsAuth As Boolean, ByVal strUsername As String, ByVal strPassword As String, ByVal strDatabase As String)
        Dim destinationConnectionString As String = Nothing

        If WindowsAuth = True Then
            destinationConnectionString = "Data Source='" & strDestServer & "';Trusted_Connection=True;"
        Else
            destinationConnectionString = "Data Source='" & strDestServer & "';user id='" & strUsername & "';password='" & strPassword & "'"
        End If

        Dim destConnBuilder As New SqlConnectionStringBuilder(destinationConnectionString)

        Dim serverConnection As ServerConnection
        If destConnBuilder.IntegratedSecurity Then
            serverConnection = New ServerConnection(destConnBuilder.DataSource)

            ' Windows Authentication
            serverConnection.LoginSecure = True
        Else
            serverConnection = New ServerConnection(destConnBuilder.DataSource, destConnBuilder.UserID, destConnBuilder.Password)
        End If

        ' If no database specified then create a new database
        server = New Server(serverConnection)

        'Define a Restore object variable.
        Dim rs As Restore
        rs = New Restore

        'Set the NoRecovery property to true, so the transactions are not recovered.
        rs.NoRecovery = False

        'Verify that backup is not corrupt
        Dim MDFFile As String = Nothing
        Dim LOGFile As String = Nothing

        rs.Devices.AddDevice(strBakFileName, DeviceType.File)
        Dim verifySuccessful As Boolean = rs.SqlVerify(server)

        If verifySuccessful Then
            MessageBox.Show("Backup Verified!", "Info")

            Dim dt As DataTable = rs.ReadFileList(server)
            For Each row As DataRow In dt.Rows
                If row.Item("Type") = "D" Then
                    MDFFile = row.Item("LogicalName")
                ElseIf row.Item("Type") = "L" Then
                    LOGFile = row.Item("LogicalName")
                End If
            Next row
        Else
            MessageBox.Show("ERROR: Backup not verified!", "Error")
            Exit Sub
        End If

        'Specify the database name.
        rs.Database = strDatabase
        rs.PercentCompleteNotification = 10
        rs.Action = RestoreActionType.Database

        'rs.RelocateFiles.Add(New RelocateFile(MDFFile, "C:\" & MDFFile & ".mdf"))
        'rs.RelocateFiles.Add(New RelocateFile(LOGFile, "C:\" & LOGFile & ".ldf"))
        rs.ReplaceDatabase = True

        AddHandler rs.PercentComplete, AddressOf BackupProgressEvent

        'Restore the full database backup with no recovery.
        rs.SqlRestore(server)

        'Set te NoRecovery property to False.
        rs.NoRecovery = False

        'Inform the user that the Full Database Restore is complete.
        MsgBox("Full Database Restore complete.")
        objFrmBackup.ProgressBar1.Value = 0
    End Sub

However I am now getting the following error:

Microsoft.SqlServer.Management.Smo.FailedOperationException: Restore failed for Server 'UKRDGTEST01\RIS2014'. ---> Microsoft.SqlServer.Management.Smo.SmoException: System.Data.SqlClient.SqlError: Directory lookup for the file "D:\Software\MySoftware\Database\2014\RISDEMO.mdf" failed with the operating system error 21(The device is not ready.).

I guess this is because I have remarked out the rs.RelocateFiles lines. The reason why I have done this is because I do not understand why I need to specify these. As the new blank database has already been created and therefore the .mdf and .ldf files, why do I need to specify them? I also have an issue with specifying them because I want to be able to do this sql restore from a computer other than the actual computer / server that is running the SQL instance. Therefore the C drive for the files are the C drive of the server not the C drive of where the application is running.

For info, I do not use the C root to store my database, this is a test box only.

Thanks

Simon

[techgnome]

Ok... let me see if I can break this down... having just done a restore (ala SQL though)... I'll see if I can clear up a few things.

First, let's say I have a database ABC ... data file is ABC.mdf and the log file is ABC.ldf. If I create a backup of the database, it's "tied" to the database ABC. So when you do a restore it's going to (by default) look for ABC.mdf and ABC.ldf.

But let's say I am moving it to a new server.. or heck, even the same server. Let's say I want to make a second copy of the database but call it XYZ (data=XYZ.mdf; log=XYZ.ldf) ... if I try to restore it, it's going to want to overwrite the ABC.mdf and ABC.ldf... but they belong to a different database! so you have to tell it to MOVE the LogicalFile ABC_data to PhysicalFile XYZ.mdf and MOVE LogicalFile ABC_log to PHysicalFile XYZ.ldf ...

Now, I put MOVE in all caps because that's one of the clauses in the SQL command for restoring to a different database.
http://msdn.microsoft.com/en-us/library/ms186858.aspx

You can see an example of the Restore with Move option here:
http://msdn.microsoft.com/en-us/libr...b_n_move_files

It's restoring Adventureworks2012_Data to a new data (and log) file...

I'd suggest studying the RESTORE DATABASE SQL command a little and understanding what it can (and cannot) do... then some of the options in the SMO model will make sense.

-tg