How do I backup and restore ONLY the primary filegroup?

Question

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

Answer 1

0

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.

answered Apr 15 '11 at 01:26 PM

Tim
31.5k ● 20 ● 30 ● 116

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.

Apr 15 '11 at 03:16 PM shelleybobelly

If you don't intend on restoring the other file group chose with Recovery.

Apr 15 '11 at 03:44 PM Tim

(comments are locked)

Answer 2

0

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'

answered Apr 15 '11 at 01:28 PM

Grant Fritchey ♦♦
62.1k ● 12 ● 19 ● 66

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.

Apr 15 '11 at 03:17 PM shelleybobelly

(comments are locked)

How do I backup and restore ONLY the primary filegroup?

Follow this question

Related Questions