How can we help you today? How can we help you today?

Backup VBScript example

Hi,

Since version v4's extended stored procedure returns two result sets, a lot of people who were using VBScripts to back up databases were having trouble getting access to the second result set, since the previous version of SQL Backup only retruned a single result set.

I haven't found a way to do this using ADO in scripting, but I've found that I can use SQLDMO.dll to do it. SQLDMO is part of SQL 2000 client-side tools. Note that it's an optional component of SQL 2005, so SQL 2005 servers may not have sqldmo.dll installed.

Here is the script: 
'Run with cscript to avoid lots of message boxes!
Dim obServer
Dim obDatabase
Dim obQueryResults
Dim strBackupCommand, strServerName
Dim i

'enter your server name and backup command.

strServerName="BRIAN"
strBackupCommand="EXEC master..sqlbackup '-SQL ""BACKUP DATABASE [Northwind] TO DISK=''c:\nwtest.sqb'' WITH COMPRESSION=2, INIT""'"

Set obServer=CreateObject("SQLDMO.SQLServer") 
      obServer.LoginTimeout = 10 
      ' Windows NT security 
      obServer.LoginSecure = True 
      obServer.Connect strServerName
Set obDataBase=obServer.Databases("master") 

Set obQueryResults=obDatabase.ExecuteWithResults(strBackupCommand, LEN(strBackupCommand))

For i=1 to obQueryResults.Rows
	WScript.Echo(obQueryResults.GetColumnString(i,1))
Next
If obQueryResults.ResultSets > 1 Then
	obQueryResults.CurrentResultSet=2 'Move to the second result set
	For i=1 to obQueryResults.Rows
		WScript.Echo(obQueryResults.GetColumnString(i,1) &"=" &obQueryResults.GetColumnString(i,2))
	Next
End If

Set obDatabase=Nothing
Set obServer=Nothing
Brian Donahue
0

Comments

2 comments

Sort by
  • petey
    Sample using ADO:

    Set conn = CreateObject("ADODB.Connection")

    conn.ConnectionString = "Driver={SQL Server}; Server=.; Database = master;"
    conn.Open

    Set recordset = conn.Execute("EXEC master..sqlbackup '-sql ""BACKUP DATABASE pubs TO DISK = [e:\temp\pubs.sqb] WITH INIT""'")
    If not recordset.eof Then
    rsArray = recordset.GetRows()
    For f = 0 to UBound(rsArray, 2)
    output = output + rsArray(0, f) + Chr(13) + Chr(10)
    Next

    wscript.echo(output)
    End If

    Set recordset = recordset.NextRecordset
    If not recordset.eof Then
    output = ""
    rsArray = recordset.GetRows()
    For f = 0 to UBound(rsArray, 2)
    output = output + rsArray(0, f) + " = " + rsArray(1, f) + Chr(13) + Chr(10)
    Next

    wscript.echo(output)
    End If

    Set recordset = Nothing
    Set conn = Nothing
    petey
    0
  • Brian Donahue
    Hi Peter,

    I'll give this a go. Somebody told me that when they ran it, NextRecordset didn't return anything...
    Brian Donahue
    0

Add comment

Please sign in to leave a comment.