Backup and Restore SQL database with SMO...
Backup and Restore SQL database with SMO...
Backup and Restore SQL database with SMO...
Backup and Restore SQL database with SMO...
Backup and Restore SQL database with SMO...
Backup and Restore SQL database with SMO... Backup and Restore SQL database with SMO... Backup and Restore SQL database with SMO... Backup and Restore SQL database with SMO... Backup and Restore SQL database with SMO... Backup and Restore SQL database with SMO... Backup and Restore SQL database with SMO... Backup and Restore SQL database with SMO...
Backup and Restore SQL database with SMO... Backup and Restore SQL database with SMO...
Backup and Restore SQL database with SMO...
Go Back  Xtreme Visual Basic Talk > > > Backup and Restore SQL database with SMO...


Reply
 
Thread Tools Display Modes
  #1  
Old 07-30-2014, 04:14 AM
lidds lidds is offline
Centurion
 
Join Date: May 2004
Posts: 100
Default Backup and Restore SQL database with SMO...


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

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:

Code:
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
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Backup and Restore SQL database with SMO...
Backup and Restore SQL database with SMO...
Backup and Restore SQL database with SMO... Backup and Restore SQL database with SMO...
Backup and Restore SQL database with SMO...
Backup and Restore SQL database with SMO...
Backup and Restore SQL database with SMO... Backup and Restore SQL database with SMO... Backup and Restore SQL database with SMO... Backup and Restore SQL database with SMO... Backup and Restore SQL database with SMO... Backup and Restore SQL database with SMO... Backup and Restore SQL database with SMO...
Backup and Restore SQL database with SMO...
Backup and Restore SQL database with SMO...
 
Backup and Restore SQL database with SMO...
Backup and Restore SQL database with SMO...
 
-->