Backup VBScript example
Brian Donahue
Posts: 6,590 Bronze 1
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:
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
Comments
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
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
I'll give this a go. Somebody told me that when they ran it, NextRecordset didn't return anything...