December 8, 2015 at 4:24 pm
Preface: sorry in advance for the nubness of my question. I am sure this is trivial to 95% of you. I appreciate all the kind responses in advance.
That being said, I just want to create a job that backs up a db from one server to another based on the condition that the 2 db versions match. The version is a user defined table key.
I am thinking this is a job with 3 steps.
1) check versions
2) backup db on server A
3) restore db on server B
The first step is my concern. . . assuming this as my step properties
* On success action: go to next step
* On failure action: quit the job reporting success
Then for the T-SQL for step 1 I would have something like:
DECLARE @Tversion nvarchar(50)
DECLARE @Pversion nvarchar(50)
Select @Tversion = Value From AppDictionary Where [Key] = 'Version'
Select @Pversion = Value From dbo.LS.AppDictionary Where [Key] = 'Version'
-- All the above works correctly, I tested it already. Below is my question
If @Tversion = @Pversion
Begin
Print 'TRUE'
Return 1
End
Else
Begin
Print 'False'
Return 0
End
-- The prints are just stubs I am using in manual query
-- They will be removed for job step when I get it all sorted out
-- The returns are now bombing:
-- A RETURN statement with a return value cannot be used in this context.
So, if the versions match, I want to come out of step one with success and go to the next step. If they don't match (which is perfectly fine), then I don't want to go to the next step and do not do the backup/restore. I just want to quit and done. I am not sure how to use the IF with a return of success/failure or if I am even thinking about this the right way. Anyone want to offer any assistance? Thanks!
December 9, 2015 at 8:24 am
Replace the RETURN and the PRINT with a RAISERROR, and any error severity that is greater than 10. I suggest trying severity level 11 (simply because your failure condition appears to align with 11's description in https://msdn.microsoft.com/en-us/library/ms164086(v=sql.100).aspx), but severity 16 will definitely cause a failure (and is more commonly used).
Instead of a Transact-SQL step, consider a CMDEXEC step that calls sqlcmd.exe, where sqlcmd is passed the T-SQL, perhaps as a stored procedure call. A good example of using a CMDEXEC job step is within and created by Ola's MaintenanceSolution.sql script, at https://ola.hallengren.com/. Note Ola's use of paired quotes for the command line, and use paired apostrophes for the stored procedure's arguments.
Aside 1:
PRINT does not generate "nice" output in a Transact-SQL job step. The job engine adds a (ODBC, IIRC?) informational header to everything that is printed (looks ugly, at best)..
Aside 2:
The ability to RETURN a value is useful when handling stored procedure calls. Outside of RETURN's use in a stored procedure, RETURN cannot return a value. When a value must be returned, a "common" practice is to use 0 for success and non-zero values for failures, "but that's not important right now":-P
December 9, 2015 at 10:59 am
Thanks for the response. As for the prints, as described in my original post, I was just using those in my query to test my code before putting into a job - never intended for those to be used more than temporarily. I think the raiserror was my missing piece of the puzzle. I did some research on it and it looks like THROW is the latest incarnation. I did some testing and I have something working.
DECLARE @Tversion nvarchar(50)
DECLARE @Pversion nvarchar(50)
Select @Tversion = Value From Mydb.dbo.AppDictionary Where [Key] = 'Version'
Select @Pversion = Value From LinkedServer.Mydb.dbo.AppDictionary Where [Key] = 'Version'
-- Get the version from test and prod then compare them below
-- If same, make a backup of prod, if different error out and end job
If @Tversion = @Pversion
Begin
-- backup remote linked server
End
Else
Begin
THROW 51000, 'The db versions are not the same.', 1;
End
So I decided to shorten it to 2 steps. Go ahead and do the backup from the if statement, else kick it out. I can change the versions in both dbs not to match and it will error out appropriately. The 2nd step would just be the restore, which if the versions don't match would be skipped.
My issue now is that I cannot seem to make a backup of my source linked server from my designation box (that the SQL job is running on). I have been researching all morning and it seems to be more complicated than I originally assumed.
The solution I am thinking about progressing towards is to have a separate job on the source box that runs an hour prior that creates a backup nightly onto a mutually accessible file share. If this job determines the versions match, then restore from that unc named location. Any feedback on this approach or a cleaner (less complex as possible) solution would be appreciated.
Thanks again!
December 9, 2015 at 2:16 pm
Here is how Ola Hallengren creates a backup job step:
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'DatabaseBackup - USER_DATABASES - FULL',
@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'CmdExec',
@command=N'sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d _DBA -Q "EXECUTE [dbo].[DatabaseBackup] @databases = ''USER_DATABASES'', @Directory = N''R:\BACKUP'', @BackupType = ''FULL'', @verify = ''Y'', @CleanupTime = NULL, @checksum = ''Y'', @LogToTable = ''Y''" -b',
@output_file_name=N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\LOG\DatabaseBackup_$(ESCAPE_SQUOTE(JOBID))_$(ESCAPE_SQUOTE(STEPID))_$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM)).txt',
@flags=0
See https://ola.hallengren.com/ and https://msdn.microsoft.com/en-us/library/ms162773(v=sql.100).aspx
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply