Well I ran into this problem a few minutes ago and created a pretty clever solution that I would like to share.
SQL Express doesn’t come with SQL Agent so you can’t back it up with the built in function so you will have to create your own. This is how I did it
It uses the built in undocumented SQL ms_spforeachdb stored procedure which loops through every database that you have on your server.
**Tip: Look up ms_spforeachdb and ms_spforeachtable on the web to find out how to use these stored procedures, they have been a life saver for me.
**I take no responsibility for any liability , damage or problems you run into, this is for eduction only. Not recommended for production etc. – use at your own risk
1. Run this to create a stored procedure that takes a database name as a parameter (replace c:\backup with a folder you created for the backups
/****** Object: StoredProcedure [dbo].[sp_backupdatabase] Script Date: 04/08/2011 09:27:19 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE PROCEDURE [dbo].[sp_backupdatabase]
SET NOCOUNT ON;
DECLARE @sqlCommand NVARCHAR(1000)
DECLARE @dateTime NVARCHAR(20)
SELECT @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),111),'/','') +
SET @sqlCommand =
'backup database ' + @databasename + ' to disk = ' + '''' + 'c:\backup\' + @databasename + '_Full_' + @dateTime + '.BAK' + ''''
EXECUTE sp_executesql @sqlCommand
2. Now create a new script and add this
sp_msforeachdb '[dbo].[sp_backupdatabase] ?'
Then save it to c:\backup as script to backup.sql or whatever you want.
3. Schedule a task (daily, weekly or whenever you want a backup ) in windows scheduler to run that script you just created, it will loop through each database and call the stored procedure to create a backup.
4. Now heres the tricky part … my databases were relatively big so I didn’t want this thing to eat up all my harddrive space. So a cleanup job is needed. This batch will delete everything in a folder that is older than 30 days (change 30 in the batch to match whenever you want to delete.)
forfiles /p c:\backup /s /m *.* /d -10 /c "cmd /c del @file : date >= 30 days"
Schedule that to run every day as well and you have essentially a floating 30 day backup!
I would suggest Raiding the harddrive as well just for additional redundancy. Along with this, turn on shadow services for the whole harddrive. It won’t matter because these backup files won’t change once they are saved, but your inetpub and any other web application folder should have this on so you can restore to a previous version.
As always, use at your own risk. I take no responsiblity for any liability, problems , damage or losses you or anyone else incurs .
Of course you can always just buy the full edition of SQL from the link below and do this all through the agent!