We’re going to set up backup, DBCC CheckDB, and index maintenance for 5 servers in 10 minutes. Note that doing the same thing for 50 servers would take the same amount of time!
Why do backups and CheckDB and index maintenance?
Backups make a copy of the data or log records. This is vital for disaster recovery & other kinds of recovery. I also consider it vital to use native SQL backups, and not rely on VM snapshots or 3rd party, proprietary backup types.
DBCC CheckDB operations look for database corruption…that’s when bits get scrambled & something becomes unreadable. Corruption is usually, but not always, caused when an IO subsystem goes kerflooey. It’s bad, it’s very very bad, you need to check for AND alert for it.
Index Maintenance defragments indexes. This can be SUPER important for performance. An index is the same as an index in a book; it lets you, or your query, find information more quickly. If your book index got rearranged, it would take you far more time to find the information you need you need. Same exact thing with databases!
Problems solved, fast
There are many options for accomplishing backup and maintenance, but I am of course going to point you toward our community tool: Minion Maintenance.
Our maintenance is all native SQL; it covers backups, CheckDB, and index maintenance; and it’s free and very comprehensive.
There are three steps to get this up and running:
- Download and extract Minion Maintenance.
- Modify the backup location.
- Add the CheckDB corruption alert to Minion Maintenance.
- Install Minion Maintenance. One command will install MM on any number of servers!
1. Aquire Maintenance
Minion Maintenance is freely available on MinionWare.net. So, download it and extract it to a server or laptop.
2. Modify the Backup Location
Open [the extraction folder]\Includes\BackupInclude.ps1 in Notepad, and change the values for “$BackupDrive” and “$BackupPath”.
For example, if the company backups are on the NAS, my values might be:
$BackupDrive = '\\YourCo\'; $BackupPath = 'SQLBackups\%SoSL%\';
Note that “%SoSL%” is a special token that will be replaced with the servername. So, Server1 backups will go to \\YourCo\SQLBackups\Server1\.
3. Add the Corruption Alert
It’s a very simple thing to add code to the MM installer:
- First, download the CheckDB corruption alert from the MinionWare Community Zone and save it to [the extraction folder]\CheckDB1.2\CheckDB-Corruption-Alert.sql
- Edit this SQL file: run “Replace All” to replace the string “<alert recipient email>” with the email address that alerts should go to. (E.g., DBAs@yourco.com.)
- Then, edit the “InstallOrder.txt” file in the same folder, so that the last line is:
CheckDB-Corruption-Alert.sql
From now on, the alert SP and job will be installed along with every Minion CheckDB installation!
4. Install
Minion Maintenance comes with a PowerShell installer. Just supply the names of the SQL Server instances you want to put MM on, the database name it should install to, and the “All” keyword (for all three MM modules):
powershell.exe c:\MinionWare\MinionSetupMaster.ps1 "Server1|Server2|Server3|Server4|Server5" master All
Afterword
We’ve spent just 10 minutes on downloads, edits, and that one PowerShell command…and now five servers are covered for backup and maintenance!
We designed these modules to be as plug-and-play as possible, but they are also deeply configurable. If you like, you can customize schedules, settings, and advanced settings (like backup tuning). To learn more, see our Minion Maintenance videos and documentation!