Allen White

Restoring a Database Backup to a new Database using SMO

In the MSDN SMO forum yesterday someone asked how to restore a backup to a new database without overwriting the existing database. The documentation isn't abundantly clear as to how to go about this so I created a small demo of how to do this.

First we need to connect to the server, then create a backup device object and set its properties.

        Dim srv As Server
        Dim srvConn As ServerConnection
        srv = New Server("MyServer")
        srvConn = srv.ConnectionContext
        srvConn.LoginSecure = True

        Dim bdi As BackupDeviceItem
        bdi = New BackupDeviceItem("AdventureWorks_backup_200609210229.bak", DeviceType.File)

Next, we need to define the Restore object, set properties for Recovery, add the backup device, and set the name of the recovered database.

        'Define a Restore object variable.
        Dim rs As Restore
        rs = New Restore
        'Set the NoRecovery property to False.
        rs.NoRecovery = False
        'Add the device that contains the full database backup to the Restore object.
        rs.Devices.Add(bdi)
        'Specify the database name.
        rs.Database = "AdventureWorksPrime"

Now, to place the database files into newly named files SMO uses an object called a RelocateFile object, and the Restore process uses an ArrayList of these objects to define where to place these files during restore. The LogicalFileName property must contain the database logical file name from the source database, and the PhysicalFileName property can be set to the desired location and file name for the new database file. Here I've used a separate RelocateFile object variable for the Data and Log files in the database.

        'Define the RelocateFile object variables
        Dim alRSFile As New RelocateFile
        Dim alRSLog As New RelocateFile
        'Set the FileName property appropriately
        alRSFile.LogicalFileName = "AdventureWorks_Data"
        alRSFile.PhysicalFileName = "d:\MSSQL\Data\AdWorks.mdf"
        alRSLog.LogicalFileName = "AdventureWorks_Log"
        alRSLog.PhysicalFileName = "d:\MSSQL\Data\AdWorks.ldf"
        'Add the RelocateFile objects to the Restore RelocateFiles collection
        rs.RelocateFiles.Add(alRSFile)
        rs.RelocateFiles.Add(alRSLog)
        'Restore the full database backup
        rs.SqlRestore(srv)

This allows you to build an SMO application to build duplicate databases in other locations for whatever reason you may have.

Allen