1

Good day,

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.

5
  • if you take a sql backup, you can use compression, so you don't need to zip it additionally. saves you a lot of time, depending ofc on the size of the backup. Jan 19 '15 at 9:44
  • Can I do a backup of a specific Partion? Jan 19 '15 at 10:02
  • If you want to get a specific partition of a specific table, I'd do a bcp of the data range you want, compress that, and then load it into the 2nd server. Jan 19 '15 at 15:05
  • SSIS is awesome at moving data and quickly. Have you considered building a package that moves only what you need each night from your VM to your dedicated box in a staging table? You could build some dynamic SQL to perform the partitioning and move the data from the staging table to the new partition. Just a thought. Jan 23 '15 at 14:16
  • Thats exactly what I am trying to do. It must dynamically move off that days partition at the end of the day to the other server. I just need the know how on the transporting side of things. How to get that 4GB of data from the VM to my machine safely and accurately every night. Jan 23 '15 at 18:29
1
  1. For very large Databases you can always use the compression method in SQL server 2012. That will help a lot in reducing down the backup size of the database:

For how to use and more on this refer to below link:

http://www.sqlservergeeks.com/sql-server-database-backup-compression-faster-disaster-recovery/

  1. To avail the space, make sure you're backup retention period is appropriate so as to make proper amount of disk space available.

  2. If possible do some TPM like cleaning activity on the disk to get some space.

  3. Also, for data level compression you can just script out the schema and then move data using BCP OUT and BCP IN. You can even zip those files and move them across the destination server.

  4. Last but not least: One more thing to add :

Make sure that Instant File Initialization is enabled on the server. That will dramatically reduce the restore time. Refer to below link for more info on this..

http://sqlblog.com/blogs/tibor_karaszi/archive/2009/03/09/do-you-have-instant-file-initialization.aspx

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.