MS SQL

Standard Maintenance Plans

Daily Maintenance Plan

1. History Cleanup Task
   • Delete all historical data (backup/restore, SQL Server Agent and Maintenance plan histories)
   • Older than 2 weeks
2. Reorganise Index Task
   • All user databases
   • Compact large objects
3. Update Statistics Task
   • All user databases
   • Update: All existing statistics
   • Scan type: Full scan
4. Back Up Database Task
   • Backup type: Full
   • Databases: All databases
   • Backup will expire: Leave unticked
   • Create a backup file for every database: Update as appropriate
5. Maintenance Cleanup Task
   • Delete files of the following type: Backup files
   • File extension: bak
   • File age: Select as appropriate

Hourly Transaction Log Backup

1. Back Up Database Task
   • Backup type: Transaction Log
   • Databases: All user databases
2. Maintenance Cleanup Task
   • Delete files of the following type: Backup files
   • File extension: trn
   • File age: Select as appropriate

Commands

Crib sheet of some very basic stuff

SELECT * FROM MASTER..SYSDATABASE					       /* Show databases */
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '%'	       /* Show tables */
SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' /* Show base tables only (not views etc) */