Backup script from 2K not creating files on 2K8

Question

Post reply

Backup script from 2K not creating files on 2K8

Jake Shelton

Hall of Fame

Points: 3109
More actions
June 3, 2014 at 5:59 am

#286263

Hi all,

At a dead end trying to troubleshoot this procedure which works fine on SS2K, and also runs without returning any errors 2K8 BUT never actually creates the .bak files - can anyone see anything amiss?

Thanks,

Jake.

/****** Object: StoredProcedure [dbo].[spBackupThenZip] Script Date: 06/03/2014 10:06:13 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[spBackupAndZip]

@DBNAME VARCHAR(256),

@BAKPATH VARCHAR(1000),

@ZIPPATH VARCHAR(1000),

@TYPE VARCHAR(1) -- F (FULL BACKUP) T (TRANSACTION LOG BACKUP)

AS

declare @NextBackupEventId int

select @NextBackupEventId=tblBackupConfig.NextBackupEventId from tblBackupConfig

declare @BackupDatabases int

select @BackupDatabases=tblBackupConfig.BackupDatabases from tblBackupConfig

declare @BackupCompression int

select @BackupCompression=tblBackupConfig.CompressBackups from tblBackupConfig

INSERT INTO

--Select * From

[DBAdmin].[dbo].[tblBackupEventLog]

Order by BackupEventDateTime desc

(

BackupEventId,

DatabaseName,

BackupEventDateTime,

BackupEventDescription,

BackupDatabases,

BackupCompression

)

VALUES

(

@NextBackupEventId,

@DBNAME,

getdate(),

'spBackupAndZip started.',

@BackupDatabases,

@BackupCompression

)

if @BackupDatabases=0

begin

INSERT INTO [DBAdmin].[dbo].[tblBackupEventLog]

(

BackupEventId,

DatabaseName,

BackupEventDateTime,

BackupEventDescription,

BackupDatabases,

BackupCompression

)

VALUES

(

@NextBackupEventId,

@DBNAME,

getdate(),

'No backup due to BackupConfig.BackupDatabases=0.',

@BackupDatabases,

@BackupCompression

)

goto error

end

if @BAKPATH=''

begin

set @BAKPATH='E:\SQLBACKUPS\'+@DBNAME+'\'

end

if @ZIPPATH=''

begin

set @ZIPPATH='E:\SQLBACKUPS\'+@DBNAME+'\'

end

DECLARE @SQLSTATEMENT VARCHAR(2000)

SET @SQLSTATEMENT =''

DECLARE @BTYPE VARCHAR(25)

DECLARE @BTYPEEXT VARCHAR(4)

DECLARE @TIMESTAM VARCHAR(20)

SET @TIMESTAM=REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(25),GETDATE(),120),'-','_'),':','_'),' ','_')

IF @TYPE ='F'

BEGIN

SET @BTYPE =' DATABASE '

SET @BTYPEEXT='.BAK'

END

IF @TYPE ='T'

BEGIN

SET @BTYPE =' LOG '

SET @BTYPEEXT='.TRN'

END

IF @TYPE NOT IN ('T','F')

BEGIN

GOTO ERROR

END

SET @SQLSTATEMENT = 'BACKUP '+@BTYPE+ @DBNAME+' TO DISK ='''+@BAKPATH+@@SERVERNAME+'_'+@DBNAME+'_'+@TIMESTAM+'_AUTO'+@BTYPEEXT+''''

PRINT 'SQL STATEMENT'

PRINT '-------------'

PRINT @SQLSTATEMENT

PRINT 'MESSAGE'

PRINT '-------'

INSERT INTO [DBAdmin].[dbo].[tblBackupEventLog]

(

BackupEventId,

DatabaseName,

BackupEventDateTime,

BackupEventDescription,

BackupDatabases,

BackupCompression

)

VALUES

(

@NextBackupEventId,

@DBNAME,

getdate(),

'Backup started.',

@BackupDatabases,

@BackupCompression

)

EXEC (@SQLSTATEMENT)

INSERT INTO [DBAdmin].[dbo].[tblBackupEventLog]

(

BackupEventId,

DatabaseName,

BackupEventDateTime,

BackupEventDescription,

BackupDatabases,

BackupCompression

)

VALUES

(

@NextBackupEventId,

@DBNAME,

getdate(),

'Backup finished.',

@BackupDatabases,

@BackupCompression

)

if @BackupCompression=1

begin

SET @SQLSTATEMENT = 'C:\Progra~1\BatchF~1\SQLBac~1\ZIP.BAT "'+@ZIPPATH+@@SERVERNAME+'_'+@DBNAME+'_'+@TIMESTAM+'_AUTO'+@BTYPEEXT+'.ZIP" "'+@BAKPATH+@@SERVERNAME+'_'+@DBNAME+'_'+@TIMESTAM+'_AUTO'+@BTYPEEXT+'"'

PRINT 'SQL STATEMENT'

PRINT '-------------'

PRINT 'MESSAGE'

PRINT '-------'

INSERT INTO [DBAdmin].[dbo].[tblBackupEventLog]

(

BackupEventId,

DatabaseName,

BackupEventDateTime,

BackupEventDescription,

BackupDatabases,

BackupCompression

)

VALUES

(

@NextBackupEventId,

@DBNAME,

getdate(),

'Compression started.',

@BackupDatabases,

@BackupCompression

)

EXEC MASTER..XP_CMDSHELL @SQLSTATEMENT

set @SQLSTATEMENT='DEL '+@BAKPATH++@@SERVERNAME+'_'+@DBNAME+'_'+@TIMESTAM+'_AUTO'+@BTYPEEXT

PRINT @SQLSTATEMENT

EXEC MASTER..XP_CMDSHELL @SQLSTATEMENT

INSERT INTO [DBAdmin].[dbo].[tblBackupEventLog]

(

BackupEventId,

DatabaseName,

BackupEventDateTime,

BackupEventDescription,

BackupDatabases,

BackupCompression

)

VALUES

(

@NextBackupEventId,

@DBNAME,

getdate(),

'Compression finished.',

@BackupDatabases,

@BackupCompression

)

end

else

begin

INSERT INTO [DBAdmin].[dbo].[tblBackupEventLog]

(

BackupEventId,

DatabaseName,

BackupEventDateTime,

BackupEventDescription,

BackupDatabases,

BackupCompression

)

VALUES

(

@NextBackupEventId,

@DBNAME,

getdate(),

'No compression due to BackupConfig.CompressBackups=0.',

@BackupDatabases,

@BackupCompression

)

end

GOTO FINAL

ERROR:

PRINT '"TYPE" SHOULD EITHER BE "F" FOR FULL BACKUP OR "T" FOR TRANSACTIONAL LOG BACKUP'

GOTO FINAL

FINAL:

INSERT INTO [DBAdmin].[dbo].[tblBackupEventLog]

(

BackupEventId,

DatabaseName,

BackupEventDateTime,

BackupEventDescription,

BackupDatabases,

BackupCompression

)

VALUES

(

@NextBackupEventId,

@DBNAME,

getdate(),

'spBackupAndZip finished.',

@BackupDatabases,

@BackupCompression

)

GO

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

SpeedySQL Ten Centuries Points: 1019 More actions · Answer 1

SpeedySQL

Ten Centuries

Points: 1019

June 3, 2014 at 6:49 am

#1718507

The parameter @BAKPATH will be NULL if unassigned, not a blank space. Change the following line:

if @BAKPATH=''

to

if @BAKPATH IS NULL

The same will need to be done for @ZIPPATH.

SpeedySQL

Jake Shelton Hall of Fame Points: 3109 More actions · Answer 2

Jake Shelton

Hall of Fame

Points: 3109

June 3, 2014 at 7:30 am

#1718533

Thanks, but it didn't work.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

Does the following path and batch file actually exist on the new machine?

'C:\Progra~1\BatchF~1\SQLBac~1\ZIP.BAT

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

SpeedySQL Ten Centuries Points: 1019 More actions · Answer 4

SpeedySQL

Ten Centuries

Points: 1019

June 3, 2014 at 7:53 am

#1718542

What is the value of @SQLSTATEMENT before it is executed?

SpeedySQL

Jake Shelton Hall of Fame Points: 3109 More actions · Answer 5

Jeff Moden (6/3/2014)

Does the following path and batch file actually exist on the new machine?

'C:\Progra~1\BatchF~1\SQLBac~1\ZIP.BAT

I had checked, and indeed it did/does. The only difference is that the batch file contains a filepath pointing to an executable called 7za.exe, now that path does NOT exist on the new server, however that would affect only the compression, not the backup process.

Jake Shelton Hall of Fame Points: 3109 More actions · Answer 6

Jake Shelton

Hall of Fame

Points: 3109

June 3, 2014 at 8:33 am

#1718560

SpeedySQL (6/3/2014)

What is the value of @SQLSTATEMENT before it is executed?

How would I find that out?

SpeedySQL Ten Centuries Points: 1019 More actions · Answer 7

You have a PRINT statement after the SET statement:

SET @SQLSTATEMENT = 'BACKUP '+@BTYPE+ @DBNAME+' TO DISK ='''+@BAKPATH+@@SERVERNAME+'_'+@DBNAME+'_'+@TIMESTAM+'_AUTO'+@BTYPEEXT+''''

PRINT 'SQL STATEMENT'

PRINT '-------------'

PRINT @SQLSTATEMENT

If you aren't seeing this statement, then one of the parameters is most likely NULL and you are executing a NULL statement. If you do have some output then please post it.

SpeedySQL

Jake Shelton Hall of Fame Points: 3109 More actions · Answer 8

Jake Shelton

Hall of Fame

Points: 3109

June 3, 2014 at 8:46 am

#1718567

I had noticed and tried the 'Print' statement but it comes back with 'Must declare @SQLStatement'

SpeedySQL Ten Centuries Points: 1019 More actions · Answer 9

You can't just run the print statement as it's referencing a variable. It's the variable @SQLStatement that the error says you must declare. How are you executing this stored procedure? In SSMS? Can you provide the command that you are executing?

How did you make the change that I suggested? Can you post the code of the updated stored procedure?

SpeedySQL

SQL Guy 1 SSCoach Points: 15786 More actions · Answer 10

SQL Guy 1

SSCoach

Points: 15786

June 3, 2014 at 9:14 am

#1718577

But you have a declaration of @sqlstatement:

DECLARE @SQLSTATEMENT VARCHAR(2000)

SpeedySQL Ten Centuries Points: 1019 More actions · Answer 11

SpeedySQL

Ten Centuries

Points: 1019

June 3, 2014 at 9:34 am

#1718582

Yes, but you need to run that too, not just the print. If you execute the proc in SSMS then you will see the output of the PRINT statement in the results pane.

SpeedySQL

Jake Shelton Hall of Fame Points: 3109 More actions · Answer 12

Jake Shelton

Hall of Fame

Points: 3109

June 4, 2014 at 3:30 am

#1718763

I concluded that the SQL Servername is called differently between 2K and 2K8, which fixed the issue.

Thanks for the replies.