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

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

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Feb 27, 2001

SQL DMO - Restoring a Database - Page 4

By Andy Warren

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)
MsgBox "Could not find file '" & sRestoreFile & "' - skipping restore of database " & oDatabase.Name, vbInformation + vbOKOnly
End If
End If

'clean up!
Set oServer = Nothing

Exit Sub

If MsgBox(Err.Description & ". Would you like to continue?", vbInformation + vbYesNo) = vbYes Then
Resume Next
End If

End Sub

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