SQL Server Automated Backups

Question

What are the recommendations of software products for creating automated backups of SQL Server 2008 databases?

The backup should happen without taking the database offline/detatching.

Answer 1

I would recommend just creating a maintenance plan in SQL Server to handle the backups, it can be configured to backup to a specified location at specified times, without taking the databases offline, and will handle your incremental backup cleanup.

http://msdn.microsoft.com/en-us/library/ms189715.aspx

Answer 2

If you are using SQL Server Express, you wont find a UI to run periodic backups.
In this case you have to run a batch using Windows Scheduled Tasks or something similar.

Don't forget to use an user with enough privileges to access SQL Server

In the batch file

"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE" -S 
(local)\SQLExpress -i D:\dbbackups\SQLExpressBackups.sql

In SQLExpressBackups.sql

BACKUP DATABASE MyDataBase1 TO  DISK = N'D:\DBbackups\MyDataBase1.bak' 
WITH NOFORMAT, INIT,  NAME = N'MyDataBase1 Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

BACKUP DATABASE MyDataBase2 TO  DISK = N'D:\DBbackups\MyDataBase2.bak' 
WITH NOFORMAT, INIT,  NAME = N'MyDataBase2 Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

Answer 3

Try to use SQL backup and FTP (http://sqlbackupandftp.com)