Sql-server – How to migrate data of to another sql server

data-warehousepartitioningsql serversql-server-2012

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.

Best Answer

  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

Related Question