x

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

more ▼

asked Apr 15 '11 at 11:11 AM in Default

shelleybobelly gravatar image

shelleybobelly
11 1 1 1

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.
Apr 15 '11 at 04:00 PM shelleybobelly
What error are you getting? It is possible to do this.
Apr 16 '11 at 03:27 AM Grant Fritchey ♦♦
No error, it just restores all filegroups.
Apr 16 '11 at 10:39 AM shelleybobelly
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.
more ▼

answered Apr 15 '11 at 01:26 PM

Tim gravatar image

Tim
35.5k 32 40 138

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)
10|1200 characters needed characters left

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'
more ▼

answered Apr 15 '11 at 01:28 PM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
90.5k 19 21 74

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)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x101
x44

asked: Apr 15 '11 at 11:11 AM

Seen: 2843 times

Last Updated: Apr 15 '11 at 11:11 AM