August 20, 2013 at 1:21 am
hello
i try within an exec-commands to recall a previously filled variable.
usually i can merge them with '+' (concat), within the exec-commands this doesn't work?
any idea?
thx and greets
DECLARE @BackupDrive NVARCHAR(5)
SET @BackupDrive = (select SUBSTRING(physical_name,1,3) from sys.database_files where state_desc = 'online' and type = 0)
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'DBA/Backup - All 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'TSQL',
@command=N'EXECUTE dbo.DatabaseBackup
@Databases = ''ALL_DATABASES,-%mdw%'',
@Directory = ' + @BackupDrive + 'Backup\Backups'',
@BackupType = ''FULL'',
...
August 20, 2013 at 1:45 am
Well, you certainly need to lose the first and last quotation marks in the @Directory line. Even then, you might still not be allowed to concatenate in place, so you could do something like this:
SELECT
@BackupDrive = SUBSTRING(physical_name,1,3) + 'Backup\Backups'
FROM
sys.database_files
WHERE
state_desc = 'ONLINE'
AND
type = 0
By the way, it's not a good idea to back up to the same drive your database files are on. Performance will suffer, availability will suffer if your backup uses all the space on the disk, and recoverability will suffer if you lose the disk for any reason.
John
Edit: I see what you're doing now - @Backup is a parameter of the proc you're calling in the @command part. This shows the importance of formatting you code for readability - if you indent that part then it's more obvious what you're doing. And using the code on the left hand side of your screen will format code in a similar way to how it appears in SSMS.
I think you still need to build the @command string in advance rather than attempting to concatenate in place. And, of course, what I said about using the same drive still applies.
August 20, 2013 at 2:36 am
first, thank you very much for the quick response.
This is only the drive-name in a san-storage, among which are several mount points. so, I would like to save somewhere else, but in a san-storage this is obosolet
to mask the code I missed, I'll try again and yes, that is correct, I want to appeal to a variable within the @command.
if you tell me that this is not possible, then I'll have to rebuild it...until now it has not been able to address the variable.
DECLARE @BackupDrive NVARCHAR(5)
SET @BackupDrive = (select SUBSTRING(physical_name,1,3) from sys.database_files where state_desc = 'online' and type = 0)
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'DBA/Backup - All 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'TSQL',
@command=N'EXECUTE dbo.DatabaseBackup
@Databases = ''ALL_DATABASES,-%mdw%'',
@Directory = ' + @BackupDrive + 'Backup\Backups'',
@BackupType = ''FULL'',
August 20, 2013 at 3:52 am
I can't find it documented anywhere, and I don't know whether there are any exceptions, but it looks like you need to build your parameter value in advance. Probably safer to do it that way than risk getting an error message when you're not expecting it.
John
August 20, 2013 at 11:39 pm
thx John,
tinkering with something laborious I could finally pass the required string...the ' can bring a lot of confusion
DECLARE @BackupDrive NVARCHAR(100)
DECLARE @MergeComm NVARCHAR(500)
SET @BackupDrive = (select +'''' + SUBSTRING(physical_name,1,3) + 'Backup\Backups''' + ',' from sys.database_files where state_desc = 'online' and type = 0)
PRINT @BackupDrive
SET @MergeComm = N'EXECUTE dbo.DatabaseBackup'
SET @MergeComm= @MergeComm + N' @Databases = ''ALL_DATABASES,-%mdw%'','
SET @MergeComm= @MergeComm + N' @Directory = ' + @BackupDrive
SET @MergeComm= @MergeComm + N' @BackupType = ''FULL'', @LogToTable = ''Y'', @Verify = ''Y'', @Compress = ''Y'', @CheckSum = ''Y'', @CleanupTime = 33'
PRINT @MergeComm
EXECUTE dbo.DatabaseBackup @Databases = 'ALL_DATABASES,-%mdw%', @Directory = 'C:\Backup\Backups', @BackupType = 'FULL', @LogToTable = 'Y', @verify = 'Y', @Compress = 'Y', @CheckSum = 'Y', @CleanupTime = 33
August 21, 2013 at 1:06 am
Beware that if you have more than one data file in your database, your query will fail.
John
Viewing 6 posts - 1 through 6 (of 6 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