MS SQL

From vwiki
Revision as of 11:26, 11 March 2011 by Sstrutt (talk | contribs) (Added "Commands")
Jump to navigation Jump to search

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) */