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 3

By Andy Warren

Restoring from a device works almost exactly the same as restoring from a file. The biggest difference is that if you're using a device, you just provide the device name, not the complete path to it. Assuming I've restored database Pubs to a device called Pubs, this is how you would use this code:

Call RestoreDBFromDevice("ANDY","Pubs","Pubs")

Sub RestoreDBFromDevice(ServerName As String, DBName As String,
DeviceNameToRestore As String)
Dim oServer As SQLDMO.SQLServer
Dim oRestore As SQLDMO.Restore
On Error GoTo Handler
'simple err checking
If ServerName = "" Or DBName = "" Or
DeviceNameToRestore = "" Then
MsgBox "You MUST provide server name, database name,
and the name of the device you want to restore",
vbInformation + vbOKOnly, "Error"
Exit Sub
End If
'open connection to server
Set oServer = New SQLDMO.SQLServer
With oServer
.LoginSecure = True
.Connect ServerName
End With
'also need a restore object
Set oRestore = New SQLDMO.Restore
'use the 'with' construct to minimize property lookups
With oRestore
'this is where your backup will be restored to
.Database = DBName
'same as EM or TSQL, you can restore database, file, or log, here we're going to
'use database
.Action = SQLDMORestore_Database
'this is the "force restore over existing database" option
.ReplaceDatabase = True
'this time we're using a device, so it's only a little different - the device name
'is all you need, not the path to where the device is stored
.Devices = DeviceNameToRestore
'do it
.SQLRestore oServer
End With
'standard clean up
Set oRestore = Nothing
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