asked
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
Nope, still no joy. I have tried all these. I'll just have to write a script that will drop all objects in the schema instead of trying to move the objects to a secondary filegroup and do a backup/restore of the primary filegroup only. Seems silly to move it all, take the space then delete it, but nothing else is working.
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.
When I use the NORECOVERY option the new database just hangs in "Restoring" mode and never comes completely on-line. I read the BOL on piecemeal, but it seems as if this is designed so some queries can be run while the db is still in "Restoring" mode, before the fullrestore.
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'
OK I'll try WITH INIT, which is the thing I didn't try. One other thing: I am trying to restore to a NEW DB on the same server, so am having to use the MOVE option on the files.
