SQL Server Express 2008 Maintenance Script

The issue I have with SQL Server Express Edition 2008 is that the SQL Agent is disabled and according to Microsoft, this has been disabled on purpose.   So, how do we schedule jobs to do such tasks as Rebuilding Indexes, Full-text Catalogs etc?   I use the Task Scheduler on Windows 2008 Server that executes a SQL script using sqlcmd -i <database script>

Here is the sample code that I use for Rebuilding indexes and catalogs:

declare @databasename varchar(max)
declare @cat varchar(max)

DECLARE database_cursor CURSOR FOR
select name from master.sys.databases
where database_id > 4 –exclude system databases

OPEN database_cursor

FETCH NEXT FROM database_cursor
INTO @databasename

WHILE @@FETCH_STATUS = 0
BEGIN

BEGIN TRY
–Below line rebuilds indexes
exec (‘USE ‘ + @databasename + ‘ EXEC sp_MSforeachtable @command1 = “ALTER INDEX ALL ON ? REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON);”‘)

exec (‘USE ‘ + @databasename + ‘ declare @cat varchar(max) select @cat = (select top 1 name from ‘ + @databasename + ‘.sys.fulltext_catalogs) if @cat is not null begin exec (”ALTER FULLTEXT CATALOG ” + @cat + ” REBUILD WITH    ACCENT_SENSITIVITY=OFF;”) end’)

END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000),
@ErrorSeverity INT,
@ErrorState INT

SET @ErrorMessage = ‘ databasename: ‘ + @databasename + ‘ ‘ + ERROR_MESSAGE();
SET @ErrorSeverity = ERROR_SEVERITY();
SET @ErrorState = ERROR_STATE();

–print @ErrorMessage
RAISERROR(@ErrorMessage,@ErrorSeverity,@ErrorState);

END CATCH

FETCH NEXT FROM database_cursor
INTO @databasename
END

CLOSE database_cursor
DEALLOCATE database_cursor

Tags: SQL Server, T-SQL