phil916
asked on
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.
Thanks
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.
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
Don't you have a backup policy set for that database? Where are the backups stored?
ASKER
On a different system. These are the parameters I have to work with.
Then why don't you work with those backups already executed?
ASKER
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.
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.
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.
ASKER
Anthony Perkins - The question is not about backups.
Phil, I don't think you have many options here. You can try to increase the network speed (fiber optic) :)
ASKER
OK. I get it. The only so much data will fit through the pipe. I am not sure what else I am looking for.
Thanks
Thanks
The question is not about backups.
Please tell me where I stated the question had to do with backups?
Please tell me where I stated the question had to do with backups?
ASKER