We are trying to setup a central job server that will perform the backups, restores, and other automated tasks for all of our SQL servers. The goal is that we have one place to check the status of all of our jobs. I have a couple of problems, though.
The first is that, to backup a database on a remote server, we are using VBScript as an ActiveX Script type of step. The script uses SQLDMO. The jobs mostly run fine, but there seems to be a problem with concurrent jobs running against the same server. For instance, I have 4 backup jobs that I kick off at 7:00PM. One of them (not always the same one) will fail with:
Error Code: 0 Error Source= Microsoft SQL-DMO Error Description: [SQL-DMO]The application is busy. Error on Line 11. The step failed.
The databses are all quite small (master, msdb, and a couple of utility dbs we have). Are there threading issues in using SQL-DMO such that I can't run jobs concurrently? The remote server is SQL2000, so I don't believe I can use SMO. My script is at the end of this post.
My second problem is trying to remotely kick off a restore. Currently, when using the local scheduler, we use the RESTORE DATABASE command right after an ALTER DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE command. This command is necessary to kill any processes currently using the DB so that a restore will succeed. In DMO I can't find anyway to duplicate this functionality. Any ideas?
Thanks
Steve
-- Backup Script --
Dim sServer, sDatabase, sPath, sDumpFile, oSQLServer, oBackup
sServer = "PRDSQL01"
sDatabase = "master"
sPath = "E:\MSSQL\DUMP\"
sDumpFile = "master.dmp"
Set oSQLServer = CreateObject("SQLDMO.SQLServer")
Set oBackup = CreateObject("SQLDMO.BackUp")
oSQLServer.loginsecure = true
oSQLServer.Connect sServer
oBackup.Database = sDatabase
oBackup.Files = sPath & sDumpFile
oBackUp.BackupSetDescription = "Backup - " & sDatabase
oBackup.Initialize = "TRUE"
oBackup.SQLBackup oSQLServer
oSQLServer.Disconnect
Set sServer = nothing
Set sDatabase = nothing
Set sPath = nothing
Set sDumpFile = nothing
Set oSQLServer = nothing
Set oBackup = nothing
Hi Stev
Vimal , here ,
sir its helpful to take backup ,please tell me script for restore the taken dump file.
Vimal
