September 22, 2015 at 7:52 am
We are running SQL Server 2014 EE SP1. With a database size of 500 GBs broken out into 4 File Groups (A, B, C, and D). File Group A and File Group B have most of the pertinent data with the following sizes (File Group A - 150 GBs and File Group B - 75 GBs). We are considering trying to move data stored in two of the File Groups (A and B) from one environment to the other (i.e., from Prod to Test or from Prod to QA). To do a full database restore (from one environment to the other), the restore may take an hour and then the post restore steps take more time. To save time, we are now looking into how we can just move some of the data. I have read where files and file groups can be backed up and restored, and SSIS Packages can be used to export/import selected tables.
1) Is backing up files and/or file groups the best route to take?
2) By moving specific files and/or file groups, would this affect referential integrity or cause any other issues?
3) Are there any other methods, techniques or tools to move data from environment to environment?
Please provide any suggestions, 'best practices' and other items that need to be considered.
Thanks in advance.
September 22, 2015 at 8:24 am
HookSqlDba7 (9/22/2015)
1) Is backing up files and/or file groups the best route to take?
backup\restore is a good way to move a copy of a database
HookSqlDba7 (9/22/2015)
2) By moving specific files and/or file groups, would this affect referential integrity or cause any other issues?
yes it will, filegroup backup\restores are designed to support peacemeal restores, you'll still need the rest of the database filegroup backups restored and any tran log backups to make the database consistent
HookSqlDba7 (9/22/2015)
3) Are there any other methods, techniques or tools to move data from environment to environment?Please provide any suggestions, 'best practices' and other items that need to be considered.
Thanks in advance.
Do you only want certain tables copied, not the whole database?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 22, 2015 at 9:20 am
Thanks Perry. To your question, "Do you only want certain tables copied, not the whole database?" Yea, there maybe 50 tables on FG A and 10 Tables on FG B.
September 22, 2015 at 9:24 am
either move the whole database or maybe use replication if it's an option, depends on your requirements and environment
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 22, 2015 at 4:02 pm
If it's only a small chunk of the overall database, you might look into building an Integrations Services (SSIS) package to do the move. Otherwise, for a subset, it's probably best to use replication as was already suggested. None of the other options for data movement are going to allow you to only move a subset of the data.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 23, 2015 at 6:28 am
Grant Fritchey (9/22/2015)
If it's only a small chunk of the overall database, you might look into building an Integrations Services (SSIS) package to do the move. Otherwise, for a subset, it's probably best to use replication as was already suggested. None of the other options for data movement are going to allow you to only move a subset of the data.
Its not real pretty, but i have in the past created a 'savemeforpostrestore' database that contained things like test data that could not be recreated for some reason, along with scripts and SSIS packages to process it back in.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply