Using DMO to Restore a Database

Total article views: 23887 | Views in the last 30 days: 55

 Rate this |   Join the discussion |  Briefcase

In this article I'd like to present examples of how to restore a database using either a file or a device using SQL-DMO. Perhaps the first thing you're wondering is why do I need to use DMO to restore? Enterprise Manager has a very good restore interface for the occasional restore, and if I need to do it on a scheduled basis I can just execute some T-SQL via Query Analyzer or OSQL.

I think there are a couple scenario's where you'll find DMO a very useful method to employ:

• You need to give a user the ability to restore at any time, but you need to make sure they can only restore one database. A common example might be a developer that is working on a new database project. You can hide everything in a simple VB executable with a simple interface, like this:

• You need to restore multiple databases. Let's say for some reason you need to restore ALL of your user databases from your most recent backup. How long would it take to do that using Enterprise Manager? Or to write a script to do it? Using DMO, it's a piece of cake!

I've got three samples to help you get started. The first shows how to restore a single database from a file backup. The second is just a minor variation that shows how to do a restore from a device. The third example shows how to restore all user databases by leveraging the code from the first example. These are all written to run in VB. If you need to run them as a job you will have to convert to VBScript by removing all the data typing (Change Dim OServer as SQLDMO.SQLServer to just Dim oServer) and also changing the "as New" to CreateObject.

Here are a couple of tips that may help if you decide to implement your own DMO restore solution:

• Actually do the task once in Enterprise Manager or QueryAnalyzer. Write down each option you checked, selected, etc. This will give you an idea of what properties and methods to look for - there are a lot of options when you think about it!
• Ideally you should write and debug your code in VB first, then port to VBScript if you need to. With VB you can step through the code and make use of debug.print statements. If you're writing directly in VBScript, use the Msgbox statement to help you watch program execution - put one after every line of code if you need to!
• Remember that the user running the code/job has to have the necessary permissions

This sub allows you to restore a file by passing it the name of your server, the database you are restoring, and the name of the file you are restoring from. Here is how you would use it:

Call RestoreDBFromFile("ANDY","Pubs","C:\backup\pubs.bak")

Sub RestoreDBFromFile(ServerName As String, DBName As String, BackupToRestore As String)
Dim oServer As SQLDMO.SQLServer
Dim oRestore As SQLDMO.Restore  
On Error GoTo Handler
 
'simple err checking
If ServerName = "" Or DBName = "" Or BackupToRestore = "" Then
MsgBox "You MUST provide server name, database name, and the name of the bak file you want to restore", vbInformation + vbOKOnly, "Error"
Exit Sub
End If
 
'open connection to server
Set oServer = New SQLDMO.SQLServer
With oServer
.LoginSecure = True
.Connect ServerName
End With
 
'also need a restore object
Set oRestore = New SQLDMO.Restore
 
'use the 'with' construct to minimize property lookups
With oRestore
'this is where your backup will be restored to
.Database = DBName
'same as EM or TSQL, you can restore database, file, or log, here we're going to
'use database
.Action = SQLDMORestore_Database
'this is the "force restore over existing database" option
.ReplaceDatabase = True
'this does a restore from a file instead of a device - note that we're still
'restoring a database, NOT a file group
.Files = BackupToRestore
'do it
.SQLRestore oServer
End With
 
'standard clean up
Set oRestore = Nothing
oServer.DisConnect
Set oServer = Nothing
 
Exit Sub
 
Handler:
If MsgBox(Err.Description & ". Would you like to continue?", vbInformation + vbYesNo) = vbYes Then
Resume Next
End If
 
End Sub

Restoring from a device works almost exactly the same as restoring from a file. The biggest difference is that if you're using a device, you just provide the device name, not the complete path to it. Assuming I've restored database Pubs to a device called Pubs, this is how you would use this code:

Call RestoreDBFromDevice("ANDY","Pubs","Pubs")

