Backup fails when run from stored procedure

Greetings,

As a follow-up to my last post re: "Rebuilding backup script using set based logic…", my backup fails (both using the original cursor method and the new loop table var method) with a 3013 "BACKUP DATABASE is terminating abnormally." message.

I've hacked away at this for a while, and am stumped as to why it's failing.

Basically this is an archiving stored procedure. The tables are stored monthly, using the naming format: LogsYYYY_MM (example: Logs2009_10 for Oct 2009). After 13 months, the data is no longer required, so this stored proc copies the data to an empty "archiveLogs" database, backs it up, and then drops the table.

The problem is with the backup, as stated above. I'm not sure why it fails. If I run the backup command "manually" it runs just fine and creates the backup file.

Here's the code (including a lot of extra debug code for debugging):

Note: the "top 500" filter on the data is just to speed up my debugging process. Normally that's not there, and it copies 6 or 7 million rows.

CREATE PROCEDURE archiveLogs AS BEGIN SET NOCOUNT ON;

-- This procedure will look for any tables that are named LogsYYYY_MM that are OLDER than 13 months, and it will archive them.
-- Archiving will cause them to move to the archiveLogs DB, backup them up, and remove them.

DECLARE
	@lastMonthToKeep varchar(15),
	@tempMonth varchar(2),
	@tempYear varchar(4),
	@archiveTable varchar(50),
	@logStr varchar(2000),
	@sql varchar(2000),
	@sqlStmt nvarchar(2000),
	@procName varchar(50),
	@backupFilename varchar(200),
	@counter INT,
	@numTables INT

DECLARE @tablesToArchive TABLE ( id INT IDENTITY(1,1), tableName VARCHAR(20))

SET @procName = 'archiveLogs: '

SET @logStr = @procName + 'Starting.'
EXEC debugLogger @logStr

SET @tempMonth = (SELECT datePart(MM,dateAdd(MM,-13,getDate())))
IF (LEN(@tempMonth) < 2)
	SET @tempMonth = '0' + @tempMonth
SET @tempYear = (SELECT datePart(YYYY,dateAdd(MM,-13,getDate())))

-- we will remove all logs tables that are < tempMonth and tempYear
SET @lastMonthToKeep = 'Logs' + @tempYear + '_' + @tempMonth

SET @logStr = @procName + 'Last month of logs to keep: ' + @lastMonthToKeep
EXEC debugLogger @logStr

INSERT INTO @tablesToArchive SELECT name FROM sysobjects WHERE xtype = 'u' AND LEN(name) = 11 AND LEFT(name,4) = 'Logs' AND SUBSTRING(name,9,1) = '_' AND name < @lastMonthToKeep

SET @numTables = (SELECT MAX(id) FROM @tablesToArchive)

SET @logStr = @procName + 'Number of tables to archive: [' + CAST(@numTables AS VARCHAR) + ']'
EXEC debugLogger @logStr

