Rebuilding backup script using set based logic...

Question

question

Bill asked Oct 13, '09

Rebuilding backup script using set based logic...

Greetings,

I've got a procedure, as follows, which is used to archive tables when after 13 months. We keep a table for each month of the year, and after 13 months, I no longer require the data to be online. Tables are named LogsYYYY_MM example: Logs2009_10 (for October, 2009).

Since I typically try to use set based logic instead of cursors, can someone please point me in the right direction with this? (Also, the backup fails in this example, indicating that the cursor is already open. Not sure why it's doing that, but the point is to try to do this without cursors, so I'm not worrying about the error at this point ...)

ALTER PROCEDURE archiveLogs AS BEGIN SET NOCOUNT ON;

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

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

-- CURSOR to select all the tables that must be archived (typically will only be one table, unless the proc has stopped running for a while)
DECLARE tablesToArchiveCur CURSOR FOR
	SELECT name FROM sysobjects 
	WHERE xtype = 'u' AND LEN(name) = 11 AND LEFT(name,4) = 'Logs' AND SUBSTRING(name,9,1) = '_' AND name < @lastMonthToKeep
	ORDER BY name

OPEN tablesToArchiveCur
FETCH NEXT FROM tablesToArchiveCur INTO @archiveTable

WHILE (@@FETCH_STATUS = 0)
BEGIN
	-- copy data from Logs.dbo.archiveTable into archiveLogs.dbo.archiveTable

	SET @sql = 'SELECT * INTO archiveLogs.dbo.' + @archiveTable + ' FROM ' + @archiveTable
	EXEC(@sql)

	SET @backupFilename = 'D:\DB Backups\LIVE_BACKEND_archiveLogs_' + @archiveTable + '.bak'
	BACKUP DATABASE archiveLogs TO DISK = @backupFilename WITH NOFORMAT, INIT, SKIP, NOREWIND, NOUNLOAD, STATS = 10


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

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

	FETCH NEXT FROM tablesToArchiveCur INTO @archiveTable
END
CLOSE tablesToArchiveCur 
DEALLOCATE tablesToArchiveCur

END

GO

I've removed some logging and try/catch logic in an attempt to keep this small and simple.

Any suggestions?

Thanks!

Bill

sql-server

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2009-10-13T13:26:46Z

jjerome answered Oct 13, '09

I usually don't have any issues with using cursors, but here is an example script using a while statement for looping instead of a cursor. I modified it to somewhat to work with your code, but you'll need to fill in a few things.

I'm assuming your backup commands all work when tested outside of context of the cursor?

Good Luck!

declare @table_list table (
    id int IDENTITY(1,1),
    table_name varchar(300)
)


insert into @table_list
SELECT [name] 
FROM sysobjects 
WHERE xtype = 'u' 
    AND LEN(name) = 11 
    AND LEFT(name,4) = 'Logs' 
    AND SUBSTRING(name,9,1) = '_' 
    AND name < @lastMonthToKeep
ORDER BY name
;

declare @curr_id int;
declare @curr_table varchar(300);

while exists (
    select top 1 * from @table_list
)
begin
    select top 1
    @curr_id = id,
    @curr_table = table_name
    from @table_list   
    ;

    -- do your stuff using @curr_table

    delete @table_list
    where id = @curr_id
    ;
end

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2009-10-13T13:47:41Z

Matt Whitfield answered Oct 13, '09

I really dislike cursors - but to be honest here is one example where they're not actually so bad. Cursors kill performance for large set based data operations. But - when you're running something like this, where 99.9% of the execution time is going to be taken issuing the BACKUP command, then there's nothing really to be gained by converting it to a set based operation.

In fact, it will probably just make it a whole load less readable.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 3 · 2009-10-13T13:52:06Z

Phil Factor answered Oct 13, '09

I reckon that the whole thing can be done a lot simpler in SQL Server 2005/2008 (and 2000 if you don't have that many archives). I couldn't do all the testing as I don't have the archives.

CREATE PROCEDURE archiveLogs
AS 
    SET NOCOUNT ON ;

    DECLARE @SQL VARCHAR(MAX)

    SELECT  @SQL=''

    SELECT  @SQL=COALESCE(@SQL, '')+'
SELECT * INTO archiveLogs.dbo.'+name+' FROM '+name
            +'
BACKUP DATABASE archiveLogs TO DISK = ''D:\DB Backups\LIVE_BACKEND_archiveLogs_'
            +name
            +''' WITH NOFORMAT, INIT, SKIP, NOREWIND, NOUNLOAD, STATS = 10
DROP TABLE archiveLogs.dbo.'+name+'
DROP TABLE logs.dbo.'+name+'
'
    FROM    sysobjects
    WHERE   xtype='u'
            AND LEN(name)=11
            AND LEFT(name, 4)='Logs'
            AND SUBSTRING(name, 9, 1)='_'
            AND name<'Logs'+DATENAME(YYYY, DATEADD(MM, -13, GETDATE()))+'_'
            +RIGHT('0'+CONVERT(VARCHAR(2), DATEPART(month,
                                                    DATEADD(MM, -13, GETDATE()))),
                   2)
    ORDER BY name

    IF NOT @SQL='' 
        EXEC (@sql)

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question