Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Feb 27, 2001

SQL DMO - Restoring a Database

By Andy Warren

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

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM