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")

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")