What backup solutions would you recommend when using SQL Server 2008 Express? I'm pretty new to SQL Server, but as I'm coming from an MySQL background I thought of setting up replication on another computer and just take Xcopy backups of that server.
But unfortunately replication is not available in the Express Edition.
The site is heavily accessed, so there has to be no delays and downtime. I'm also thinking of doing a backup twice a day or something.
What would you recommend? I have multiple computers I can use, but I don't know if that helps me since I'm using the Express version.
SQL Server Express 2008 supports database backups. It's missing SQL Agent, which allows to schedule backups, and the maintenance plan wizard for creating a backup tasks.
You can backup databases in two different ways:
Syntax something like:
BACKUP DATABASE MyDatabase TO DISK='C:\MyDatabase.bak';
If you want to schedule your backup jobs, you have to write a T-SQL script and then use the Windows Task Schedule to call SQLCmd to run the script on what every schedule you're interested in:
I use SQLBackupAndFTP - fantastic and simple product.
I was wrote backup script for use myself, install as metioned in post by splattne:
I use ExpressMaint, and it works great as a scheduled task. Just pass the appropriate parameters for the type of job you are doing.
The source code is also out there. We changed it slightly to add an entry in the application event log on failure.
Based on UndertheFold's post I did some googling and found the details of ExpressMaint. I'd not seen this before, so I am very pleased to have found it.
FYI, the web page is, http://expressmaint.codeplex.com/
I then used one of the examples I found to create this batch file that I have scheduled to run overnight every day.
This takes a backup, keeps each backup (
-BU
) for four days, so you get a history if there is corruption. The logs (-RU
) are kept for one week.I've only been using it for a few weeks, but I have been very happy with it, as it's a hands-off approach. Locally I put the backups on a second disk, I then use JungleDisk to do an offsite backup to the Amazon EC2 cloud storage.
You can use DBSave. It's a great freeware tool to backup and restore MS SQL Server. It's very simple to setup and to use.
I am using a windows scheduler job to backup the SQL Server express database every few hours by using a batch file . Seems to work fine.
I'd check out SQLServerBooster: http://www.sqlserverbooster.com.
It's looks like it has a nice feature set and is free. The support for azure/S3/glacier is nice as well.