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;
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment