SQL Server 2008 Express - "Best" backup solution?
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.
Solution 1:
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:
- Use Microsoft SQL Server Management Studio Express which has the Backup option on the right click menu for each database under "tasks."
- Use T-SQL to manually write your backup script. Read the MSDN documentation for the T-SQL BACKUP command.
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:
sqlcmd -s server_name\sqlexpress -i C:\SqlJobs\backup.sql -o C:\Logs\output.txt
Solution 2:
I use SQLBackupAndFTP - fantastic and simple product.