SQL Server Re-allocate database files with added storage Part 1 of 2

I have an issue that I will explain in more detail in the next question. It has to do with file reallocation of the database files across a recently added 'disk' on the SAN.

I think the primary concern here is to test the setup in a lab environment with easy access to the physical files. A copy of the database and its files should be created on a VM on a local machine. The problem is that there is only a WAN connection via a VPN. This is fine when most operations are server side but not so much for transferring large files and the bandwith it would consume. The main database is just under 300GB. There is one or two more non-trival databases.

An RDP session to the database server is the only real option. Setting up additional transfer protocols (FTP etc) are not really feasible. Is there a way to 1 - divide up the files of the larger databases into chunks to be transferred over a period of days and reassemble once all of the pieces are received. We did this back in the modem days with very large (comparatively) files. The entire database is preferable but I am not sure why. 2 - Script out the schema, which is a big job in of it self and then a subset of the data with a concern being that I get most of the records in the low row count tables and a subset of the high row count tables.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

phil916Author Commented:
Oh if possible maybe a date range cutoff could be used instead of a sampling. The last 3-6 months maybe. This would be preferable to gauge performance since querying cal be run against a realistic set of data. Maybe not...
Vitor MontalvãoMSSQL Senior EngineerCommented:
You have some options. The one I prefer is to backup with compress option. It's possible since SQL Server 2008 and you can have compression rates of 70%-80%.
You can also ZIP datafiles, since they are mostly text will have a very good compression rate also.
Another option is to have data in more than one datafile so you can copy a file at once. And you can ZIP each datafile as well.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
phil916Author Commented:
The only problem I see with this is that I have limited disk space to do this in. The reason for the problem in the first place. At 80% compression 300GB gets me 60GB which is workable. But then lets say I can use Winrar or something to split the files into 2GB checks. I probably wont achieve any more compression but I can get the split. That would give me about one file over 15-20 minutes I would guess. There would be 20 files 4 files per day = 5 days. A bit longer than I was hoping for.

Also I would have to have room both for the compressed backup and the file parts which wold put me over. I have a little less than 100GB
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Don't you have a backup policy set for that database? Where are the backups stored?
phil916Author Commented:
On a different system. These are the parameters I have to work with.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Then why don't you work with those backups already executed?
phil916Author Commented:
It's is not really a question of "work with". There are many different types of backups and backup solutions. This one does not lend itself to my purpose.
Anthony PerkinsCommented:
Here is what you need to do,
1.  Do a Full backup.
2.  Restore the Full Backup with  NORECOVERY.  These twp steps can take as long as needed.
3.  In a maintenance window, stop all activity.
4. Do a Differential backup.
5. Restore the Differential backup with RECOVERY.  Depending on activity, these last two steps could take as little as 15 minutes.

If you cannot afford 15 minutes or you are unable to do backups and restores,  then i suggest you contact a reputable DBA in your area.
phil916Author Commented:
Anthony Perkins - The question is not about backups.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Phil, I don't think you have many options here. You can try to increase the network speed (fiber optic) :)
phil916Author Commented:
OK. I get it. The only so much data will fit through the pipe. I am not sure what else I am looking for.

Anthony PerkinsCommented:
The question is not about backups.
Please tell me where I stated the question had to do with backups?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.