SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


copying partial data between servers


copying partial data between servers

Author
Message
Kristen Bates
Kristen Bates
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 8
I need a way of restoring partial data from our Production database to Development. The Production database, EDS, is 1.5 TB containing 180 tables all partitioned on the same scheme by month. Every month of data is about 100 GB. The primary filegroup only contains system tables. Every month is in it's own filegroup, currently with 8 files in each filegroup. On Development, we only want to store 1 month of data. This is so we can save time in syncing Dev to Prod, and also save storage space on Development. The developers need the Dev environment synced to Prod quite often, every 1-2 weeks, and we need a way to do that that takes <= 4 hours. There are frequent DDL changes between Dev and Prod, so we need a method of syncing the two databases that will include DDL changes as well as data.

Everything I have tried has failed. All attempts to backup a single filegroup on Prod and restore that one filegroup to Dev failed because SQL Server did not recognize the backup as being from the current backup set. So I am trying to find a way to trick the system by not using backups, but just copying the files. My latest experiment was this:
1. create empty EDS database on Dev that matches Prod exactly - same table structure, same partition scheme, same data file names and locations, etc.
2. take Prod offline
3. copy the 8 data files for the selected month, and also the primary file and the log file into a separate folder.
4. bring Prod online
5. detach Dev EDS database
6. copy and paste the 8 data files and the primary data and log file over the existing Dev files
7. Attach the database.
8. Error: "One or more files does not match the primary file for the database".

Is there some other method that might work? I have not tried the partial backup or piecemeal backup methods and don't know much about them. Let me know if anyone has any ideas.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search