How to transfer a Database on a weekly basis for analysis purposes

Solution 1:

You could look at log shipping, very similar to your 'manual' approach but uses built-in SQL components to do the work and works with transaction log backups.

A great explanation and comparision to db mirroring can be see at http://blogs.technet.com/josebda/archive/2009/04/02/sql-server-2008-log-shipping.aspx

Solution 2:

As a mere suggestion, you say your db is 36 gigs. Depending on the compression + speed of transfer you might find it easier to dump it on a USB drive and snail mail it. Furthermore, some ftp will never allow files of > 4GB. Incremental pushes might be your way, but again consider the worst case: what happens if your data changes faster than your db can push it to the replicator?

Solution 3:

I have a client who needed this exact thing. They couldn't accept the db being inaccessible when the logs restore so log shipping was out. Replication was not possible because their schema would be too much of a hastle. Mirroring wouldn't work because it's read-only, and they needed a writable database... but those writes did not have to go back to the main server.

I ended up writing a maintenance plan and a few scripts that does a full backup of the db in question (120 GB), then uses xp_cmdshell to copy the file to a network share on the other SQL server, and then lastly executes a job remotely (you can set what server to execute on in the plan step) to do a restore of the database.

You may want to check out Tara Kizer's isp_Restore script which can help you with this. http://weblogs.sqlteam.com/tarad/archive/2005/11/08/8262.aspx I ended up writing my own script because hers didn't do exactly what I needed, but it should at least get you started.