October 28, 2011 at 9:50 am
Our production DB has 4 total files associated with it:
1 File_A - Primary File Group- 15 GB
2 File_B - ExternalSystemData File Group - 250 MB
3 File_C - StaticData File Group - 100 GB
4 Log File
We like to refresh File_A and File_B in our development environment on a regular basis. We would like to refresh File_B too but it has become so large that moving it over our current network set-up is no longer a valid option. We don’t really need to refresh File_B often because these are mostly static files that do not change much, although we would still like File_B to be online and available in our development environment. What we have been doing lately is just making file/filegroup back-ups and restoring those to our development environment using the following:
After creating the backup containing the file/filegroup File_A?Primary and File_B?ExternalSystemData through SSMS we run the following to restore to our development environment:
RESTORE DATABASE DEV_DB
FILEGROUP = 'PRIMARY'
FROM Disk = 'C:\temp\Production.bak'
WITH PARTIAL,REPLACE,RECOVERY
RESTORE DATABASE DEV_DB
FILEGROUP = 'ExternalSystemData'
FROM Disk = 'C:\temp\Production.bak'
WITH PARTIAL,REPLACE,RECOVERY
The restores seem to work fine for those files/filegroups but once they complete File_C is taken offline and becomes unusable. We want to be able to restore File_A and File_B from prod to dev without losing the ability to use File_C.
Any suggestions?
October 28, 2011 at 10:10 am
What you need to do after that is then restore all the log backups taken on prod between when the backup of FileGroupC was taken and when that last backup was taken. This is because the entire database must be restored to a consistent state and an exact same time.
Other option, if the static filegroup is really static, mark it readonly on production, then restore it once to dev and there will be no need to restore logs.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply