SQL Server 2000 Transaction Log Backups

Posted: September 4, 2009 in Backups, Disaster Recovery, Maintenance, SQL Server 2000, Transaction Logs

This is really one reason why I don’t like the built in maintenance plans. Why can’t they simply ignore the databases which are in simple recovery mode? Why do they instead fill the error logs with message that they have tried to backup the transaction log of the master database??

I wanted to be able to choose which databases to exclude – and then only try to carry out a T log backup on a DB which has Full or Bulk-Logged recovery set.
Remember you can only start T Log backups after you have taken a full backup.

DECLARE @name VARCHAR(50); — database name  
DECLARE @fileName VARCHAR(250); — filename for backup
DECLARE @BakCommand Varchar(550);
DECLARE @rootDir Varchar(20);
DECLARE @fileDate Varchar(50);
/*@rootDir is the root directory where user db backups go*/
SET @rootDir = ‘F:\SQLBackups\user’;  

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)  
   + ‘_’  
   + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),’:’,”) ;

DECLARE db_cursor CURSOR Local Fast_Forward FOR  
SELECT [name]  
FROM master.dbo.sysdatabases  
WHERE [name] NOT IN (‘master’,’model’,’msdb’,’tempdb’)  
   AND DATABASEPROPERTYEX(name, ‘Recovery’) IN (‘FULL’,’BULK_LOGGED’) ;

OPEN db_cursor    
        FETCH NEXT FROM db_cursor INTO @name    
        WHILE @@FETCH_STATUS = 0    
        BEGIN
                /*A sub directory is created for each database if not already there
                Backups are kept for 3 days*/
                SET @BakCommand = ‘use [‘ + @name + ‘]; Checkpoint;’;
                EXEC (@BakCommand);
                use Master;
                SET @BakCommand = ‘master.dbo.xp_sqlmaint N”-S . -D ‘ + @name + ‘ -BkUpLog “‘+ @rootDir + ‘” -BkUpMedia DISK  -CrBkSubDir -DelBkUps 3DAYS -BkExt “TRN””’;
                –Print @BakCommand
                EXEC (@BakCommand);                
       
                FETCH NEXT FROM db_cursor INTO @name;    
        END;
CLOSE db_cursor;
DEALLOCATE db_cursor;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s