SQL Server 2000 Transaction Log Backups

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;

SQL Server 2000 Maintenance Plan – non-system databases

I don’t like the system generated maintenance plans so use this instead.

I normally go from one step to the next with total disregard as to it’s outcome. This is because I check job histories and logs each morning so if update stats or integrity checks failed I will know about it and fix it then. I also want my backups to run regardless – they stay on disk for 3 days but are also copied to tape so if a problem with integrity creeps in and isn’t noticed for > 3 days (I am on holiday) then if I can’t fix it I can use tape backups to repair almost as easily as disk backups. If this doesn’t suit you you could add an extra step which takes a backup but does not delete old backups – you just need the space for this if you can’t check for a while.

The most unsatisfying thing with this is detecting read only databases – it is easy in 2005 but haven’t found a satisfactory easy method for 2000.

I have a separate job for system dbs where NOT IN is replaced with IN. This runs at a different time of the evening

Step 1: Update Usage and Checkpoint

USE Master;

DECLARE @dbName varchar(255);

DECLARE @SQL varchar(255);

DECLARE dbCursor CURSOR Local Fast_Forward FOR

select [name]

From master..sysdatabases

WHERE [Name] not in ('master','msdb','tempdb','model','Distribution','Resource')

Order by [name];

OPEN dbCursor

FETCH NEXT FROM dbCursor INTO @dbName

WHILE @@FETCH_STATUS = 0

BEGIN

Set @sql = 'dbcc updateusage ([' + @dbName + ']) WITH NO_INFOMSGS;';

Exec (@sql);

Set @sql = 'Use ' + QuoteName(@dbName) + ' Checkpoint;';

Exec (@sql);

Use Master;

FETCH NEXT FROM dbCursor INTO @dbName;

END;

CLOSE dbCursor;

DEALLOCATE dbCursor;

Step 2: Integrity Check

USE Master;

DECLARE @dbName varchar(255);

DECLARE @SQL varchar(255);

DECLARE dbCursor CURSOR Local Fast_Forward FOR

select [name]

From master..sysdatabases

WHERE [Name] not in ('master','msdb','tempdb','model','Distribution','Resource')

Order by [name];

OPEN dbCursor

FETCH NEXT FROM dbCursor INTO @dbName;

WHILE @@FETCH_STATUS = 0

BEGIN

Set @sql = 'dbcc CHECKDB ([' + @dbName + ']) WITH NO_INFOMSGS;';

Exec (@sql);

FETCH NEXT FROM dbCursor INTO @dbName;

END;

CLOSE dbCursor;

DEALLOCATE dbCursor;

Step 3: Update Stats

USE Master;

DECLARE @dbName varchar(255);

DECLARE dbCursor CURSOR Local Fast_Forward FOR

/* 1048 represents REadonly databases but it is no the only code that represents Readonly

you will need to check your dbs*/

select [name]

From master..sysdatabases

WHERE Name not in ('master','msdb','tempdb','model','Distribution','Resource')

AND status <> 1048

Order by [name];

OPEN dbCursor

FETCH NEXT FROM dbCursor INTO @dbName

WHILE @@FETCH_STATUS = 0

BEGIN

Exec ('Use [' + @dbName + '] EXEC sp_MSforeachtable ''UPDATE Statistics ? With Sample 50 Percent''');

FETCH NEXT FROM dbCursor INTO @dbName;

END;

CLOSE dbCursor;

DEALLOCATE dbCursor;

Step 4; Backup databases

USE Master;

DECLARE @rootDir as Varchar(255);

SET @rootDir = 'E:\Backup'; /*Enter path to user backups here*/

DECLARE @dbName varchar(255);

DECLARE dbCursor CURSOR Local Fast_Forward FOR

select [name] From master..sysdatabases

WHERE Name not in ('master','msdb','tempdb','model','Distribution','Resource')

Order by [name];

OPEN dbCursor

FETCH NEXT FROM dbCursor INTO @dbName;

WHILE @@FETCH_STATUS = 0

BEGIN

DECLARE @BakCommand as varchar(1024)

SET @BakCommand = 'master.dbo.xp_sqlmaint N''-S . -D ' + @dbName + ' -BkUpDB "'+ @rootDir + '" -BkUpMedia DISK -CrBkSubDir -DelBkUps 3DAYS -BkExt "BAK"''';

--Print @BakCommand

EXEC (@BakCommand);

FETCH NEXT FROM dbCursor INTO @dbName;

END;

CLOSE dbCursor;

DEALLOCATE dbCursor;