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
