SQL Job to run .vsb

Question

Post reply

SQL Job to run .vsb

Vendage1

Valued Member

Points: 62
More actions
November 20, 2014 at 9:59 am

#295248

Hello everyone. I am writing with the hope that I can get some assistance from folks who are much more knowledgeable on a topic like this.
What I'm trying to do is run a job that calls a .vsb command on a remote server to shut down application services, then backup the database on the DB server, then start the services again with another .vsb command to the remote app server.
SQL Job
Step 1 - .vsb to remote server to stop app services
Step 2 - Backup DB's on local DB server
Step 3 - .vsb to remote server to start app services
Here is the job Step 1 command I am trying to run to stop the app services.
EXEC master...xp_cmdshell '\\SERVERNAME\c$\Support\Applicaiton\Backup\cscript.exe PauseDmWebService.vsb'
Any help would be great!

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Simple question.

Why do you want to stop app services to backup the DB? Backups can be done online.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Vendage1 Valued Member Points: 62 More actions · Answer 2

Luis Cazares (11/20/2014)
Simple question.
Why do you want to stop app services to backup the DB? Backups can be done online.

I don't want to. I would prefer not having to go down this path, but the application vendor has a requirement that states the application services have to be off_line before the backup is done. Personally believe this is a lesson in futility, but management wants it done so I am doing what I'm told.

Thanks for the question!

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

Don't you hate those vendors? 😀

Have you tried changing the type of the step to an ActiveX Script or Operating System?

What is the problem that you have?

I suppose you're using PauseDMWebService.vbs and ResumeDMWebService.vbs?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Vendage1 Valued Member Points: 62 More actions · Answer 4

Luis Cazares (11/20/2014)
Don't you hate those vendors? 😀
Have you tried changing the type of the step to an ActiveX Script or Operating System?
What is the problem that you have?
I suppose you're using PauseDMWebService.vbs and ResumeDMWebService.vbs?

I am using both of those .vbs files. My issue is getting SQL to actually run the .vsb files. So far I am having issues reaching out to the remote server. I believe my syntax is correct on the SQL side of things....I think...

EXEC master...xp_cmdshell '\\SERVERNAME\c$\Support\Application\Backup\cscript.exe PauseDmWebService.vsb'

.vsb files:

PauseDMWebService.vbs

ResumeDMWebService.vbs

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

Have you noticed the vsb instead of vbs?

Does the account used by xp_cmdshell has permissions to execute the scripts?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Vendage1 Valued Member Points: 62 More actions · Answer 6

Luis Cazares (11/20/2014)
Have you noticed the vsb instead of vbs?
Does the account used by xp_cmdshell has permissions to execute the scripts?

That is my fault. I meant to type vbs on these forums but got excited and fat fingered my responses.

The error I am currently getting is:

Could not find server 'master' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers. [SQLSTATE 42000] (Error 7202). The step failed.

Which I find odd (at least in my mind) why I would need a linked server because I am trying to run a .vbs on another server which doesn't have SQL installed on it.

I am running my SQL job for the .vbs step as Transact-SQL Script (T-SQL).

EXEC master...xp_cmdshell '\\<Servername>\c$\Support\Application\Backup\cscript.exe PauseDmWebService.vbs'

Luis Cazares SSC Guru Points: 183706 More actions · Answer 7

You have 3 dots instead of 2.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Luis Cazares SSC Guru Points: 183706 More actions · Answer 8

I was on my mobile, but now that I'm back on the desktop, I can elaborate a little more.

SQL Server is identifying master as a linked server instead of a database because of the number of dots and it would use the default db and schema. To avoid this problems, you should always specify the schema.

EXEC master.sys.xp_cmdshell '\\<Servername>\c$\Support\Application\Backup\cscript.exe PauseDmWebService.vbs'

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

spaghettidba SSC Guru Points: 105732 More actions · Answer 9

Vendage1 (11/20/2014)
Luis Cazares (11/20/2014)
Simple question.
Why do you want to stop app services to backup the DB? Backups can be done online.
I don't want to. I would prefer not having to go down this path, but the application vendor has a requirement that states the application services have to be off_line before the backup is done. Personally believe this is a lesson in futility, but management wants it done so I am doing what I'm told.
Thanks for the question!

Educate them instead of doing what you're told.

You won't regret it.

-- Gianluca Sartori