A little while ago I was having a problem with MS SQL Server 2005, It was ballooning my log files and not recovering space after a backup and I posted a fix for this here.  The only issue I had left at that point was rolling it into a complete solution that can be run on the server and does not require being run manually.  The following script I put together can run as a Job (or as a SQL task in a Maintenance plan) and will backup all of the databases on your server with the exception of the system db's (master, msdb, temp, model) .  There are also sections in the script to add your own exclusions in case you want to remove long running backups or databases that do not require this sort of treatment, search for DB_2EXCLUDE.

NOTE: The word press plug-in for code formatting changes my greater than and less than sign into > and < respectivly so you will need to change this back if copying the code....until i figure it out anyway.

DECLARE @dbExt VARCHAR(4)
DECLARE @logExt VARCHAR(4)
DECLARE @backupLocation VARCHAR(100)
DECLARE @DATE VARCHAR(50)
DECLARE @tempFile VARCHAR(4000)
DECLARE @tempPath VARCHAR(4000)
DECLARE @LOG nvarchar(MAX)
DECLARE @lasterror int
DECLARE @mailprofile VARCHAR(100)
DECLARE @mail_rec VARCHAR(1000)
-- cursor vars
DECLARE @dbname VARCHAR(100)
DECLARE @dbid int
DECLARE @recModel int
 
SELECT @dbExt = N'.bak'
SELECT @logExt = N'.trn'
SELECT @backuplocation = N'F:\SQL Backup'
SELECT @DATE = CONVERT(VARCHAR(8), GETDATE(), 112)
 
SELECT @mailprofile = 'default'
SELECT @mail_rec = 'myemailAddress@mycompany.com'
SELECT @LOG = 'SQL Server Backup' + CAST(GETDATE() AS nvarchar(100))
 
-- cycle the log
EXEC sp_cycle_errorlog
 
CREATE TABLE #dbInfo
(
database_id int NOT NULL,
dbName nvarchar(100) NOT NULL,
recoveryModel int
)
 
SELECT TOP 1 @dbid = database_id FROM sys.databases WHERE [name]
   NOT LIKE 'DB_2EXCLUDE%' AND database_id > 4  ORDER BY database_id ASC;
SELECT @dbname = [name] FROM sys.databases WHERE database_id = @dbid;
SELECT @recModel = recovery_model FROM sys.databases WHERE database_id = @dbid;
 
-- begin backup process
WHILE @dbid > 0
BEGIN
 
   INSERT INTO #dbInfo
      (database_id, dbName, recoveryModel)
      VALUES
      (@dbid, @dbname, @recModel);
 
   IF @recModel = 1
   BEGIN
 
      SELECT @tempFile = @backuplocation + N'\transactionlog_backup_'
      + @dbname + @DATE + @logExt
      SELECT @LOG = @LOG + '
      Backing up [' + @dbname + '] transaction log file to: '  + @tempFile
      PRINT 'Backing up [' + @dbname + '] transaction log file to: '  + @tempFile
 
      CHECKPOINT
      BACKUP LOG @dbname TO DISK = @tempFile WITH NOFORMAT, NOINIT,
        NAME = N'Transaction Log  Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
      SELECT @lasterror = @@ERROR
      IF @lasterror <> 0
      BEGIN
       -- log the error
       SELECT @LOG = @LOG + '-ERROR: Backing up database log for [' +
          @dbname + '] with error number ' +
          CAST(@lasterror AS nvarchar(25))
       PRINT 'ERROR: Backing up database log for [' + @dbname + '] with error number ' +
          CAST(@lasterror AS nvarchar(25))
      END
   END
 
   SELECT @tempFile = @backuplocation + N'\backup_' + @dbname + @DATE + @dbExt
   SELECT @LOG = @LOG + 'Backing up [' + @dbname + '] database file to: '  + @tempFile
   PRINT 'Backing up [' + @dbname + '] database file to: '  + @tempFile
 
   BACKUP DATABASE @dbName TO  DISK =  @tempFile WITH NOFORMAT, NOINIT,
      NAME = N'Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
 
   SELECT @lasterror = @@ERROR
   IF @lasterror <> 0
   BEGIN
      -- log the error
      SELECT @LOG = @LOG + '-ERROR: Backing up database: ['
         + @dbname + '] with error number ' +
         CAST(@lasterror AS nvarchar(25))
      PRINT 'ERROR: Backing up database: [' + @dbname + '] with error number ' +
         CAST(@lasterror AS nvarchar(25))
   END
 
   DECLARE @SQL nvarchar(4000)
   DECLARE @logName nvarchar(100)
   DECLARE @param nvarchar(4000)
 
   -- get the transaction log name
   SELECT @LOG = @LOG + '
   Getting Log File Name [' + @dbname + ']'
   PRINT 'Getting Log File Name [' + @dbname + ']'
 
   SELECT @SQL = 'SELECT TOP 1 @out = name FROM ' + @dbname
      + '.sys.sysfiles where FileName like ''%.ldf'' '
   SELECT @param = N'@out varchar(100) OUTPUT'
   EXEC sp_executesql @SQL, @param, @OUT = @logName OUTPUT
 
   SELECT @LOG = @LOG + 'Log File Name Is ' + @logName
   PRINT 'Log File Name Is ' + @logName
 
   -- shrink the transaction log file
   SELECT @SQL = 'USE ' + @dbname
   SELECT @SQL = @SQL + ' DBCC SHRINKFILE (@dblogname, 0, TRUNCATEONLY);'
   SELECT @SQL = @SQL + ' SELECT @lasterr = @@ERROR;'
   SELECT @param = '@dblogname nvarchar(500), @lasterr int'
   --SELECT @tempPath = @dbname+'_log'
 
   SELECT @LOG = @LOG + '
   Performing Log File Shrink [' + @dbname + ']'
   PRINT 'Performing Log File Shrink [' + @dbname + ']'
 
   EXEC sp_executesql @SQL, @param, @dblogname = @logName, @lasterr = @lasterror
   -- error obtained in the stored procedure
   IF @lasterror <> 0
   BEGIN
      -- log the error
      SELECT @LOG = @LOG + '-ERROR: Shrinking the database log file: ['
         + @dbname + '] with error number ' +
         CAST(@lasterror AS nvarchar(25))
      PRINT 'ERROR: Shrinking the database log file: ['
         + @dbname + '] with error number ' +
         CAST(@lasterror AS nvarchar(25))
   END
 
   SELECT @LOG = @LOG + 'Log File Shrink Completed'
 
   -- get a new record
   SELECT @dbid = 0;
   SELECT TOP 1 @dbid =  database_id FROM sys.databases
      WHERE [name] NOT LIKE 'DB_2EXCLUDE%' AND database_id > 4 AND
      database_id NOT IN (SELECT DISTINCT x.database_id FROM #dbInfo x);
   SELECT @dbname = [name] FROM sys.databases WHERE database_id = @dbid;
   SELECT @recModel = recovery_model FROM sys.databases WHERE database_id = @dbid;
 
END
 
DROP TABLE #dbInfo
EXEC msdb.dbo.sp_send_dbmail @profile_name='default',
   @recipients=@mail_rec,@Body_Format = 'HTML', @BODY=@LOG;
PRINT @LOG

Technorati Tags: , , , , ,