How do I backup and restore ONLY the primary filegroup?
Hi, 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
BACKUP DATABASE DB_NAME FILEGROUP = 'FILEGROUPNAME' TO DISK = 'YOURPATH\YOURFILENAME.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: BACKUP DATABASE x FILE = 'LogicalFileName' TO DISK = 'somepath' WITH INIT; --or BACKUP DATABASE x FILEGROUP = 'Primary' TO DISK = 'somepath' WITH INIT To restore, you just go the other way: RESTORE DATABASE x FILEGROUP = 'Primary' FROM DISK = 'sompath'