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

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: 1004414 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)

SpeedySQL Ten Centuries Points: 1019 More actions · Answer 4

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

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

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: 15979 More actions · Answer 10

But you have a declaration of @sqlstatement:

DECLARE @SQLSTATEMENT VARCHAR(2000)

SpeedySQL Ten Centuries Points: 1019 More actions · Answer 11

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

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

Thanks for the replies.