August 28, 2005 at 8:44 pm
Hi,
I need to restore a PROD db from a DEV db.
The PROD db has multiple user-defined filegroups and multiple data files (.ndf) in each of them but DEV db only have PRIMARY filegroup (only 1 .mdf).
I undestand that if I just restore from PRIMARY (DEV)to PRIMARY (PROD), all user-defined filegroup will be gone.
Q1 How do I make sure that the user-defined filegroups remain and the some highly access tables and index go to the corresponding filegroups in PROD ?
Q2. I noticed for some tables I can't specify (using the EM. Design Table -> Properties -> Tables tab : Text filegroup) the Text columns to go to user-defined filegrop created just for text/image storage?
Appreciate advice/experience from someone who has done this before.TQ.
August 29, 2005 at 8:44 am
Can you backup the database in dev and then restore in prod? Otherwise, you could script the alter database statements to make the PROD look like DEV, and then import the data, but that will take a lot of time and attention.
Michelle
August 29, 2005 at 7:44 pm
Yes, it's DB restore from DEV to PROD.
use master
go
RESTORE DATABASE [DB1]
FROM DISK = 'D:\DBA\MSSQL\BACKUP\DISK[DB1]_AFT.BAK'
WITH Recovery,
MOVE '[DB1]_Data' TO 'D:\MSSQL\DATA\[DB1]_Data.MDF',
MOVE '[DB1]_Log' TO 'D:\MSSQL\data\[DB1]_Log.LDF',
REPLACE
go
Because DEV only has only 1 .mdf & .ldf file, so when I perform a restore all sys cum user tables & indexes in DEV will goes to .mdf in PROD, right?
How do I move the existing user-defined filegroups in PROD (already with data stored and the same data may also exists in DEV) back to PROD database and avoid duplication of data that already has in PROD & DEV?
August 30, 2005 at 8:12 am
Do you just need the data moved to the PROD database? If so, you could truncate the tables and then DTS the data...that would keep the filegroups in PROD and keep the indexes on the right filegroups. In this scenario, you could then create alter table statements if you need to change the table structures.
The best thing to do, however, would be to set up a new dev database with the same filegroups as the PROD, so they mirror each other. Then when it comes time to move to PROD, you can use the RESTORE DATABASE with the WITH MOVE options.
Michelle
August 30, 2005 at 8:20 pm
PROD & DEV is on diff site and on diff LAN zone. I can't use DTS. The data is in tens of GB in size and 300s of tables. If i use DTS, network bandwidth will compromised, right?
How I wish the DEV will has the same server config & filegroups as PROD but sighs.......sadly it's NO from mgmt (cost always come first). PROD is a SAN box while DEV is normal IDE disks.
August 30, 2005 at 11:36 pm
But there is no need for the dev to have a san. You could run it on a USB disk if you wanted. The thing that is important is that you set it up so it has the same number of files placed in the same filegroups as in prod. They can be placed at the same physical location, but when you restore the db to prod you specify WITH MOVE for the files to place them at the correct locations on prod.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply