Database backup examples illustrate restore operations performed by using SQL-DMO.
This example illustrates a full database restore.
Full database restore is the first step in restoring a Microsoft® SQL Server™ database lost due to hardware failure or other extreme condition.
Database restore is constrained by the type of backup performed. This example illustrates a restore of a database backed up by using full database backup and no transaction log backup. When a transaction log backup maintenance strategy is used to create a chain of backup sets capturing point in time images, the initial full restore must indicate that the backup is the first in the series. For more information, see the Restoring a Database and Transaction Log Chain example later.
' Create a Restore object and set action and target database properties.
Dim oRestore As New SQLDMO.Restore
oRestore.Action = SQLDMORestore_Database
oRestore.Database = "Northwind"
' Example illustrates restore from a striped backup. Two source devices
' are specified. The full database backup is indicated as the first
' backup set by using the FileNumber property. Note: Device creation is
' not illustrated in this example.
oRestore.Devices = "[NorthDev1],[NorthDev2]"
oRestore.FileNumber = 1
' Optional. ReplaceDatabase property ensures that any existing copy
' of the database is overwritten.
oRestore.ReplaceDatabase = True
' Call SQLRestore method to perform the restore. In a production
' environment, consider wrapping the method call with a wait pointer
' or use Restore object events to provide feedback to the user.
'
' Note: Create and connect of SQLServer object used is not
' illustrated in this example.
oRestore.SQLRestore oSQLServer
This example illustrates performing a database restore, then applying a log backup chain to roll the database forward to its state at the last log backup.
When a transaction log backup maintenance strategy is used to create a chain of backup sets capturing point in time images, an initial full restore of the database must indicate that the backup is the first in the series. Each successive restore of a member of the log backup set chain is, until the final member, marked to indicate that it is not the last. The final restore is indicated as the last in the series.
Performing a restore of a database and transaction log backup set chain can be performed using a one or more Restore objects. This example illustrates using a single Restore object, reconfiguring the object as required, and calling the SQLRestore method multiple times.
' Create a Restore object and set action and target database properties
' for initial restore of the database.
Dim oRestore As New SQLDMO.Restore
oRestore.Action = SQLDMORestore_Database
oRestore.Database = "Northwind"
' Example illustrates restore from a striped backup. Two source devices
' are specified. The full database backup is indicated as the first
' backup set by using the FileNumber property. Note: Device creation is
' not illustrated in this example.
oRestore.Devices = "[NorthDev1],[NorthDev2]"
oRestore.FileNumber = 1
' Optional. ReplaceDatabase property ensures that any existing copy
' of the database is overwritten.
oRestore.ReplaceDatabase = True
' When restoring a database and log backup set chain, the LastRestore
' property is False for all but the last log chain restored.
oRestore.LastRestore = False
' Call SQLRestore method to perform the restore of the database. In a
' production environment, consider wrapping this entire series of
' method calls with a wait pointer or use Restore object events to
' provide feedback to the user.
'
' Note: Create and connect of SQLServer object used is not
' illustrated in this example.
oRestore.SQLRestore oSQLServer
' Reconfigure Restore object for log chain restoration by resetting the
' Action property.
oRestore.Action = SQLDMORestore_Log
' Example would restore the second backup set from the devices specified
' above.
oRestore.FileNumber = 2
' Setting LastRestore here is redundant, but emphasizes that this is the
' first in a chain of log backup sets.
oRestore.LastRestore = False
' Call SQLRestore method to perform the restore of the first chain
' member.
oRestore.SQLRestore oSQLServer
' Indicate the next member of the chain. In the example, it's the third
' backup set in the devices specified above.
oRestore.FileNumber = 3
' Set LastRestore to indicate that the member is the last in the chain.
oRestore.LastRestore = True
' Call SQLRestore method to perform the restore of the last chain
' member.
oRestore.SQLRestore oSQLServer
| Restore Object | SQLServer Object |