Backup and Restore using VBScript (ActiveX)

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

Backup and Restore using VBScript (ActiveX)

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

Backup - [WildPacket]

New on SQL. Running the BlackBerry DB on this SQL2000.
Within the SQL Enterprise Console I right click on the BESMgmt DB and then
click All tasks -> Backup DB -> in destination backup to it shows me the
location and a file named Backup4Aug04. I want to rename this file?
Second I see the 2 files with *.TRN and *.BAK which I know what these files
are.
The weird thing is along with these 2 files and Backup4Aug04 the date stamp
changes to the current date daily. and the Backup4Aug04 is like 12 GB and I
am sure our DB is not that big.
I want to know what this Backup4Aug04 file is and what is it doing?
Advise please.
Thank you.WILDPACKET wrote:
> New on SQL. Running the BlackBerry DB on this SQL2000.
> Within the SQL Enterprise Console I right click on the BESMgmt DB and then
> click All tasks -> Backup DB -> in destination backup to it shows me the
> location and a file named Backup4Aug04. I want to rename this file?
> Second I see the 2 files with *.TRN and *.BAK which I know what these files
> are.
> The weird thing is along with these 2 files and Backup4Aug04 the date stamp
> changes to the current date daily. and the Backup4Aug04 is like 12 GB and I
> am sure our DB is not that big.
> I want to know what this Backup4Aug04 file is and what is it doing?
> Advise please.
> Thank you.
Sounds like you have a scheduled job running that is doing your backups
(this is a good thing), which would explain the transaction log backups
with date/time stamps. I would also say that this job is appending full
backups to Backup4Aug04 (thus the 12GB size) instead of overwriting each
time.
You can manually run a backup, as you're attempting to do, and can
safely remove Backup4Aug04 from the device list and add your own file.|||WILDPACKET,
> Within the SQL Enterprise Console I right click on the BESMgmt DB and then
> click All tasks -> Backup DB -> in destination backup to it shows me the
> location and a file named Backup4Aug04. I want to rename this file?
You can remove it or add a new one that will be the new destination (if
selected).
> Second I see the 2 files with *.TRN and *.BAK which I know what these files
> are.
- .TRN transaction log backup
- .BAK database backup
> The weird thing is along with these 2 files and Backup4Aug04 the date stamp
> changes to the current date daily. and the Backup4Aug04 is like 12 GB and I
> am sure our DB is not that big.
If you select "Append to media", then every backup will be added and the
previous stay intact. If you select "Overwrite existing media", then the
media will be overwritten with the new backup and no previous will be
available.
AMB
"WILDPACKET" wrote:
> New on SQL. Running the BlackBerry DB on this SQL2000.
> Within the SQL Enterprise Console I right click on the BESMgmt DB and then
> click All tasks -> Backup DB -> in destination backup to it shows me the
> location and a file named Backup4Aug04. I want to rename this file?
> Second I see the 2 files with *.TRN and *.BAK which I know what these files
> are.
> The weird thing is along with these 2 files and Backup4Aug04 the date stamp
> changes to the current date daily. and the Backup4Aug04 is like 12 GB and I
> am sure our DB is not that big.
> I want to know what this Backup4Aug04 file is and what is it doing?
> Advise please.
> Thank you.|||Thank you all for your reply. I got it.
"Alejandro Mesa" wrote:
> WILDPACKET,
> > Within the SQL Enterprise Console I right click on the BESMgmt DB and then
> > click All tasks -> Backup DB -> in destination backup to it shows me the
> > location and a file named Backup4Aug04. I want to rename this file?
> You can remove it or add a new one that will be the new destination (if
> selected).
> > Second I see the 2 files with *.TRN and *.BAK which I know what these files
> > are.
> - .TRN transaction log backup
> - .BAK database backup
> > The weird thing is along with these 2 files and Backup4Aug04 the date stamp
> > changes to the current date daily. and the Backup4Aug04 is like 12 GB and I
> > am sure our DB is not that big.
> If you select "Append to media", then every backup will be added and the
> previous stay intact. If you select "Overwrite existing media", then the
> media will be overwritten with the new backup and no previous will be
> available.
>
> AMB
> "WILDPACKET" wrote:
> > New on SQL. Running the BlackBerry DB on this SQL2000.
> >
> > Within the SQL Enterprise Console I right click on the BESMgmt DB and then
> > click All tasks -> Backup DB -> in destination backup to it shows me the
> > location and a file named Backup4Aug04. I want to rename this file?
> >
> > Second I see the 2 files with *.TRN and *.BAK which I know what these files
> > are.
> >
> > The weird thing is along with these 2 files and Backup4Aug04 the date stamp
> > changes to the current date daily. and the Backup4Aug04 is like 12 GB and I
> > am sure our DB is not that big.
> >
> > I want to know what this Backup4Aug04 file is and what is it doing?
> >
> > Advise please.
> >
> > Thank you.|||I am blowing away my SQL server because it is running on Win2000 server and I
want to install Win2003 and then reinstall SQL 2000. Is there a free
too/utility which can assist me in perfroming all the proper
documentation/configuration?
Thank you.
"Alejandro Mesa" wrote:
> WILDPACKET,
> > Within the SQL Enterprise Console I right click on the BESMgmt DB and then
> > click All tasks -> Backup DB -> in destination backup to it shows me the
> > location and a file named Backup4Aug04. I want to rename this file?
> You can remove it or add a new one that will be the new destination (if
> selected).
> > Second I see the 2 files with *.TRN and *.BAK which I know what these files
> > are.
> - .TRN transaction log backup
> - .BAK database backup
> > The weird thing is along with these 2 files and Backup4Aug04 the date stamp
> > changes to the current date daily. and the Backup4Aug04 is like 12 GB and I
> > am sure our DB is not that big.
> If you select "Append to media", then every backup will be added and the
> previous stay intact. If you select "Overwrite existing media", then the
> media will be overwritten with the new backup and no previous will be
> available.
>
> AMB
> "WILDPACKET" wrote:
> > New on SQL. Running the BlackBerry DB on this SQL2000.
> >
> > Within the SQL Enterprise Console I right click on the BESMgmt DB and then
> > click All tasks -> Backup DB -> in destination backup to it shows me the
> > location and a file named Backup4Aug04. I want to rename this file?
> >
> > Second I see the 2 files with *.TRN and *.BAK which I know what these files
> > are.
> >
> > The weird thing is along with these 2 files and Backup4Aug04 the date stamp
> > changes to the current date daily. and the Backup4Aug04 is like 12 GB and I
> > am sure our DB is not that big.
> >
> > I want to know what this Backup4Aug04 file is and what is it doing?
> >
> > Advise please.
> >
> > Thank you.|||See if this helps.
How to move databases between computers that are running SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
AMB
"WILDPACKET" wrote:
> I am blowing away my SQL server because it is running on Win2000 server and I
> want to install Win2003 and then reinstall SQL 2000. Is there a free
> too/utility which can assist me in perfroming all the proper
> documentation/configuration?
> Thank you.
>
>
> "Alejandro Mesa" wrote:
> > WILDPACKET,
> >
> > > Within the SQL Enterprise Console I right click on the BESMgmt DB and then
> > > click All tasks -> Backup DB -> in destination backup to it shows me the
> > > location and a file named Backup4Aug04. I want to rename this file?
> >
> > You can remove it or add a new one that will be the new destination (if
> > selected).
> >
> > > Second I see the 2 files with *.TRN and *.BAK which I know what these files
> > > are.
> >
> > - .TRN transaction log backup
> > - .BAK database backup
> >
> > > The weird thing is along with these 2 files and Backup4Aug04 the date stamp
> > > changes to the current date daily. and the Backup4Aug04 is like 12 GB and I
> > > am sure our DB is not that big.
> >
> > If you select "Append to media", then every backup will be added and the
> > previous stay intact. If you select "Overwrite existing media", then the
> > media will be overwritten with the new backup and no previous will be
> > available.
> >
> >
> > AMB
> >
> > "WILDPACKET" wrote:
> >
> > > New on SQL. Running the BlackBerry DB on this SQL2000.
> > >
> > > Within the SQL Enterprise Console I right click on the BESMgmt DB and then
> > > click All tasks -> Backup DB -> in destination backup to it shows me the
> > > location and a file named Backup4Aug04. I want to rename this file?
> > >
> > > Second I see the 2 files with *.TRN and *.BAK which I know what these files
> > > are.
> > >
> > > The weird thing is along with these 2 files and Backup4Aug04 the date stamp
> > > changes to the current date daily. and the Backup4Aug04 is like 12 GB and I
> > > am sure our DB is not that big.
> > >
> > > I want to know what this Backup4Aug04 file is and what is it doing?
> > >
> > > Advise please.
> > >
> > > Thank you.