x

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

more ▼

asked Oct 13, 2009 at 03:22 PM in Default

Bill gravatar image

Bill
105 6 6 7

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

I think it's probably time to break down your logic into smaller chunks to debug. I would probably start with a simple backup command and work backwards. Just from my outside observation it seems like you are knee-deep in sql code.

Also, is there any reason for all the extra commands in the backup stmt? What about:

Backup database archiveLogs to disk = 'd:\db Backups\...';

Here's a MS article on code 3013

Good Luck!

more ▼

answered Oct 13, 2009 at 04:02 PM

jjerome gravatar image

jjerome
191

Good point. I stripped it down to the basic backup command, which still failed, but at least this time it gave me more details. Namely, it was telling me a could not run the backup because I was inside a transaction.

ARGH! I run my SQL query windows in IMPLICIT_TRANSACTIONS ON mode (I'm an Oracle guy, so I like issuing commit or rollback after I do stuff). The backup was failing because of this. Silly me. However, Silly SQL Server for not simply telling me this in the ERROR_MESSAGE() in the first place!

Thanks everyone!
Oct 13, 2009 at 05:24 PM Bill
(comments are locked)
10|1200 characters needed characters left

In your debugging, is the path and filename valid? Could this file already exist? In which case, a WITH INIT might help.

I'm slightly confused as to what is broken. If you run the proc with just the backup, strip out all the table copy stuff, does that work?

more ▼

answered Oct 13, 2009 at 03:48 PM

Steve Jones - Editor gravatar image

Steve Jones - Editor ♦♦
5.1k 78 79 82

WITH INIT is already specified. Thanks for the tip though.
Oct 13, 2009 at 05:21 PM Bill
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x991
x346
x193

asked: Oct 13, 2009 at 03:22 PM

Seen: 1979 times

Last Updated: Oct 19, 2009 at 01:06 PM