SQL vbs backup script not working

I have used this script on multiple SQL servers, 2000 and 2005. I have a SQL 2000 server (w2k3 R2 SP2) which I have been unable to get any sort of automated backup working for months. I have to right-click each DB (about 20 of them) and choose ALL TASKS --> BACKUP DATABASE just to back them up.

Every time I set aside time to work on it, I encounter the same thing as before and not really sure where to start. Event viewer gives me nothing but I do get a WSH error...

[IMG]http://i632.photobucket.com/albums/uu44/sunspotresorts/SQL_ERROR.jpg[/IMG]

Here is the script with the server name and password replaced with SERVER and PASSWORD:

--------------------------------------------------------

Dim Server(0)
Server(0) = "DRIVER={SQL Server};Server=SERVER;Database=master;uid=SQLBackup;pwd=PASSWORD"

Dim BackupLocation(0)
BackupLocation(0) = "F:\Baks"

Dim Exclusions(0)
Exclusions(0) = ""


For t = 0 to UBOUND(Server)
    set Connection = CreateObject("ADODB.Connection")
    Connection.Open Server(t)
    set rs = CreateObject("ADODB.Recordset")
    rs.Open "select name, status from sysdatabases where UPPER(name) not in ('master', 'model', 'msdb', 'tempdb')", Connection, 3, 2, 1
    Do
        myExclusions = Split(Exclusions(t), ",")
        FoundExclusion = False
        For x = 0 to UBOUND(myExclusions)
            If UCASE(myExclusions(x)) = UCASE(rs.fields("name")) Then FoundExclusion = true
        Next


        
If FoundExclusion = False Then Connection.Execute "BACKUP DATABASE [" & rs.fields("name") & "] TO  DISK = N'" & BackupLocation(t) & rs.fields("name") & ".bak' WITH  INIT ,  NOUNLOAD ,  NAME = N'" & rs.fields("name") & " backup',  NOSKIP ,  STATS = 10,  NOFORMAT"


        rs.movenext
    Loop Until rs.EOF
    rs.Close
    Connection.Close
Next

--------------------------------------------------------

• http://i632.photobucket.com/albums/uu44/sunspotresorts/SQL_ERROR.jpg

A couple of things not sure will help- is "Connection" not a reserved word?
eg. replace connection with dbConn or something else

As a one off replace your Connection.Open Server(t) with

Connection.open "Server(0) = "DRIVER={SQL Server};Server=SERVER;Database=master;uid=SQLBackup;pwd=PASSWORD"

And see if this works.

Also make sure you password and login can access the Master DB and its not just a user for the DB you are trying to backup.

After that another option is to try and setup a file DSN and use the connection data from that to build your connection string.


 

"I'm living so far beyond my income that we may almost be said to be living apart"

looking at your error message, it seems pretty obvious that your connection string is wrong.

look at www.connectionstrings.com and check your username/passwords.

If that doesn't work, check that your server is setup to allow remote connections.

--------------------
Procrastinate Now!

thanks for the replies... i'll check on both and report back asap.