Sub RestoreDBFromDevice(ServerName As String, DBName As String, DeviceNameToRestore As String) Dim oServer As SQLDMO.SQLServer Dim oRestore As SQLDMO.Restore On Error GoTo Handler   'simple err checking If ServerName = "" Or DBName = "" Or DeviceNameToRestore = "" Then MsgBox "You MUST provide server name, database name, and the name of the device you want to restore", vbInformation + vbOKOnly, "Error" Exit Sub End If   'open connection to server Set oServer = New SQLDMO.SQLServer With oServer .LoginSecure = True .Connect ServerName End With   'also need a restore object Set oRestore = New SQLDMO.Restore   'use the 'with' construct to minimize property lookups With oRestore 'this is where your backup will be restored to .Database = DBName 'same as EM or TSQL, you can restore database, file, or log, here we're going to 'use database .Action = SQLDMORestore_Database 'this is the "force restore over existing database" option .ReplaceDatabase = True 'this time we're using a device, so it's only a little different - the device name 'is all you need, not the path to where the device is stored .Devices = DeviceNameToRestore 'do it .SQLRestore oServer End With   'standard clean up Set oRestore = Nothing oServer.DisConnect Set oServer = Nothing   Exit Sub   Handler: If MsgBox(Err.Description & ". Would you like to continue?", vbInformation + vbYesNo) = vbYes Then Resume Next End If   End Sub

Now that you've had a chance to take a look at how to restore a single database, here is the code that will restore all of your user databases. This takes advantage of the RestoreDBFromFile SUB that we've already written to do the actual restore, we just need a way to pass it the name of each database we want to restore. For this example I'm looping through the databases collection and testing the systemobject property so that I only process user created databases. You could easily change this to restore based on a pattern (restore all db's that begin with A) or by reading the list of databases from a configuration table you maintain. 

One thing about this example is that I'm assuming the *.bak file is named after the database. Depending on your backup strategy you may need to do additional work here. For example, my naming convention for full backups is FULL_dbname_yyyymmdd_hhmm.bak. This easily allows me to see that it's a full backup and to make sure that Im choosing the right file for a restore. A more robust solution would be to use the backup related tables from MSDB (backupmediafamily, backupfiles, etc) to identify the most recent backup, it's type (file or device), and the name.

Using it is easy:

Call RestoreAll("Andy")

Sub RestoreAll(ServerName As String) '2/24/01 law 'Sample code to show how to restore all databases. Please use with care! Requires 'a reference to Microsoft SQL-DMO. Code tested on SQL 2000. Dim oServer As SQLDMO.SQLServer Dim oDatabase As SQLDMO.Database Dim sRestoreFile As String On Error GoTo Handler 'simple err checking If ServerName = "" Then MsgBox "You MUST provide the server name.", vbInformation + vbOKOnly, "Error" Exit Sub End If 'open connection to server Set oServer = New SQLDMO.SQLServer With oServer .LoginSecure = True .Connect ServerName End With 'cycle through all databases For Each oDatabase In oServer.Databases 'for this example we only want to restore user created databases If oDatabase.SystemObject = False Then 'use our sub to do the work. Here I'm assuming that we have previously done a 'file based backup to the folder c:\backup, where the name of the backup file 'is simply the databasename with ".bak" appended sRestoreFile = "C:\backup\" & oDatabase.Name & ".bak" If Dir$(sRestoreFile) <> "" Then Call RestoreDBFromFile(ServerName, oDatabase.Name, sRestoreFile) Else MsgBox "Could not find file '" & sRestoreFile & "' - skipping restore of database " & oDatabase.Name, vbInformation + vbOKOnly End If End If Next 'clean up! oServer.DisConnect Set oServer = Nothing Exit Sub Handler: If MsgBox(Err.Description & ". Would you like to continue?", vbInformation + vbYesNo) = vbYes Then Resume Next End If End Sub

Total article views: 23887 | Views in the last 30 days: 55