Maintenance Plans in SQL 2005 Express

I use SQL Express on a few machines at home, and the one thing I'm missing in the Express Edition is the ability to automatically perform database backups/integrity checks/reindexing regularly with a Maintenance Plan. Since SQL Express doesn't have Maintenance Plans, it takes a bit of extra effort to set that up. Here's how I do it:

A Windows Scheduled Task runs daily, kicking off sqlexpressmaintplan.cmd:

sqlcmd -S SQLSERVER\INSTANCE -i d:\scripts\sqlexpressmaintplan.sql
gzip -fNr e:\SQLBackups

(The second line simply compresses (using gzip) the .BAK files to conserve space.)

This executes sqlexpressmaintplan.sql, which has the configuration for my "maintenance plan". At a glance, it looks like a lot, but I've got it broken down into separate sections/tasks, so it's easy to comprehend and maintain.

-- integrity check on system databases; save reports for 7 days

exec expressmaint
    @database      = 'ALL_SYSTEM',
    @optype        = 'CHECKDB',
    @reportfldr    = 'e:\SQLReports',
    @rptretainunit = 'weeks',
    @rptretainval  = 1,
    @report        = 1

-- integrity check on user databases; save reports for 7 days

exec expressmaint
    @database      = 'ALL_USER',
    @optype        = 'CHECKDB',
    @reportfldr    = 'e:\SQLReports',
    @rptretainunit = 'weeks',
    @rptretainval  = 1,
    @report        = 1

-- reindex my database; save reports for 7 days

exec expressmaint
    @database      = 'MyDatabase',
    @optype        = 'REINDEX',
    @reportfldr    = 'e:\SQLReports',
    @rptretainunit = 'weeks',
    @rptretainval  = 1,
    @report        = 1

-- backup system databases; verify; save backups for 2 weeks; save reports for 7 days

exec expressmaint
    @database      = 'ALL_SYSTEM',
    @optype        = 'DB',
    @backupfldr    = 'e:\SQLBackups',
    @reportfldr    = 'e:\SQLReports',
    @verify        = 1,
    @dbretainunit  = 'weeks',
    @dbretainval   = 2,
    @rptretainunit = 'weeks',
    @rptretainval  = 1,
    @report        = 1

-- backup user databases; verify; save backups for 4 weeks; save reports for 7 days

exec expressmaint
    @database      = 'ALL_USER',
    @optype        = 'DB',
    @backupfldr    = 'e:\SQLBackups',
    @reportfldr    = 'e:\SQLReports',
    @verify        = 1,
    @dbretainunit  = 'weeks',
    @dbretainval   = 4,
    @rptretainunit = 'weeks',
    @rptretainval  = 1,
    @report        = 1


The real magic happens in the expressmaint stored procedure, created by SQL Server MVP Jasper Smith. (Also available as a standalone executable.) You can read more about this great tool on sqldbatips.com in two articles covering the stored procedure and the standalone app.

It's been a really long time since I originally set this up, and I'm having to revisit it now (and write about it) because I just rebuilt my primary workstation and server at home. Now that I'm reading back over this article, I can't remember why I chose to use the stored procedure instead of the executable version -- the exe would be a bit easier to drop into place, along with the batch file (the SQL script would no longer be necessary), next time I have to set this up on a fresh SQL Express install. I'm going to try out the executable version this time around to see if it's any easier to set up and maintain (it looks like it will be).

Re-Arming Vista's Trial After Expiration

Windows Vista can be installed without a product key in a 30-day trial mode. This is nice for users who may want to try out Vista, or compare the different flavors (Home Basic, Home Premium, Business, Ultimate, etc). After 30 days, you are required to activate Vista by entering a valid product key, which will then lock it in to a particular edition depending on the key (Vista also supports instant upgrades, so you can change editions later).

As Jeff Atwood points out, you can extend the grace period to 120 days using the re-arm command. It's easy, it's legal, and it's provided by Microsoft. And ideally, you'll extend the grace period BEFORE the 30-day trial is up.

My Vista 30-day trial ran out on my media center PC, and I was given only four options the next time I tried to login: activate now, re-enter my license key, use reduced functionality mode, or logout. I'm not ready to activate, and I don't have a license key. I don't want to logout -- I want to watch TV! This leaves "reduced functionality mode," which is only an IE browser window (you're supposed to use it to buy Vista online). I remembered the re-arm command, but reduced functionality mode doesn't provide a Command Prompt, the Start Menu, Desktop, or anything else -- just a single IE window.

Fortunately, there's an easy workaround (or maybe several. Safe Mode seems like a reasonable option, but I didn't want to reboot). IE's address bar can be used to browse the file system to open web pages stored locally. It can also be used to launch c:\windows\explorer.exe, which starts up the rest of the Windows environment and gets you out of reduced-functionality mode. From there, I opened a command prompt with elevated privileges and ran "slmgr -rearm" to extend the trial period by another 30 days.

Visual Studio 2005 SP1 for Vista

Visual Studio 2005 Service Pack 1 is finally out of beta. Remember to uninstall the beta (listed under "updates" as Hotfix for Visual Studio 2005 [SKU name] - ENU (KB929470)) first.