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

 

[chris_asa]

into a different database

That is not an option!
RESTORE can "steamroller over a different database" or "create a different database".

I use SQL-Agent and native backups so I can't help you with SMO equivalents, but the steamroller approach is however SMO implements the WITH REPLACE option of restore.
Per MS:
Restore normally prevents accidentally overwriting a database with a different database. If the database specified in a RESTORE statement already exists on the current server and the specified database family GUID differs from the database family GUID recorded in the backup set, the database is not restored. This is an important safeguard.
REPLACE overrides the safeguards.

You could avoid the relatively dangerous REPLACE steamroller by creating the new db with the name and location of your choice using the equivalent of:
RESTORE DATABASE DoesNotExistYet
FROM TheBackup
WITH RECOVERY -- assuming no logs to restore
MOVE 'TheBackup' TO 'C:\here\there\DoesNotExistYet.MDF'; -- edited to add the closing '


I have several concerns about your approach (truncating the log, users issuing backup commands, ad hoc full backups without the WITH COPY ONLY option, code that looks like it was intended to change the recovery model of the source db, even the concept of users having permission to change recovery model), but you didn't ask about that stuff so I won't interfere.

Chris

 

[chris_asa]

I see you posted the same question here and you were pointed to the MOVE and REPLACE options in SMO-speak.

One additional item you really should look at is BACKUP WITH COPY ONLY in SMO
Use the COPY ONLY option when running ad hoc backups outside of the db maintenance plan to avoid trashing the DR plan for the source db.

Chris