SQL DMO - Restoring a Database

February 27, 2001

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







The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers