Below is my simple script to backup a database, and the it seems to work fine. My question is....how can I capture the output as if I executed it from SQL Managment Studio? Or at least verify that it has completed successfully. I don't want to verify the physical file is sitting in the backup location with a fileexist command either as the command may be ran from a different server than the SQL server.
SQLDBServer = "MyServerName"
SQLDB = "MyDB"
SQLUser = "SA"
SQLPassword = "MyPassword"
TimeStamp = StrReplace (TimeYmdHms(),"/","-" )
TimeStamp = StrReplace (TimeStamp,":","-" )
TimeStamp = StrUpper(TimeStamp)
mySQLConn = CreateObject('ADODB.Connection')
mySQLCMD = createobject("ADODB.Command")
mySQLRS = CreateObject('ADODB.Recordset')
mySQLRS.CursorLocation = 3
mySQLConn.ConnectionString = "Provider=SQLOLEDB; Data Source=%SQLDBServer%;Password=%SQLPassword%;User ID=%SQLUser%;Initial Catalog=Master;"
adOpenStatic = 1
adLockReadOnly = 4
adCmdText = 1
mySQLConn.Open()
SQLText = StrCat("BACKUP DATABASE [%SQLDB%] TO DISK = '%SQLDB% %TimeStamp%.bak' WITH NOFORMAT, INIT, NAME = N'%SQLDB%-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10")
mySQLCMD.activeconnection = mySQLConn
mySQLCMD.commandtext = SQLText
mySQLRS = mySQLCMD.execute
;mySQLRS.Close
mySQLConn.Close()
mySQLRS = 0
mySQLConn = 0