• Edit
• History
• Print
• Backlinks

Restore SQL Via DMO

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)&amp;lt;&amp;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 &amp;lt;&amp;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 

SQL | VB Script