Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
vijayan.vinu3
Starting Member
19 Posts |
 Posted - 2012-02-15 : 05:31:02
|
HiI made a batch file to interactively backup databases. Following is the code for the batch file : echo offclsset /p DBNAME=Enter database name: set BACKUP=c:\%DBNAME%150212.bakset /p SQLSERVERNAME=Enter_Your_SQL_ServerName_here: echo.sqlcmd -E -S %SQLSERVERNAME% -d master -Q "BACKUP DATABASE [%DBNAME%] TO DISK = N'%BACKUP%' With INIT"echo. The batch file runs fine when i run it from cmdprompt or schedule it as a Windows Task. But i wanted to run it through SSMS using TSQL.I went through a few topics online and got to know that batch files can be executed using the xpcmdshell command.So, I configured XPcmdShell and advanced options and wrote the following TSQL to run the batch file : EXEC xp_CMDShell 'c:\backup_restore_scripts\Backup_script.bat' But, when i execute the above command, inplace of the batch file running, an output is shown in the SSMS output window.  I am not able to understand whats happening. In any case, the batch file has not executed as backup was not created. Please check the code and tell if I am doing it right....if not then please do explain where I'm wrong guys. |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2012-02-15 : 16:35:45
|
| I can't see your image file so I'm shooting in the dark. Having said that...1) Do you have xp_cmdshell enabled via sp_configure?2) xp_cmdshell can only be executed by members of sysadmin (are you?) or via a proxy account (have you set one up?).3) If you are running as sysadmin, the command still operates within the context of the SQL Service Account. Does the service account have access to the OS resources used in the batch file (Drivers, Directories, et al)?=================================================Men shout to avoid listening to one another. -Miguel de Unamuno |
 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
vijayan.vinu3
Starting Member
19 Posts |
Posted - 2012-02-16 : 01:21:07
|
quote:
Originally posted by Bustaz Kool
I can't see your image file so I'm shooting in the dark. Having said that...1) Do you have xp_cmdshell enabled via sp_configure?2) xp_cmdshell can only be executed by members of sysadmin (are you?) or via a proxy account (have you set one up?).3) If you are running as sysadmin, the command still operates within the context of the SQL Service Account. Does the service account have access to the OS resources used in the batch file (Drivers, Directories, et al)?=================================================Men shout to avoid listening to one another. -Miguel de Unamuno
1) Yes, I have xp_cmdshell configured.2) I am using a sysadmin Login3) The batch file uses only one directory where it is creating the backup file. How do I check if the service account has access to the OS resources used in the batch file??? (BTW if the batch file runs from cmdprompt and does create the backup, doesn't that mean that my service account has access to the directory because the batch file by default uses the service account using which I am currently logged into SQL Server)4) How do i add an image that is on my system?...The output only shows the code of the batch file as data in a table. |
|
|
|
vijayan.vinu3
Starting Member
19 Posts |
Posted - 2012-02-16 : 01:23:45
|
| I am not sure why the image is not showing....but it does work when you right click on in and open it in a new tab.....the output is just a table where as the batch file is supposed to create a backup....Please check the image and see if you can help meThanks a lot |
|
|
|
vijayan.vinu3
Starting Member
19 Posts |
Posted - 2012-02-16 : 01:46:06
|
quote:
Originally posted by X002548
But if you are going to do it in SSMS, why not just create a sproc.Why do you NEED to use xp_cmdshell????Close enough to 5:00Aloha!!!"Oh waiter, can I have another Singapore sling... ma-halo"Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxWant to help yourself?http://msdn.microsoft.com/en-us/library/ms130214.aspxhttp://weblogs.sqlteam.com/brettk/http://brettkaiser.blogspot.com/
You are right. Why use xp_cmdshell if I am using SSMS. If u had a look at the code of the batch file, it is clear that it is interactive and asks the user which database to backup. I know that this can be donr from "management tasks" in SSMS which can be used to automate backups.But, I have another script for restoring databases and I think "management tasks" do not give an automated option for restoring the databases. You either have to do it from SSMS manually or using TSQL code. Instead if i can run the batch file it will ask the user which database to backup and in which server the database resides in. After this the script backsup or restores the respective database.I don't think that SQLServer has an interactive mode for run time input. In which case I would have to use the xp_cmdshell in the stored procedure also if i want to run the batch file. |
|
|
|
vijayan.vinu3
Starting Member
19 Posts |
Posted - 2012-02-16 : 03:24:40
|
| HiThanks for the help guys. I got it to work.I created an SSIS package using 'Execute Process Task'. Then i made a job in sqlserver which uses this package. By this I can run the batch file from SQL server by either scheduling it or manually.It still does not answer the question of Why the batch file was not running using xp_cmdshell. But it still is fine for me coz it works from inside SQL SERVER. If anyone has an idea why it doesn't work using xp_cmdshell...then please do post it here...would love to knowThanks |
|
|
|
|
|
|
|
|
Problem Running Batch File using TSQL