I have a SQL 2005 database that has several schemas. We are needing to copy this database to a QA and eventually a prod server. One of the schemas is "dev" and we are not interested in copying any of that data. The dev tables are of a significant size, so restoring the entire database and then dropping the dev items is not ideal. Since we can't put a schema on a separate file group, I "borrowed" a script that enumerates all the tables/indexes in DEV and moves them over to a different filegroup, in anticipation of just restoring the PRIMARY filegroup only.
I have moved all objects and some of the data to my local server (where I am sysadmin) so I can practice on backup/restore to another DB. My problem is that no matter how I back it up, it seems to write BOTH filegroups the the backup file so every time I restore, I get both primary and secondary filegroups, when all I want is primary. I've tried making the secondary read_only then restoring read_write, among every other kind of permutation I can think of, but it keeps restoring primary and secondary. Any ideas?
The original DB is in simple recovery
asked Apr 15 '11 at 11:11 AM in Default
BACKUP DATABASE DB_NAME FILEGROUP = 'FILEGROUPNAME' TO DISK = 'YOURPATHYOURFILENAME.BAK' WITH INIT Similar script for your restore but you have to PARTIAL. An example
RESTORE DATABASE DB_NAME FILEGROUP = 'FILEGROUPNAME' FROM BACKUP WITH PARTIAL, NORECOVERY
BOL has additional information of piecemeal restores.
To backup the file or file group:
To restore, you just go the other way: