When building highly scalable applications, you need a powerful database. My database of choice right now is SQL Server 2005. Since a full SQL Server license costs a few thousand dollars per processor, I use SQL Server Express. Why not...it's free! It's basically the same thing as the full blown SQL Server 2005, except there are some limitations on memory, database capacity and other things I will not hit with the websites I build on the side. It also doesn't allow you Import/Export data which is a vey useful feature. That's OK, we can script our database updates. And finally, it doesn't include scheduled maintenance. This is not good. All websites and databases MUST be backed up daily in case of a disaster.

So how do we implement this?
I did a quick search on Google and found the following webpage:
http://www.sqldbatips.com/showarticle.asp?ID=27

Download SQL script

Basically, it's a SQL script that does the maintenance tasks for you. Simply specify the parameters based on your needs and schedule it to run with Windows Task Scheduler. That's it!

Example execute call
This call executes a Full Database Backup of all user databases to c:\backups.
It also verifies the backups and reports to c:\reports.
It keeps the backups for 2 weeks and reports for 1 week

  exec expressmaint
      @database      = 'ALL_USER', 
      @optype         = 'DB',
      @backupfldr    = 'c:\backups',
      @reportfldr      = 'c:\reports',
      @verify          = 2,
      @dbretainunit  = 'weeks',
      @dbretainval   = 1,
      @rptretainunit  = 'weeks',
      @rptretainval   = 1,
      @report          = 1

Example Scheduled Task
On my server, I have it scheduled with the following parameters. It's set to run everyday at 3am.

Run: "C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE" -S.\SQLExpress -i"D:\DBBackups\UserFullBackup.sql"

Start in: "C:\Program Files\Microsoft SQL Server\90\Tools\Binn"

Additional comments
1. When I ran the script for the first time, I received the following error:
SQL Server blocked access to procedure 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface Area Configuration" in SQL Server Books Online.

To resolve it, run the following SQL commands:
USE master
GO
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

2. I then received error:
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.

To resolve it, run the following SQL commands:
USE master
GO
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'show advanced options', 0
GO

I have tested the main application and it's solid. It runs very fast and all of the new features run great. The scheduling part appears to be running great, but I still want to test it out on a couple more operating systems (it's very complicated code and I want to make sure it runs perfectly). I have finished testing it on Windows XP and Windows 2003, but still want to test it on Windows 2000. I plan to complete this tomorrow and release ALL of version 4.0. But if you want to get a jump start with the new features, the download link is below. When I publish the final v4 tomorrow, it will be an installation package. And if you don't care about the scheduling feature, then the download link below will be perfect for you.

You'll notice the entire application is now branded with my company name, iArchitect. This is a decision I recently made to push the product under my company name with other products I will be releasing soon. One product coming soon, Web Analyzer, will be free for all licensed Sitemap Generator users! I'll let you know when it's available.

After a long, long wait, I finally have version 4.0 ready! A few licensed users have been using it for the past week and have identified only a couple minor glitches. I am confident the code is solid and ready to be released. I am finishing up some installation package items and will be releasing the new version Monday night (tonight). Thank you everyone for the wait. It will be well worth it! In case you're curious, here are the features that have been added: