SQL Server 2005 Maintenance Plan – non-system databases

I separate System database backups and User database backups – although the code is essentially the same.

On Some servers extra databases are added to the not in list – update statistics takes a long time to run on a large database so I will run that separately once a week.

These steps are run nightly.

Each Step is step to progress even if it fails – I don’t want to stop all backups just because one db failed something. I have emails sent to me and I also read the job history log each working day so I pick up these failures.

Step 1: Shrink log file if recovery is simple
This only shrinks the log if the database is in simple recovery mode. You shouldn’t really have to do this but I have had some problems which this cleared up for me.

DECLARE @dbname sysname, @cmd varchar(1000);
DECLARE db_recovery_cursor CURSOR FOR
SELECT [name]
from sys.databases
WHERE [name] not in('master','tempdb','model','distribution','msdb')
AND is_read_only = 0
AND recovery_model_desc = 'Simple';
OPEN db_recovery_cursor;
FETCH NEXT FROM db_recovery_cursor INTO @dbname;
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @databaseId as int;
SELECT @databaseId = database_id
FROM sys.databases
WHERE [name] = @dbname;

DECLARE @LogName as varchar(250);
SELECT @LogName = name
FROM sys.master_files
Where type_Desc = 'log'
AND database_id = @databaseId;

DECLARE @sql as Varchar(2000);
SET @sql = 'Use [' + @dbName + '];';
SET @sql = @sql + 'Checkpoint; ';
SET @sql = @sql + 'DBCC ShrinkFile(['+ @LogName + '],30);';

EXEC(@sql);
FETCH NEXT FROM db_recovery_cursor INTO @dbname;
END;
CLOSE db_recovery_cursor;
DEALLOCATE db_recovery_cursor;

Step 2: Update Usage and Checkpoint and turn off autoshrink
Update usage helps fix problems in space reporting on the database.
Some of our databases were created with auto shrink on – a big no no!

