Offline/remote backups

Hi

What is the best way to perform daily offline/remote/off-premise backups of our websistes Sql Server 2005 database?

I've looked into using SSIS but for one reason or another am having serious problems with this. Ideally we'd like to be able connect over the internet and/or lan and pull the entire database down in one go. Our db is ~600mb so isn't major.

We used to use Sql Server 2000's DTS and this worked well, but I'm having a real hard time using SSIS to do the same so was wondering what the other options are

Many thanks

Ben

I'm curious as to why you're going the DTS/SSIS route rather than simply using a standard SQL backup and copying the backup file to a remote location. Understanding that would help us come up with a scenrio that would meet your needs.

If you need a remote system that is a hot or cold standby, you might consider something like log-shipping.

|||

Hi Kevin

The reason we used DTS originally was that it allowed us to backup over the internet. Obviously, upgrading to Sql Server 2005 made SSIS the logical choice.

My customer has two servers running inside the same building and he wants the live database periodically backed-up to the other server so in the event of hard-drive failure, they can still retrieve their data. Also, possibly just once a day, they'd like to download the entire database to another office via the internet.

I suggested to my customer about using the normal backup and possibly FTP'ing the backup file to a remote location. However, log-shipping certianly seems like a good idea. I'd never heard of it until just you mentioned it, so thankyou very much.

Do both instances of Sql Server have to be the same type? Ie, can an Enterprise or Standard version log-ship to a Sql Express instance? Can more than one other instance be used for log-shipping?

Thanks for your help

Ben

|||Log shipping is not supported in SQL Server 2005 Express. If you want, you can emulate log shipping by doing a job that backs up the transaction log on the primary server, FTP the backup to the standby server, and restore the backup on the standby server in read-only mode. This can be done using scripts. A good understanding of how log shipping works will give you an idea on how to create the custom log shipping using a combination of jobs and scripts. The problem with this is that you do not have the luxury of a monitor server nso you need to make sure that you implement your alerts properly. You can check http://www.sql-server-performance.com/sql_server_log_shipping.asp for a reference article on how to do a custom log shipping with scripts, jobs and all.