SQLServerCentral Article

Using DMO to Restore a Database

,

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

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating