Difference between revisions of "MS SQL"
Jump to navigation
Jump to search
(Initial creation - Added maintenance tasks) |
(Added "Commands") |
||
| Line 29: | Line 29: | ||
#* File extension: <code>trn</code> | #* File extension: <code>trn</code> | ||
#* File age: Select as appropriate | #* File age: Select as appropriate | ||
== Commands == | |||
Crib sheet of some very basic stuff | |||
<source lang="sql"> | |||
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) */ | |||
</source> | |||
Revision as of 11:26, 11 March 2011
Standard Maintenance Plans
Daily Maintenance Plan
- History Cleanup Task
- Delete all historical data (backup/restore, SQL Server Agent and Maintenance plan histories)
- Older than 2 weeks
- Reorganise Index Task
- All user databases
- Compact large objects
- Update Statistics Task
- All user databases
- Update: All existing statistics
- Scan type: Full scan
- 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
- Maintenance Cleanup Task
- Delete files of the following type: Backup files
- File extension:
bak - File age: Select as appropriate
Hourly Transaction Log Backup
- Back Up Database Task
- Backup type: Transaction Log
- Databases: All user databases
- 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) */