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

Comments

Leave a Comment

Name:
Email:
URL:
Comment:
Security Code:
Type Security Code: