SQL DMO - Restoring a Database
February 27, 2001In 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:
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: