I have a database running on a virtual machine hosted at our ISP. It is a fairly large database which has about 5GB of data inserted into it every day (network logs).
It is partitioned daily at midnight and has a file group and a file for every day.
We currently only have enough space to keep a few days data on the virtual machine. I have setup a dedicated machine at our offices which is running sql server with plenty of hdd space and will be much better suited for running queries on the large volumes of data.
Now what I want to find out is: how can I efficiently move off one (partition / filegroup) from sql1 machine to sql2 machine?
We don't have great internet here in South Africa, so any sort of compression / safety checks will be needed.
I was thinking I could backup the file to a
.bak and zip it, then download that file to the sql2 server and restore it. However I am looking for a more integrated approach possibly using sql server and want not not code my whole own program to do such a thing.
I know that sql server has a lot of mechanisms built in for data warehousing. I am sure that there is some component in sql which does something like this. After all I am trying to achieve a basic version of data warehousing.
What options do I have / does sql server provide me and where can I go look to how to implement them properly?
I am running Sql Server 2012 Enterprise on both machines.