There’s no such thing as a free lunch
I recently visited a site where the local IT Team were having to remember to manually backup a SQL Server 2005 Express Edition database each night, as one of the missing features of the Express Edition is the ability to set up a maintenance plan to do this automatically. What follows is a workaround I provided to automate their database backups:
Solution
First off I created a really simple batch file using Notepad:
@echo off
Set YYYY_MM_DD = %DATE:~6,4%_%DATE:~3,2%_%DATE:~0,2%
Set DBName = SDE
set BackupFile = C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackup%DBName%_%YYYY_MM_DD%.bak
osql -S ServerName -E -Q "BACKUP DATABASE %DBName% TO DISK='%BackupFile%'"
I saved the file as C:Program FilesBatch JobsDBBackup.bat
All the batch file is doing is running an SQL command to backup the database to disk appending the current date automatically. All that is then left is to create a Scheduled Task to run the batch file every night.
Clearly there is much more you could do with this and indeed there are a whole raft of applications (many free) that allow you to do this with a GUI etc. but…just in case it helps.
As always any criticism, positive or negative is always welcome.
JOATIT 