USE Master;
DECLARE @dbName varchar(255);
DECLARE @SQL varchar(255);
DECLARE dbCursor CURSOR Local Fast_Forward FOR
select [name]
From master.sys.databases
WHERE Name not in ('master','msdb','tempdb','model','Distribution','Resource')
And is_read_only = 0
And state_desc = 'ONLINE'
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);
SET @sql = 'EXEC sp_dboption ''' + @dbname + ''', ''autoshrink'', ''False'';';
EXEC(@sql);
Use Master;
FETCH NEXT FROM dbCursor INTO @dbName;
END;
CLOSE dbCursor;
DEALLOCATE dbCursor;

Step 3: Integrity Check
Here you may not want to run this against every database every night but if you can you should. I use the Data_purity option as we have a lot of databases that are getting imported from sql 2000

USE Master;
DECLARE @dbName varchar(255);
DECLARE @SQL varchar(255);
DECLARE dbCursor CURSOR Local Fast_Forward FOR
select [name]
From master.sys.databases
WHERE Name not in ('master','msdb','tempdb','model','Distribution','Resource')
And is_read_only = 0
And state_desc = 'ONLINE'
Order by [name];
OPEN dbCursor;
FETCH NEXT FROM dbCursor INTO @dbName;
WHILE @@FETCH_STATUS = 0
BEGIN
Set @sql = 'dbcc CHECKDB ([' + @dbName + ']) WITH NO_INFOMSGS , DATA_PURITY ;';
Exec (@sql);
FETCH NEXT FROM dbCursor INTO @dbName;
END;
CLOSE dbCursor;
DEALLOCATE dbCursor;

Step 4: Update Stats
Sp_updatestats will only update the stats SQL thinks needs updating. Update statistics will up date stats whether they need it or not. Updating stats will cause stored procs to recompile – which is ok if the stats were out of date but not if they werern’t.

USE Master;
DECLARE @dbName varchar(255);
DECLARE dbCursor CURSOR Local Fast_Forward FOR
select [name] From master.sys.databases
WHERE Name not in ('master','msdb','tempdb','model','Distribution','Resource')
AND is_read_only = 0
AND state_desc = 'ONLINE'
Order by [name];
OPEN dbCursor;
FETCH NEXT FROM dbCursor INTO @dbName;
WHILE @@FETCH_STATUS = 0
BEGIN
Exec ('Use [' + @dbName + '] EXEC SP_updatestats ');
FETCH NEXT FROM dbCursor INTO @dbName;
END;
CLOSE dbCursor;
DEALLOCATE dbCursor;

Step 5: Backup and remove old backups
I delete files older than 96 hours – more accurate than x days. On some servers where space is more of a constraint this time is reduced. On out live servers backups are also copied to tape but this process runs outside of sql. Backups will only be removed if there is a corresponding live database. This means if a database is dropped backups will stay on the server until separately dropped – I like having this in case someone wants to restore it. I’ll normally manually delete them after 6 months or so.

If a database is over a certain size I stripe the backup to 4 files.

USE Master;

Declare @delDate as VARCHAR(50);
Set @delDate = CAST(DATEADD(hh, -96, GETDATE()) AS VARCHAR(50));

DECLARE @rootDir as Varchar(255);
SET @rootDir = 'M:\Backups\User\'; /*Enter path to user backups here*/

DECLARE @dbName varchar(255);
DECLARE @sqlCreateDir varchar(255);

Declare @y as char(4);
Set @y = Cast(Year(getDate()) as char(4));
Declare @m as char(2);
Set @m = Right('0' + Cast(Month(getDate()) as varchar(2)),2);
Declare @d as char(2);
Set @d = Right('0' + Cast(Day(getDate()) as varchar(2)),2);
Declare @date as char(10);
Set @date = @y + '_' + @m + '_' + @d;

Declare @h as char(2);
Set @h = Right('0' + Cast(DatePart(hh,getDate()) as varchar(2)),2);
Declare @mi as char(2);
Set @mi = Right('0' + Cast(DatePart(mi,getDate()) as varchar(2)),2);
Declare @time as char(5);
Set @time = @h + '_' + @mi;

DECLARE dbCursor CURSOR Local Fast_Forward FOR
select [name] From master.sys.databases
WHERE state_desc = 'ONLINE'
AND 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 @dirPath as varchar(250);
Set @dirPath = @rootDir + @dbName;

Set @sqlCreateDir = 'master.dbo.xp_create_subdir N''' + @dirPath + '''';
Exec (@sqlCreateDir);

EXECUTE master.dbo.xp_delete_file 0, @dirPath,N'bak',@delDate;

DECLARE @size as numeric(25,0);

SELECT @size = b.backup_size
FROM master..sysdatabases d with (nolock)
left join msdb..backupset b with (nolock)
on d.name = b.database_name
AND b.backup_start_date = (select max(backup_start_date)
from msdb..backupset b2
where b.database_name = b2.database_name
and b2.type = 'D')
WHERE d.name = @dbName;

Declare @BakName as Varchar(250);
If @size < 2147483648 BEGIN Set @BakName = @dbName + '_' + @date + '_' + @time + '.bak'; --Now run a backup command DECLARE @BakCommand as varchar(1024); SET @BakCommand = 'BACKUP Database ' + QuoteName(@dbName); SET @BakCommand = @BakCommand + ' TO DISK=''' + @dirPath + '\' + @BakName + ''''; SET @BakCommand = @BakCommand + ' WITH INIT, Checksum;'; exec (@BakCommand); END ELSE BEGIN Set @BakName = @dbName + '_' + @date + '_' + @time; --Now run a backup command SET @BakCommand = 'BACKUP Database ' + QuoteName(@dbName); SET @BakCommand = @BakCommand + ' TO DISK=''' + @dirPath + '\' + @BakName + 'a.bak'''; SET @BakCommand = @BakCommand + ' ,DISK=''' + @dirPath + '\' + @BakName + 'b.bak'''; SET @BakCommand = @BakCommand + ' WITH INIT, Checksum;'; exec (@BakCommand); END; FETCH NEXT FROM dbCursor INTO @dbName; END; CLOSE dbCursor; DEALLOCATE dbCursor;

References:
Update Usage: http://msdn.microsoft.com/en-us/library/ms188414.aspx
Check Db with Data_purity: http://www.sqlskills.com/BLOGS/PAUL/post/CHECKDB-From-Every-Angle-How-to-tell-if-data-purity-checks-will-be-run.aspx