November 6, 2007 at 8:50 am
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.
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply