Windows Internal Database/SQL Express backups and maintenance

Google informs me that my post on Windows Internal Database Maintenance is popular.  It’s not what I’m currently using, however.

I’ve switched over to Ola Hallengren’s scripts.  If you download and run the installer script, it creates a series of agent jobs, which, well.  Neither Windows Internal Database nor SQL Express has SQL Server Agent.  However, the jobs run as cmdexec scripts that look like

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', @Directory = N'f:\sql_backups', @BackupType = 'FULL', @Verify = 'Y', @CleanupTime = 672, @CheckSum = 'Y', @LogToTable = 'Y'" -b

It’s easy to run these through Task Scheduler.  You’ll want to replace “$(ESCAPE_SQUOTE(SRVR))” with your actual server.

(The connection string for the version of Windows Internal Database based on 2008 is \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query.  The connection string for the version of Windows Internal Database based on 2012 is \\.\pipe\MICROSOFT##WID\tsql\query.  SQLExpress on your current host is .\SQLExpress by default.)

This is what a maintenance job for the SBSMonitoring instance looks like in Task Scheduler:

SBSmaint

If you open the bottom three items (the top two are SBS Monitoring specific items), they’re:

sqlcmd -E -S.\SBSMONITORING -d master -Q "EXECUTE [dbo].[IndexOptimize] @Databases = 'USER_DATABASES', @LogToTable = 'Y'" -b

sqlcmd -E -S.\SBSMONITORING -d master -Q "EXECUTE [dbo].[DatabaseIntegrityCheck] @Databases = 'USER_DATABASES', @LogToTable = 'Y'" -b

sqlcmd -E -S.\SBSMONITORING -d master -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = 'SYSTEM_DATABASES', @Directory = N'F:\SQL_backups', @BackupType = 'FULL', @Verify = 'Y', @CleanupTime = 336, @CheckSum = 'Y', @LogToTable = 'Y'" -b

There’s a second job that’s just backing up the user databases that runs more frequently.  (The database is in simple recovery.)

sqlcmd -E -SSBS08\SBSMONITORING -d master -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', @Directory = N'F:\SQL_backups', @BackupType = 'FULL', @Verify = 'Y', @CleanupTime = 336, @CheckSum = 'Y', @LogToTable = 'Y'" -b

You can, of course, add log backups if your database is in full recovery.  Here’s an example for Windows Internal Database (2012 edition):

sqlcmd -E -S\\.\pipe\MICROSOFT##WID\tsql\query -d master -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', @Directory = N'f:\sql_backups', @BackupType = 'LOG', @Verify = 'Y', @CleanupTime = 168, @CheckSum = 'Y', @ChangeBackupType = 'Y', @LogToTable = 'Y'" -b

Leave a Comment

Filed under scripting, sql

Leave a Reply

Your email address will not be published. Required fields are marked *