Use the following script to restore a database. Simplier to use than Enterprise Manager, & less error prone.
'
' Description.
' ============
' This is a simple VBScript to restore a database.
'
' Usage
' =====
' Create a shortcut to this script, and edit the link to pass in the Server name,
' Database name & backup file to resore.
'
' If any of these are omitted, the script prompts you for them.
'
' It remembers the values for next time by writing/reading the registry.
'
' Example
' =======
' SQLRestoreViaDMO -s:local, -d:Pubs, -f:C:\backups\pubs.bak
' where '-s:' = server
' '-d:' = database
' '-f:' = backup file
'
' This was adapted from an on-line example for VB 6.0
' Copyright David Jackson 2004
' http://glossopian.co.uk
Option Explicit
Dim sServer
Dim sDatabase
Dim sBakFile
Dim oArg
Dim sSwitch
Dim sValue
Dim WShell
Dim bDoIt
Set WShell = CreateObject("Wscript.Shell")
For Each oArg In WScript.Arguments
sSwitch = LCase(Left(oArg,3))
sValue = Right(oArg,Len(oArg)-3)
Select Case sSwitch
Case "-s:"
sServer = sValue
Case "-d:"
sDatabase = sValue
Case "-f:"
sBakFile = sValue
Case Else WShell.Popup "Unknown switch", , "Database Restore", 16
ExitThisScript
End Select
Next
ReadWriteRegistry sServer, sDatabase, sBakFile
'Sense check this is what we REALLY want to do. You've got 10 seconds to decide...
bDoIT = WShell.Popup ("Restore " & sBakFile & " to " & sDatabase & " on " & sServer & "?", 10, "Database Restore", 4 + 32)
' if -1 then selection timed out
if bDoIT = 6 then
'We got to trust that the Server & DB exist, but we can check the file is there.
if ReportFileStatus(sBakFile) then
RestoreDBFromFile sServer, sDatabase, sBakFile
WShell.Popup "Restored " & sBakFile & " to " & sDatabase & " on " & sServer,, "Database Restore"
ExitThisScript
Else
WShell.Popup "Cannot find " & sBakFile, , "Database Restore", 16
ExitThisScript
End if
Else 'do we want to point to another database?
bDoIT = WShell.Popup ("Changes Settings?", 10, "Database Restore", 4 + 32 )
if bDoIT = 6 Then
'Clear current Settings
WShell.RegWrite "HKEY_CURRENT_USER\Software\Glossopian\ServerName", "", "REG_SZ"
WShell.RegWrite "HKEY_CURRENT_USER\Software\Glossopian\DataBase", "", "REG_SZ"
WShell.RegWrite "HKEY_CURRENT_USER\Software\Glossopian\bakFile", "", "REG_SZ"
sServer = ""
sDatabase = ""
sBakFile = ""
ReadWriteRegistry sServer, sDatabase, sBakFile
WShell.Popup "Please run again to use the new settings" ,, "Database Restore"
End if
End If
ExitThisScript
'---------------------------------------------------------------
Sub ReadWriteRegistry(sServer, sDatabase, sBakFile)
'---------------------------------------------------------------
On Error Resume Next
if Not Eval(sServer = "") then
WShell.RegWrite "HKEY_CURRENT_USER\Software\Glossopian\", 1, "REG_BINARY"
WShell.RegWrite "HKEY_CURRENT_USER\Software\Glossopian\ServerName", sServer, "REG_SZ"
End if
if Not Eval(sDatabase = "") then
WShell.RegWrite "HKEY_CURRENT_USER\Software\Glossopian\DataBase", sDatabase, "REG_SZ"
End if
if Not Eval(sBakFile = "") then
WShell.RegWrite "HKEY_CURRENT_USER\Software\Glossopian\bakFile", sBakFile, "REG_SZ"
End if
if Eval(sServer = "") then
sServer = WShell.RegRead("HKEY_CURRENT_USER\Software\Glossopian\ServerName")
Do While sServer = ""
sServer = InputBox("Which Server shall I restore to?")
Loop
WShell.RegWrite "HKEY_CURRENT_USER\Software\Glossopian\ServerName", sServer, "REG_SZ"
End if
if Eval(sDatabase = "") then
sDatabase = WShell.RegRead("HKEY_CURRENT_USER\Software\Glossopian\DataBase")
Do While sDatabase = ""
sDatabase = InputBox("Name of the database to restore?")
Loop
WShell.RegWrite "HKEY_CURRENT_USER\Software\Glossopian\DataBase", sDatabase, "REG_SZ"
End if
if Eval(sBakFile = "") then
sBakFile = WShell.RegRead("HKEY_CURRENT_USER\Software\Glossopian\bakFile")
Do While sBakFile = ""
sBakFile = InputBox("Which file shall I restore?")
Loop
WShell.RegWrite "HKEY_CURRENT_USER\Software\Glossopian\bakFile", sBakFile, "REG_SZ"
End if
On Error Goto 0 'Turn Error Checking back on
End Sub
'---------------------------------------------------------------
Sub ExitThisScript
'---------------------------------------------------------------
Set WShell = Nothing
Wscript.Quit
End Sub
'---------------------------------------------------------------
Sub RestoreDBFromFile(ServerName , DBName , BackupToRestore )
'---------------------------------------------------------------
Dim oServer
Dim oRestore
'On Error GoTo Handler
'simple err checking
If ServerName = "" Or DBName = "" Or BackupToRestore = "" Then
MsgBox "You MUST provide server name, database name, and the name of the bak file you want to restore", _
vbInformation + vbOKOnly, "Error"
Exit Sub
End If
'open connection to server
Set oServer = ImportObject("SQLDMO.SQLServer")
With oServer
.LoginSecure = True
.Connect ServerName
End With
'also need a restore object
Set oRestore = ImportObject("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 does a restore from a file instead of a device - note that we're still
'restoring a database, NOT a file group
.Files = BackupToRestore
'do it
.SQLRestore oServer
End With
'standard clean up
Set oRestore = Nothing
oServer.DisConnect
Set oServer = Nothing
Exit Sub
'Handler:
'If MsgBox(Err.Description & ". Would you like to continue?"
'Resume Next
'End If
End Sub
'****************************************************************
Function ImportObject(sClass)
' PURPOSE: Given a classname, this function will:
' + return a reference to the object
' + Import Typelib constants into global namespace
' DEPENDENCY: TLBINF32.DLL must be present and registered
' Derived from Michael Harris' Typelib extraction HTA
' WARNING: Some TLBs contain hundreds of constants!
' Alex K. Angelopoulos posted this on 10/31/02.
'
'Sample usage:
'<CODE>
'Set objIE = ImportObject("InternetExplorer.Application")
'</CODE>
'
' which is identical to the following code lines, except that
' all approximately 80 constants are defined.
'
'<CODE>
'Set objIE = CreateObject("InternetExplorer.Application")
'CONST CSC_UPDATECOMMANDS = -1
' ... many more CONST statements
'CONST SWFO_COOKIEPASSED = 4
'</CODE>
'
' Edited and modified to allow repeated calls with
' the same class. Paul Randall 11/21/02
Dim objTLIA 'TypeLib Info Application; TLBinf32.dll
Dim objTLII 'TypeLib Interface Info object for the parent of
' the object created from the specified class.
' Contains a collection of enumeration objects
Dim objCEnum 'One of the enumeration objects
' Contains a collection of constant objects
Dim objConstant 'One constant object in the enumeration object
Dim objObject 'The object specified by the class string passed
' to this routine.
'strMsg for obtaining the list of constants and their values
Dim strMsg 'List of constants and their values
strMsg = "Typelib constants for: " & sClass & vbcrlf & vbcrlf
Set objObject = CreateObject(sClass)
Set objTLIA = CreateObject("TLI.TLIApplication")
Set objTLII = objTLIA.InterfaceInfoFromObject(objObject).Parent
For Each objCEnum in objTLII.Constants
' We only want them if they are visible
If Left(objCEnum.Name, 1)&lt;&gt;"_" Then
strMsg = strMsg & "EnumName: " & objCEnum.Name & _
" contains " & objCEnum.Members.count & " items." & vbcrlf
For Each objConstant In objCEnum.Members
strMsg = strMsg & objConstant.name & " = " & objConstant.value & vbcrlf
On Error Resume Next
ExecuteGlobal "CONST " & objConstant.Name & "=" & objConstant.Value
if Err.Number = 1041 then
if eval(objConstant.Name & "=" & objConstant.Value) then
'Ignore unchanged values
else
MsgBox "Unexpected new value for TypeLib constant" & vbcrlf & _
"in Function ImportObject(" & sClass & ")" & vbcrlf & _
"Constant name = " & objConstant.Name & vbcrlf & _
"Old value = " & eval(objConstant.Name) & vbcrlf & _
"New value = " & objConstant.Value & vbcrlf & _
vbcrlf & "Quitting"
WScript.Quit
end if
elseif Err.Number &lt;&gt; 0 then
MsgBox "Unexpected error in " & _
"Function ImportObject(" & sClass & ")" & vbcrlf & _
"Error Number = " & Err.Number & vbcrlf & _
"Error Description = " & Err.Description & vbcrlf & _
"Error Source = " & Err.Source & vbcrlf & _
vbcrlf & "Quitting"
WScript.Quit
end if
On Error GoTo 0
Next
else
strMsg = strMsg & "EnumName: " & objCEnum.Name & _
" contains " & objCEnum.Members.count & " hidden items." & vbcrlf
End If
Next
'writeFile "LogFile.txt", strMsg
Set ImportObject = objObject
End Function
'Write string As a text file.
'----------------------------------------------------------
Function WriteFile(FileName, Contents)
'----------------------------------------------------------
Set oFS = CreateObject ("Scripting.FileSystemObject")
Dim OutStream
Set OutStream = oFS.OpenTextFile(FileName, 2, True)
OutStream.Write Contents
Set oFS = Nothing
End Function
'----------------------------------------------------------
Function ReportFileStatus(filespec)
'----------------------------------------------------------
Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
If (fso.FileExists(filespec)) Then
ReportFileStatus = True
Else
ReportFileStatus = False
End If
Set fso = Nothing
End Function