Designing Custom Maintenance Plans with TSQL

1. Designing Custom Maintenance Plans with TSQL By John Miner

2. Why implement plans? • Prevent loss of data. • Hardware failure • Unintentional software action • Database corruption • Provide good server performance to users. • Ordered Indexes • Up-to-date statistics Surprising how many systems do not have these plans in place correctly.

3. What’s not covered! • Maintenance Plan Wizard – a wizard that steps the user through the process of setting up basic plans with limited options. • Maintenance Plan Designer – a drag-and-drop GUI interface in SSMS that facilitates the design and creation of somewhat more customizable plans. • Powershell – extremely advanced scripting performing tasks against more than one database at a time.

4. Core Maintenance Plan Tasks This presentation will review how to build a custom maintenance plans from the ground up using TSQL commands. • Verify integrity of databases. • Backup databases (full versus differential). • Backup logs • Maintain database indexes. • Maintain index/column statistics. • Remove older data from [msdb]. • Remove older backups from file system.

5. Before Creating Plans • Database Mail has to be setup to send notifications. • At least on Operator needs to be configured to receive the messages. • Active Directory distribution lists should assigned to the Operator.

6. Check Database Integrity • Database corruption might not show up right away and sometimes require full restore from old backup to fix. • Use DBCC CHECKDB (‘db name’) for small databases. • Adding the NOINDEX option for larger databases to reduce time by not checking non-clustered indexes. • Considering executing as off-line operation due to intense resource usage. With modern hard disk systems, this task is largely overlooked.

7. Recovery Models • Simple Recovery – transaction log is automatically truncated during periodic checkpoints and can’t be used for recovery. • Full Recovery – transaction log is not automatically truncated during periodic checkpoints and can be used for recovery. • Bulk-Logged Recovery - Same as Full Recovery except for bulk operations are minimally logged saving significantly on processing time, but preventing point-in-time restore options.

8. Full Backup – TSQL Example BACKUP DATABASE @VAR_NAME TO DISK = @VAR_FILE WITH FORMAT, INIT, NAME = @VAR_DESC, SKIP, NOREWIND, NOUNLOAD, STATS = 10;

9. Full Backup Described • Provides a full copy of the data so that it can be used to restore a database after a given event. • The database name, backup file name, and a description must be supplied. • Using the FORMAT and INIT options, any previous backups are over-written. • The NOFORMAT and NOINIT options allow for appending data. Do not append data since backup files can become quite large.

10. Differential Backup – TSQL Example BACKUP DATABASE @VAR_NAME TO DISK = @VAR_FILE WITH DIFFERENTIAL, FORMAT, INIT, NAME = @VAR_DESC, SKIP, NOREWIND, NOUNLOAD, STATS = 10;

11. Differential Backup Described • Provides a copy of the data changes since the last full backup. • The database name, backup file name, and a description must be supplied. • Using the FORMAT and INIT options, any previous backups are over-written. • The NOFORMAT and NOINIT options allow for appending data. Must use recent full backup, recent differential backup, and all transaction logs to restore to a given point-in-time.

12. Log Backup – TSQL Example BACKUP LOG @VAR_NAME TO DISK = @VAR_FILE WITH NOFORMAT, NOINIT, NAME = @VAR_DESC, SKIP, NOREWIND, NOUNLOAD, STATS = 10;

13. Log Backup Described • Provides a copy of the data changes recorded in the log file. • The log file is truncated after this action. Log files can grow uncontrollably if not done. • Appending log backups in the same file allows for easier restoring. Do this if the database size is small. Chose a periodic daily execution time that balances business needs versus acceptable data loss (15, 30 or 60 minutes).

14. Maintain Indexes • REBUILD versus REORGANIZE options of ALTER INDEX command. • Both commands reduce the amount of fragmentation in Indexes which increases database performance. • REBUILD is a very server intensive command but is quick. Only perform during off-line hours. • REORGANIZE can be performed during on-line hours but is slower. • One must run the UPDATE STATISTICS command on the object after a REORGANIZE operation.

15. Maintain Statistics • The ‘sp_updatestats’ stored procedure can be executed in the current database. • Updates only the statistics that require updating based on the rowmodctr information in the sys.sysindexes catalog view. • Can cause stored code to be recompiled. • Gives Query plan optimizer better information to work with.

16. Remove data from [msdb] • The stored procedures below are used to clear [msdb] data by oldest date pertaining to backup sets, job history, and maintenance plans. ‘msdb.dbo.sp_delete_backuphistory’ ‘msdb.dbo.sp_purge_jobhistory’ ‘msdb.dbo.sp_maintplan_delete_log’ • While this is not necessary, having two year old information is sometimes useless.

17. Remove older backups • The undocumented ‘master.dbo.xp_delete_file’ stored procedure can be used. • Deletes backup (*.bak), transaction (*.trn) and report files (*.txt). • Physically examines the contents of the file before deleting. • Must specify last file date to keep on disk.

18. Tape Rotation (GFS) • The Grand-Father-Son approach uses three levels of tape rotation. • Level 1 - daily backups • Level 2 - weekly backups • Level 3 - monthly backups

19. Tape Rotation (TOH) • The Tower of Hanoi approach uses five levels of tape rotation. • Level A every 2 days; Level B every 4 days • Level C every 8 days; Level D every 16 days • Level E every 32 days

20. Best Practices (Daily) • Full or Differential backups at the start of the day. • Transactional Log backups every hour. • Copy backups from database server to archive server. • Swipe backups from archive server to tape daily. • Rotate tapes daily using either the GFS or Tower of Hanoi schemes. • Store tapes off site.

21. Best Practices (Weekly) • Verify the integrity of each database. • Remove old data from [msdb]. • Remove old backups from server. • Rebuild or Reorganize Indexes * • Update statistics on changed tables * • Perform full backups on large databases. Use daily scheme to secure backup. * - Move to daily if time permits in over night schedule.

22. Best Practices (Monthly) • Send full tape backup of databases to parent site. • Restore backup from tape to test validity.

23. Biography • Has twenty years of data processing and proven project management experience, specializing in the banking, health care, and government areas. • His credentials include a Masters degree in Computer Science from the University of Rhode Island; and Microsoft Certificates (MCDBA & MCSA). • John is currently a Developing DBA at Sensata working with SQL Server 2008 silo of products. • When he is not busy working, he spends time with his wife, daughter and dog enjoying outdoor activities

24. Questions & Answers • References • “Brad's Sure Guide to SQL Server Maintenance Plans”, Brad McGehee, Simple Talk Publishing • "Best Practices for Backup and Restore in SQL Server 2005", Javier Loria, Penton Media Inc. • SQL Server Books Online - http://msdn.microsoft.com/en-us/library/ms130214.aspx • Please ask about the presentation. • If you have any questions, you can contact me at jminer@cox.net