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)
Else
MsgBox "Could not find file '" & sRestoreFile & "' - skipping restore of database " & oDatabase.Name, vbInformation + vbOKOnly
End If
End If
Next
'clean up!
oServer.DisConnect
Set oServer = Nothing
Exit Sub
Handler:
If MsgBox(Err.Description & ". Would you like to continue?", vbInformation +
vbYesNo) = vbYes Then
Resume Next
End If
End Sub
|
» 
Comment and Contribute