Microsoft SQL Server 2008 External Backup

Question

I would like to save my backups from my SQL 2008 server to another server location. We have 2 servers:

• Deployment server
• File Server

The problem is that the deployment server doesn't have much space. And we keep 10 days backups of our databases. Therefore we need to store our backups on an external "file server". The problem is that SQL doesn't permit this. I've tried to run the SQL 2008 service with an account that has admin rights on both pc's (domain account), but this still doesn't work.

Any thoughts on this one. Otherwise we'll have to put an external harddisk on a rack server and that's kinda silly no?

EDIT:

I've found a way to make it work. You have to share the folder on the server. Then grant the Development Server (the PC itself) write permissions. This will make external backups possible with SQL server. Don't know if it's safe though, I find it kinda strange to give a computer rights on a folder.

Answer 1

You can use 3rd party tools like SqlBackupAndFTP

Answer 2

There are several ways of doing this already described, but this one is based on my open source project, SQL Server Compressed Backup. It is a command line tool for backing up SQL Server databases, and it can write to anywhere the NT User running it can write to. Just schedule it in Scheduled Tasks running with a user with appropriate permissions.

An example of backing up to a share on another server would be:

msbp.exe backup "db(database=model)" "gzip" "local(path=\\server\share\path\model.full.bak.gz)"

All the BACKUP command options that make sense for backing up to files are available: log, differential, copy_only, checksum, and more (tape drive options are not available for instance).