January 11, 2013 at 4:45 am
Hi,
Just created a little usp that will be used to databases are backed up using the correct naming
convention and to the correct locations.
When I step through (debug) the procedure using VS2010 it all works fine and the database is
backed up to the correct location, however when I execute this new procedure nothing happens, no errors
and no backup.
I've tried logging into SSMS using my windows and the sa credentials - same result.
ALTER PROCEDURE [Admin].[usp_TimeStampedBackups]
(
@CustomerNVARCHAR(100)
,@BackupLocationNVARCHAR(500) = 'TRANSFER'
)
AS
BEGIN
SET NOCOUNT ON
SET @Customer= UPPER(RTRIM(LTRIM(@Customer)))
SET @BackupLocation = UPPER(RTRIM(LTRIM(@BackupLocation)))
DECLARE @FileNameVARCHAR(500);
DECLARE @sqlVARCHAR(max);
DECLARE @YYYYMMDD CHAR(10), @HourStr CHAR(2),@MinStr CHAR(2);
EXEC usp_DateParts_Get
@YYYYMMDD = @YYYYMMDD OUTPUT,
@HourStr = @HourStr OUTPUT,
@MinStr = @MinStr OUTPUT;
SELECT
@YYYYMMDD AS N'@YYYYMMDD',
@HourStr as N'@HourStr',
@MinStr as N'@MinStr';
IF (@BackupLocation = 'TRANSFER')
BEGIN
SET @FileName = '\\dbsrv\DB_Transfer\'
END
IF (@BackupLocation = 'BACKUP')
BEGIN
SET @FileName = '\\dbsrv\Backup\'
END
SET @FileName = @FileName +
RTRIM(@Customer) + '_' +
@YYYYMMDD + '_' +
@HourStr +
@MinStr + '.bak'
SET @sql =
'
BACKUP DATABASE [<<Customer>>] TO DISK = ''<<FileName>>''
WITH NOFORMAT, NOINIT, NAME = N''<<Customer>> - Database Backup'',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
'
SET @sql = REPLACE(@Sql, '<<Customer>>', @Customer)
SET @sql = REPLACE(@Sql, '<<FileName>>', @FileName)
EXECUTE (@Sql);
END
January 11, 2013 at 5:37 am
I did, also copied the full command from the debugger to see if it worked, it did.
January 11, 2013 at 5:50 am
Do you end up with the same command if you run it in SSMS instead of the debugger?
One thing that doesn't look right... you have declared @YYYYMMDD as CHAR(10), but YYYYMMDD is only 8 characters, so you will have trailing spaces in the middle of the concatenated file name.
January 11, 2013 at 5:56 am
Hi,
Yes the command works fine when copied out of the text visuaizer in VS2010 debugger into SSMS . The usp_DateParts_Get puts hyphens in the date, that's why it's 10 chars.
The procedure renders the backup thus:
BACKUP DATABASE catering TO DISK = '\\dbsrv\DB_Transfer\FRED_2013-01-11_1115.bak '
WITH NOFORMAT, NOINIT, NAME = N'FRED - Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
January 11, 2013 at 6:41 am
thedavegray (1/11/2013)
Yes the command works fine when copied out of the text visuaizer in VS2010 debugger into SSMS
OK, but when running the procedure in SSMS, does it generate the same command?
January 11, 2013 at 6:59 am
Isn't creation date and time information of the bak-file enough ?
To assemble a recovery requence, IMHO it is best to just pull all the needed data out of the msdb data of the instance that hosts the database.
You could also opt to use SQLAgent to schedule your backup jobs and have the first step in the job alter the backup devices so they point to actual files that comply to your naming convention standards.
Any DB-owner group member can launch a backup command of her/his desire. :unsure:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 11, 2013 at 7:01 am
Ah, sorry I see what you mean. I did put a select @sql in but nothing came back to SSMS, thin that might be because the procedure in the middle "usp_DateParts_Get" displays its OUT params.
I will give it another go and see what it returns.
Is it likely to be anything to do with the user? When I run from VS2010 presumably thatt's under my windows credentials but SSMS it depends on the instance login, yes.. no?
January 11, 2013 at 7:09 am
You should still see the output from select @sql even with the display of the out params.
The fact that you can't see anything would lead me to suggest that you are somehow creating an empty string to execute, hence nothing being executed, and no error message.
Select the contents of @sql each time you modify it i.e. after the initial set and after each replace command.
January 11, 2013 at 7:21 am
Can you double check to make sure that the SQL Server service account has access to the network share?
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
January 11, 2013 at 7:27 am
Ian Scarlett (1/11/2013)
You should still see the output from select @sql even with the display of the out params.The fact that you can't see anything would lead me to suggest that you are somehow creating an empty string to execute, hence nothing being executed, and no error message.
Select the contents of @sql each time you modify it i.e. after the initial set and after each replace command.
On the head! Yep, I needed to set the var @sql to nothing first as it was always null, not sure why it worked in VS2010 though.
I'm marling this as the answer & thanks also for the other responses.
January 11, 2013 at 8:23 am
The behaviour you are seeing will be caused by different settings between VS2010 and SSMS for the "CONCAT_NULL_YIELDS_NULL" connection setting.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply