| Executive Summary: Microsoft's internal Database Operations team makes SQL Server automation scripts available to database administrators (DBAs). SQL Server automation scripts make up a suite of SQL Server maintenance jobs. SQL Server maintenance jobs provide DBAs with automated tasks that they can run on their servers on a regular basis. |
Microsoft makes a lot of its internal IT operations open to the public so that people can see how this world-class company runs its IT business. At Tech-Ed 2007, Mark Pohto, who heads Microsoft's internal Database Operations team, showed me some new SQL Server DBA automation scripts his team uses. Together, these scripts constitute a suite of SQL Server Agent maintenance jobs the team uses to perform backups, automate database maintenance, improve performance, mitigate risk, and improve the disaster-recovery process. But the coolest thing about these scripts is that they're free for you to download and use.
Related: Database Automation Software Lets DBAs Skip the Scripts
The scripts automate tasks that almost everyone using SQL Server should regularly perform on their servers. Because these are SQL Server Agent jobs, you can easily install them by running a T-SQL script. Each job runs a related stored procedure that's installed when the job is created.
After you install the package, you'll have the following scripts:
- SQLBackupAll performs a full backup of all databases except those you specifically exclude. You can specify the desired backup location, whether to do a third-party backup or a native backup, whether to exclude system or read-only databases, which backup threads to use, and job priority.
- SQLBackupDiffAll performs a differential backup of all unexcluded databases by using either the built-in backup utility or Quest Software's LiteSpeed for SQL Server. This job is disabled by default because some environments don't use differential backups.
- SQLBackupTranAll backs up transaction logs for all unexcluded databases. The job includes parameters to dynamically define the desired backup location and specify whether to use native backup or a third-party backup tool.
- SQLBackupChecker looks for databases that haven't been backed up within a specified number of hours and alerts you if it finds any.
- SQLDBCCAll performs a battery of database consistency checks on all system and user databases.
- SQLIndexDefragAll defragments indexes on all unexcluded SQL Server databases. You can also use this job to defragment indexes within a specific SQL Server instance, database, or table. This job is disabled by default.
- SQLUpdateStatistics updates the statistics for the tables in all unexcluded databases. You can also use this job to update index statistics within a specific SQL Server instance, database, or table. This job is disabled by default.
- SQLCleanupMsdbBackupHistory cleans up the backup-history information in the msdb database.
- SQLCycleErrorLog periodically cycles the SQL Server error log to keep it at a size that's manageable for viewing through the native tools.
The download for these scripts includes a Word document that explains the parameters and options for each script and provides examples of how to use it. For example, to defragment all the indexes in all databases, you can type the following at the query prompt in SQL Query Analyzer or SQL Server Management Studio:
Parameters let you specify the database, the table, and the percent fill factor; the sample command above uses the default values (null, null, and 10.0, respectively).
Installing the automation scripts is easy. First, make sure that your SQL Server 2005 or SQL Server 2000 server has SQL Server Agent running on it. Then run the command
where servername is the name of your server. To uninstall the jobs, use the command
You can download the automation scripts at http://download.microsoft.com/download/4/0/C/40CBAD9A-D990-450B-8785-F288CEBFB448/AITScripts.zip
SQL SERVER AUTOMATION SCRIPTSBENEFITS: Microsoft's internal Database Operations team's SQL Server automation scripts automate tasks you'd normally have to manually perform on your servers on a regular basis. SYSTEM REQUIREMENTS AND NOTES: SQL Server 2005, SQL Server 2000, SQL Server Agent HOW TO GET IT: You can download SQL Server automation scripts from http://download.microsoft.com/download/4/0/C/40CBAD9A-D990-450B-8785-F288CEBFB448/AITScripts.zip |
