June 6, 2009 at 12:18 pm
Since osql is being depricated, probably the better route is to create a backup SQL script for the targeted databases to backup such as:
BACKUP DATABASE [TestDB] TO DISK = N'E:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Backup\TestDB.bak' WITH NOFORMAT, NOINIT, NAME = N'TestDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10, CONTINUE_AFTER_ERROR
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'TestDB' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'TestDB' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''TestDB'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N'E:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Backup\TestDB.bak' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO
Save it to a directory on the harddrive and then script out a sqlcmd batch file to automate the backup. This can be scheduled via the Windows Scheduler.
The syntax would be similiar to this:
@echo off
sqlcmd -H avt-oak-01 -S avt-oak-01\SQLEXPRESS -E -i "E:\Docs\SQLQueries\Back
upTestDb.sql" -o "C:\BackupTestDb.log"
EXIT
The log output would then have the following output:
14 percent processed.
24 percent processed.
34 percent processed.
44 percent processed.
54 percent processed.
64 percent processed.
74 percent processed.
84 percent processed.
94 percent processed.
Processed 160 pages for database 'TestDB', file 'TestDB' on file 1.
100 percent processed.
Processed 2 pages for database 'TestDB', file 'TestDB_log' on file 1.
BACKUP DATABASE successfully processed 162 pages in 0.505 seconds (2.492 MB/sec).
The backup set on file 1 is valid.
June 7, 2009 at 12:00 pm
Normally, when a person posts a different solution than was originally given, I would think that an explanation of that solution would automatically be posted as well. So, I had to do my own bit of searching to get the meaning of all the code in this solution. You see, not everybody here is good with all types of coding here. Anyway for those who would like an explanation of the sqlcmd script simply open command prompt or powershell and type sqlcmd /? and all the codes and explanations will be given. For your convenience I attached a screenprint of this function.
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
June 8, 2009 at 10:03 am
The reason I don't highly recommend this article is because the author simply ignore the importance of SQL security. As this article let technical even non-technical people run scheduled sql jobs(owner of jobs might be sa or other people) in a batch file(on-demand), it requires the person's window domain id should have SQLagentoperatorRole in MSDB. If domain user account or local NT account has SQLagentoerpatorRole in MSDB, it means that user not only could run the job, but also could view existing jobs, create new job and schedule job (sp_add_job, sp_help_job, etc). There is potential security threat(bring down your SQL)if you allow anyone creates job in your sql server.
just my thought.
June 8, 2009 at 2:59 pm
Until I get up to speed with MS powershell, I do most of my SQL work in a UNIX environment on my XP laptop. I use OSQL to run stored procedures as well as the syscmd statement. I like to know when an SQL job ends so I can look at the results.
I run OSQL in the background, capture the job number and use the "wait" comamnd to
determine when the job is finished. This is how I would run a database backup script, wait for it to end and show the results in an ASCII log
C:/mssql7/Binn/osql ......... -o dblog -Q "exec sp_db_backup" & # run job in background
PID=$! # capture job number
wait $PID # wait for job to end
echo "job database backup has just ended"
cat dblog # show the results after the job ends
March 13, 2013 at 9:26 am
So I just tried doing something similar and it says it runs but nothing happens when the process is completed. At all.
ECHO Executing Job
ECHO.
osql -S "server" -E -Q"exec msdb.dbo.sp_start_job 'job'"
ECHO Job execution executed.
Pause
CLS
EXIT
Thing is, it works fine from my local machine. If i run it from a remote computer it does the fake run.
March 13, 2013 at 7:42 pm
ssj_goemon,
You might need to add an alias through sql server configuration manager.
Could be a networking problem. Open a command prompt and run this:
telnet server.ip.address sqlport
e.g. 192.168.2.235 1433
If the screen goes blank you have network connectivity to the remote server.
Does the account have permissions to connect and/or run jobs on the remote server?
osql -S "remote server" -E -Q"select getdate()"
or:
sqlcmd -S "remote server" -E -Q"select getdate()"
You should get the date.
Create this job on your remote server:
USE [msdb]
GO
/****** Object: Job [getdate] Script Date: 03/13/2013 21:29:04 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 03/13/2013 21:29:04 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'getdate',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [run getdate] Script Date: 03/13/2013 21:29:05 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'run getdate',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'select getdate()',
@database_name=N'master',
@flags=4
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
Then execute this:
osql -S(remote_server_name) -E -Q"exec msdb.dbo.sp_start_job 'getdate'"
or
osql -S(remote.ip.address) -E -Q"exec msdb.dbo.sp_start_job 'getdate'"
sqlcmd -S(remote) -E -Q"exec msdb.dbo.sp_start_job 'getdate'"
or
Then run this query from the remote server using ssms.
select [jh].[message], [jh].[run_date], [jh].[run_time] from sysjobs j join sysjobhistory jh on [j].[job_id] = [jh].[job_id]
where [j].[name] = 'getdate' and step_name = '(Job outcome)'
The message field should give you the security context. e.g. the account that tried to run the job.
smokey
March 18, 2013 at 8:24 am
Hello,
Before I saw your post I created a login for processes and gave it a proxy and was able to run it from one of my servers. The server that I ran it from had bids on it. I then tested it from a server that did not have BIDS on it and it does not run. Not sure where to go from here. I think the reason is my user and the other server do not have osql as they do not have a build of sql server on them. We do not want to have to install it on the user's machine. IS there any other way around this?
March 19, 2013 at 8:35 am
If you want a client computer initiate a server process then they require some interface. OSQL, SQLCMD, and BIDS are all interfaces. You can limit the sql installation features to say management tools - Basic or Complete. You will have to play around with it to figure out which component permits a client to execute the job and any other features you require.
An alternative is to create a scheduling system that removes the need to execute jobs on the fly. I don't recommend allowing users kick off jobs willy nilly. It starts with one job then inevitably escalates.
good luck
Viewing 8 posts - 31 through 37 (of 37 total)
You must be logged in to reply to this topic. Login to reply