Transfer of large database from 1 server to another

I am in the process of setting up a test environment on a server in a different location to the LIVE server.

I need to make a copy of the SQL Server database and move it across.

I want to know, if I were to Zip the Data and Log files and then split the zip file into smaller files for faster FTP. Would this damage or alter the data alignment in any way?

Before you bring ZIP into the mix, be advised that Sql Server natively supports backing up a database to multiple files typically resulting in smaller files and faster backup time.

Check out Backup to multiple files for faster and smaller SQL Server files by Greg Robidoux.

Summarizing Greg's post:

Problem


Have you ever wished you could get your backups to run faster? Well
there may be a way, by writing to multiple files. Creating SQL Server
backups is pretty simple to do; you can use the SQL Server management
tools or you can use T-SQL commands to issue the backup. But sometimes
with large databases it takes a long time to run and it is difficult
to copy this very large file across the network or even to your backup
tapes.

Solution


Write your database backup to multiple files. In addition to writing
your database backup to one file you have the ability to write to
multiple files at the same time and therefore split up the workload.
The advantage to doing this is that the backup process can run using
multiple threads and therefore finish faster as well as having much
smaller files that can be moved across the network or copied to a CD
or DVD. Another advantage to writing to multiple files is if you have
multiple disk arrays you can write your backup files to different
arrays and therefore get better I/O throughput. One thing to note is
that to maintain the I/O throughput you should keep the writing of
your backup files and the reading of the database file on different
disk arrays.

Backup to multiple files using T-SQL

BACKUP DATABASE [Northwind] TO 
DISK = 'C:\Northwind_file1.bak', 
DISK = 'D:\Northwind_file2.bak', 
DISK = 'E:\Northwind_file3.bak', 
DISK = 'F:\Northwind_file4.bak' 
WITH INIT , NOUNLOAD , NAME = 'Northwind backup', NOSKIP , STATS = 10, NOFORMAT,COMPRESSION

On another note:

You should SERIOUSLY consider taking this backup with COPY_ONLY to make sure you don't mess up your production differential base.

StackExchange Database Administrators Q#185613, answer score: 14