SET @counter = 1
WHILE @counter <= @numTables
BEGIN
	SET @archiveTable = (SELECT tableName FROM @tablesToArchive WHERE id = @counter)

	-- copy data from Logs.dbo.archiveTable into archiveLogs.dbo.archiveTable

	SET @logStr = @procName + 'Archving data from Logs table: [' + @archiveTable + ']...'
	EXEC debugLogger @logStr

	BEGIN TRY
		SET @sql = 'SELECT TOP 500 * INTO archiveLogs.dbo.' + @archiveTable + ' FROM ' + @archiveTable
		EXEC debugLogger @sql
		EXEC(@sql)

		SET @logStr = @procName + 'Rowcount: ' + CAST(@@ROWCOUNT AS VARCHAR) + ' records copied to archive table (archiveLogs.dbo.' + @archiveTable + ').'
		EXEC debugLogger @logStr
	END TRY

	BEGIN CATCH
		SET @logStr = @procName + 'Error during copy.'
		EXEC debugLogger @logStr

		SET @logStr = @procName + 'Error Message: ' + ERROR_MESSAGE()
		EXEC debugLogger @logStr

		RAISERROR(@logStr, 16, 1)
		RETURN
	END CATCH

	-- Backup the archive logs table
	SET @logStr = @procName + 'Backing up archiveLogs.dbo.' + @archiveTable + ' to LIVE_BACKEND_archiveLogs_' + @archiveTable + '.bak'
	EXEC debugLogger @logStr

	SET @backupFilename = 'D:\DB Backups\LIVE_BACKEND_archiveLogs_' + @archiveTable + '.bak'
	SET @logStr = @procName + 'Backup Filename: [' + @backupFilename + ']'
	EXEC debugLogger @logStr

	BEGIN TRY
		BACKUP DATABASE archiveLogs TO DISK = @backupFilename WITH NOFORMAT, INIT, SKIP, NOREWIND, NOUNLOAD, STATS = 10
	END TRY

	BEGIN CATCH
		SET @logStr = @procName + 'Error Message: ' + ERROR_MESSAGE()
		EXEC debugLogger @logStr

		SET @logStr = @procName + 'Error Message: ' + CAST(ERROR_NUMBER() AS VARCHAR)
		EXEC debugLogger @logStr

		SET @logStr = @procName + 'Error during BACKUP.'
		EXEC debugLogger @logStr

		RAISERROR(@logStr, 16, 1)
		RETURN
	END CATCH

	SET @logStr = @procName + 'Backup completed.'
	EXEC debugLogger @logStr


	SET @sql = 'DROP TABLE archiveLogs.dbo.' + @archiveTable
	EXEC debugLogger @sql
	EXEC(@sql)

	SET @logStr = @procName + 'Archive table dropped from archiveLogs database.'
	EXEC debugLogger @logStr


	-- drop existing table
	SET @sql = 'DROP TABLE logs.dbo.' + @archiveTable
	EXEC debugLogger @sql
	EXEC(@sql)

	SET @logStr = @procName + 'Table: logs.dbo.' + @archiveTable + ' dropped.'
	EXEC debugLogger @logStr

	SET @counter = @counter + 1
END

SET @logStr = @procName + 'Completed.'
EXEC debugLogger @logStr

END

GO

Here's the debug log output:

logDateTime             logText
----------------------- ------------------------------------------------------------------------------------------------
2009-10-13 14:17:15.333 archiveLogs: Starting.
2009-10-13 14:17:15.333 archiveLogs: Last month of logs to keep: Logs2008_09
2009-10-13 14:17:15.333 archiveLogs: Number of tables to archive: [7]
2009-10-13 14:17:15.333 archiveLogs: Archving data from Logs table: [Logs2008_02]...
2009-10-13 14:17:15.333 SELECT TOP 500 * INTO archiveLogs.dbo.Logs2008_02 FROM Logs2008_02
2009-10-13 14:17:15.663 archiveLogs: Rowcount: 500 records copied to archive table (archiveLogs.dbo.Logs2008_02).
2009-10-13 14:17:15.663 archiveLogs: Backing up archiveLogs.dbo.Logs2008_02 to LIVE_BACKEND_archiveLogs_Logs2008_02.bak
2009-10-13 14:17:15.663 archiveLogs: Backup Filename: [D:\DB Backups\LIVE_BACKEND_archiveLogs_Logs2008_02.bak]
2009-10-13 14:17:15.663 archiveLogs: Error Message: BACKUP DATABASE is terminating abnormally.
2009-10-13 14:17:15.663 archiveLogs: Error Message: 3013
2009-10-13 14:17:15.663 archiveLogs: Error during BACKUP.

I don't know if there is some reason why it doesn't like running the backup command along with the dynamic SQL, or what the issue might be. I've trying running the backup command "dynamically" (as an EXEC stmt) as well, but that makes no difference (not a surprise).

Thanks!

Bill