August 15, 2013 at 7:52 am
After my failed attempt last time, I thought of a new way to get my scheduled backups in place.
I have basically created an SP and put it in model, this creates the folder structure for the backups, deletes old backups then takes a full backup of the database. To automate this, I am created another SP, which I will run, feeding in the database name of which I Want it to schedule backups for.
create procedure dbo.CreateBackupAgentJob @DatabasenameInput varchar(100) = null as
declare @JobCode varchar(max)
declare @thisserver nvarchar(100)
select @thisserver = (select @@SERVERNAME)
set @JobCode =
'begin transaction
DECLARE @jobId BINARY(16)
EXEC msdb.dbo.sp_add_job @job_name=N''' + @DatabasenameInput + '- backup Job'',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=2,
@notify_level_page=2,
@delete_level=0,
@category_name=N''[Uncategorized (Local)]'',
@owner_login_name=N''sa'', @job_id = @jobId OUTPUT
select @jobId
GO
EXEC msdb.dbo.sp_add_jobserver @job_name=N''' + @DatabasenameInput + '- backup Job'', @server_name = @thisserver
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N''' + @DatabasenameInput + '- backup Job'', @step_name=N''' + @DatabasenameInput + ' Backup'',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_fail_action=2,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N''TSQL'',
@command=N''exec dbo.backupdatabase'',
@database_name=@databasenameinput,
@flags=0
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N''' + @DatabasenameInput + '- backup Job'',
@enabled=1,
@start_step_id=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=2,
@notify_level_page=2,
@delete_level=0,
@description=N'',
@category_name=N''[Uncategorized (Local)]'',
@owner_login_name=N''sa'',
@notify_email_operator_name=N'',
@notify_netsend_operator_name=N'',
@notify_page_operator_name=N''
GO
USE [msdb]
GO
DECLARE @schedule_id int
EXEC msdb.dbo.sp_add_jobschedule @job_name=N''' + @DatabasenameInput + 'backup Job'', @name=N''10pm''',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20130815,
@active_end_date=99991231,
@active_start_time=220000,
@active_end_time=235959, @schedule_id = @schedule_id OUTPUT
select @schedule_id
GO )
exec @jobcode
I am getting a syntax error on the highlighted row? please help
August 15, 2013 at 7:59 am
I have also just realised I pasted this in the wrong forum (should be in 2008)
August 15, 2013 at 9:54 am
Hi Steve,
I think you've got one too many ' after 10pm, you'll need to remove that and put one in after the last GO.
Not sure where the closing ) following that GO has it's opening?
And @thisserver in the EXEC msdb.dbo.sp_add_jobserver line needs to be concatenated in I think.
You also have a begin transaction without a matching commit/rollback - and note transactions don't work across GO commands. In fact, I'm not sure that it'll run - GO is a SMSS/sqlcmd command that marks separate T-SQL batches, it's not a T-SQL command in itself.
You can probably get away with removing them or replacing with ;
Hope that helps!
August 15, 2013 at 3:54 pm
I don't see any highlighting?
But I do see a syntax error. This is your stored procedure:
create procedure dbo.CreateBackupAgentJob @DatabasenameInput varchar(100) = null as
declare @JobCode varchar(max)
declare @thisserver nvarchar(100)
select @thisserver = (select @@SERVERNAME)
set @JobCode =
'begin transaction
DECLARE @jobId BINARY(16)
EXEC msdb.dbo.sp_add_job @job_name=N''' + @DatabasenameInput + '- backup Job'',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=2,
@notify_level_page=2,
@delete_level=0,
@category_name=N''[Uncategorized (Local)]'',
@owner_login_name=N''sa'', @job_id = @jobId OUTPUT
select @jobId
There is no ' to close the string.
Yes, yes, you intended the GO that follows to be part of the SQL string, but SSMS will intercept the GO right there and pass text before the GO as a batch.
You need something like this:
DECLARE @go varchar(6) = char(13) + char(10) + 'go' + char(13) + char(10)
which you use in this way:
select @jobId' +
@go +
'EXEC msdb.dbo.sp_add_jobserver @job_name=N''' + @DatabasenameInput + '- backup Job'', @server_name = @thisserver' +
@go +
'USE [msdb]
But didn't I tell you that you should use quotestring and quotename to avoid issues with SQL injection. What if there is a database with the name mydb' SHUTDOWN WITH NOWAIT --?
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 16, 2013 at 3:37 am
Cheers guys.
Erland, your right. I should of stayed with your origional suggestion but the quotes were driving me nuts. However, Im going to persist.
How do I get aroudn the quotes using quotestring? Seems like an odd question to ask.
alter procedure [dbo].[CreateBackupProc] @DatabaseNameInput nvarchar(200) = null
as
declare @jobname nvarchar(100)
declare @setDatabasename varchar(100)
declare @deletebackupscmd varchar(200)
declare @backupdatabaseCMD varchar(300)
select @setDatabasename = dbo.quotestring(@DatabaseNameInput)
SELECT @JobName = dbo.quotestring(@DatabaseNameInput) + ' - daily backup'
select @backupdatabaseCMD = 'Exec dbo.BackupDatabase'
DECLARE @jobId BINARY(16)
EXEC msdb.dbo.sp_add_job @job_name= @jobname,
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=2,
@notify_level_page=2,
@delete_level=0,
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
select @jobId
EXEC msdb.dbo.sp_add_jobserver @job_name= @Jobname , @server_name =N'(local)'
EXEC msdb.dbo.sp_add_jobstep @job_name= @jobname, @step_name=N'Backup Database',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_fail_action=2,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=@BackupDatabaseCMD,
@database_name=@setDatabasename,
@flags=0
EXEC msdb.dbo.sp_update_job @job_name= @jobname,
@enabled=1,
@start_step_id=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=2,
@notify_level_page=2,
@delete_level=0,
@description=N'',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa',
@notify_email_operator_name=N'',
@notify_netsend_operator_name=N'',
@notify_page_operator_name=N''
DECLARE @schedule_id int
EXEC msdb.dbo.sp_add_jobschedule @job_name = @Jobname, @name=N'Daily 2am',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20130807,
@active_end_date=99991231,
@active_start_time=20000,
@active_end_time=235959, @schedule_id = @schedule_id OUTPUT
select @schedule_id
Everything is working fine now, the job is being created with the steps i need etc. But obviously the SP is trying to find a database called ''Steve'' as ive used Steve as the database to backup (From my input). But as its in quotestring.
If i just specify the database Steve.. it works so this is the last piece to my prolonged agony!
Really appreciate your help with this
August 16, 2013 at 3:38 am
Erland.. in addition, I the reason i am doing it this way, is due to what you said about the time\date stamp in my origional plan.. it would have been the day of job creation every time the database was backed up, not the day of the backup.. so thanks for pointing that out!
August 16, 2013 at 3:49 pm
But now you have changed the stored procedure, so there is no dynamic SQL, and you should have to use quotestring. What I had in mind was:
EXEC msdb.dbo.sp_add_job @job_name=N''' + @DatabasenameInput + '- backup Job'',
This should have been:
EXEC msdb.dbo.sp_add_job @job_name=N' + dbo.quotestring(@DatabasenameInput + '- backup Job') + ',
Note that it is the full job name that should be put in quotes.
On the other hand, now you have:
SELECT @JobName = dbo.quotestring(@DatabaseNameInput) + ' - daily backup'
EXEC msdb.dbo.sp_add_job @job_name= @jobname,
So if the name of the database is [font="Courier New"]Gurka[/font], the name of the backup job becomes [font="Courier New"]'Gurka'- backup Job[/font]. That is, the quotes become part of the name.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 19, 2013 at 2:57 am
Erland,
Even with the below, I get syntax errors it still doesnt run through. It doesn't like the dash
EXEC msdb.dbo.sp_add_job @job_name=N' + dbo.quotestring(@DatabasenameInput + '- backup Job') + ',
am i approaching this in the wrong way?
August 19, 2013 at 3:05 am
EXEC msdb.dbo.sp_add_job @job_name=N' + dbo.quotestring(' + @DatabasenameInput + '- backup Job') + ',
try this... but what is "quotestring".
Regards
Durai Nagarajan
August 19, 2013 at 3:18 am
durai nagarajan (8/19/2013)
EXEC msdb.dbo.sp_add_job @job_name=N' + dbo.quotestring(' + @DatabasenameInput + '- backup Job') + ',
try this... but what is "quotestring".
It still doesnt like the initial + in the brackets.
August 19, 2013 at 3:46 am
Apply the closing parenthesis in the '
Regards
Durai Nagarajan
August 19, 2013 at 3:47 am
WHat i currently have is
Variables table -
Stored prod 1 - dbo.backup database - creates folder structure, backups up database, deletes old backups
stored proc 2 - input the database name when calling it, and it creates a job to run Stored proc 1 on the sepcified database
its just the job which isnt being created right.
all files attached
August 19, 2013 at 3:52 am
durai nagarajan (8/19/2013)
Apply the closing parenthesis in the '
Already tried mate still didnt like it.
dbo.quote string info - http://www.sommarskog.se/dynamic_sql.html < search that page - ive attached this script
August 19, 2013 at 7:08 am
SQLSteve (8/19/2013)
Erland,Even with the below, I get syntax errors it still doesnt run through. It doesn't like the dash
EXEC msdb.dbo.sp_add_job
@job_name=N' + dbo.quotestring(@DatabasenameInput + '- backup Job') + ',
In difference to many other languages, T-SQL does not accept expressions for actual parameter values; you can only pass variables and constants.
Thus, you would need
SELECT @jobname = N' + dbo.quotestring(@DatabasenameInput + '- backup Job') + '
EXEC msdb.dbo.sp_add_job @job_name = @jobname
However, that still does not make sense, because written in tokens you now have:
SELECT @jobname = Unicode_string - identifier identifier string
That is, from a lexical point of view,
SELECT @jobname = N' + dbo.quotestring(@DatabasenameInput + '- backup Job') + '
is the same as
SELECT @jobname = N'ABC'- backup Job 'XYZ'
You seem to make things unnecessarily difficult for yourself. What you need is probably
SELECT @jobname = @DatabasenameInput + '- backup Job'
All assuming that you stay away from dynamic SQL (which definitely is above your level of confusion, to be honest).
To be honest, I don't know what the rules for job names are, but as long as you stick to static SQL, there is no risk for SQL injection.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 19, 2013 at 8:09 am
Your right i do over complicate things.
I tried using the @job_name = @jobname before but to no avail when I was create an SP inside an SP, but with this approach, It has worked!
Thanks for your help with that! got there eventually
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy