Restoring single filegroup

8 Dec 2008 4:45 PM

subs

I have a live database server which I want to mirror on a test server
every month. I have around 30 databases on each server, all with the
same structure and filegroups. Each database has two filegroups,
PRIMARY and AUDITLOGS.

PRIMARY contains the actual application data that I want restored to
the test server.

AUDITLOGS contains audit data which I don't want restored. There are
two reasons why I dont want AUDITLOGS restored 1) I want seperate
audit data for test and live environments 2) AUDITLOGS takes up around
90% of the total database size.

I am taking filegroup backups on live, a backup of PRIMARY every
night, and a backup of AUDITLOGS every week. What I want to do is to
copy all the PRIMARY backups once a month and restore on test.

When I restore just the PRIMARY filegroup it leaves the database in a
'restoring' state. The exact message after restore is:

"This RESTORE statement successfully performed some actions, but the
database could not be brought online because one or more RESTORE steps
are needed. Previous messages indicate reasons why recovery cannot
occur at this point."

Can someone advise of the correct syntax for restoring a single
filegroup, leaving the other intact?

Many Thanks.

Red.

8 Dec 2008 8:49 PM

Rick Sawtell

It sounds like you want to do a piecemeal restore.

If you have Enterprise edition this should be a snap.

Ensure that you are using the PARTIAL keyword in your restore scripts and
replay the logs appropriately.

Take a look in BOL or do a search on piecemeal restore for SQL Server.

Rick Sawtell
MCT, MCSD, MCDBA, MCITPro: Database Administration

9 Dec 2008 12:03 AM

subs

On 8 Dec, 20:49, "Rick Sawtell" <r_sawt...@nospam.hotmail.com> wrote:
> It sounds like you want to do a piecemeal restore.
>
> If you have Enterprise edition this should be a snap.
>
> Ensure that you are using the PARTIAL keyword in your restore scripts and
> replay the logs appropriately.
>
> Take a look in BOL or do a search on piecemeal restore for SQL Server.
>
> Rick Sawtell
> MCT, MCSD, MCDBA, MCITPro: Database Administration

Thanks for the reply. Did a quick search, and that certainly looks
like what I need to do. The one drawback I have is I'm dealing with
Standard Edition. Does that mean its not possible without restoring
from a whole backup? (As I mentioned before, I only need to restore
around 10% of the database size which would be the difference between
50Gb and 500Gb of backup files to process, as well as move between
servers!).

Red.

9 Dec 2008 7:50 AM

Tibor Karaszi

> The one drawback I have is I'm dealing with
> Standard Edition. Does that mean its not possible without restoring
> from a whole backup?

You should be able to do this using Standard Edition. Piecemeal
Restore refers to restore piece by piece, where what you already
restore is online while restoring the subsequent pieces. I.e., an
implementation of online restore (which also requires EE). You only
need to PARTIAL option. I haven't tried this in SE myself, but that is
my understanding.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

<s***@gambool.com> wrote in message

Show quoteHide quote

news:7d5f2be7-edbc-4cde-99dd-b28139109a49@f18g2000vbf.googlegroups.com...
> On 8 Dec, 20:49, "Rick Sawtell" <r_sawt...@nospam.hotmail.com>
> wrote:
>> It sounds like you want to do a piecemeal restore.
>>
>> If you have Enterprise edition this should be a snap.
>>
>> Ensure that you are using the PARTIAL keyword in your restore
>> scripts and
>> replay the logs appropriately.
>>
>> Take a look in BOL or do a search on piecemeal restore for SQL
>> Server.
>>
>> Rick Sawtell
>> MCT, MCSD, MCDBA, MCITPro: Database Administration
>
> Thanks for the reply. Did a quick search, and that certainly looks
> like what I need to do. The one drawback I have is I'm dealing with
> Standard Edition. Does that mean its not possible without restoring
> from a whole backup? (As I mentioned before, I only need to restore
> around 10% of the database size which would be the difference
> between
> 50Gb and 500Gb of backup files to process, as well as move between
> servers!).
>
> Red.

9 Dec 2008 10:09 AM

subs

On 9 Dec, 07:50, "Tibor Karaszi"
<tibor_please.no.email_kara***@hotmail.nomail.com> wrote:
Show quoteHide quote

> > The one drawback I have is I'm dealing with
> > Standard Edition. Does that mean its not possible without restoring
> > from a whole backup?
>
> You should be able to do this using Standard Edition. Piecemeal
> Restore refers to restore piece by piece, where what you already
> restore is online while restoring the subsequent pieces. I.e., an
> implementation of online restore (which also requires EE). You only
> need to PARTIAL option. I haven't tried this in SE myself, but that is
> my understanding.
>
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>
> <s***@gambool.com> wrote in message
>
> news:7d5f2be7-edbc-4cde-99dd-b28139109a49@f18g2000vbf.googlegroups.com...
>
> > On 8 Dec, 20:49, "Rick Sawtell" <r_sawt...@nospam.hotmail.com>
> > wrote:
> >> It sounds like you want to do a piecemeal restore.
>
> >> If you have Enterprise edition this should be a snap.
>
> >> Ensure that you are using the PARTIAL keyword in your restore
> >> scripts and
> >> replay the logs appropriately.
>
> >> Take a look in BOL or do a search on piecemeal restore for SQL
> >> Server.
>
> >> Rick Sawtell
> >> MCT, MCSD, MCDBA, MCITPro: Database Administration
>
> > Thanks for the reply. Did a quick search, and that certainly looks
> > like what I need to do. The one drawback I have is I'm dealing with
> > Standard Edition. Does that mean its not possible without restoring
> > from a whole backup? (As I mentioned before, I only need to restore
> > around 10% of the database size which would be the difference
> > between
> > 50Gb and 500Gb of backup files to process, as well as move between
> > servers!).
>
> > Red.

Thanks for your help Rick. The following worked perfectly:

RESTORE DATABASE MyDatabase
FILEGROUP = 'PRIMARY'
FROM
DISK = 'F:\SQL_Backups\MyDatabase_PRIMARY.bak'
WITH
PARTIAL, FILE = 1, NOUNLOAD, STATS = 10
GO

Red.

8 Dec 2008 11:55 PM

bass_player

Have a look at this article as a reference for restoring filegroups
http://www.mssqltips.com/tip.asp?tip=1613

<s***@gambool.com> wrote in message
Show quoteHide quote

news:6f4d9145-003d-4d6f-9d2a-59262c37ec9f@w24g2000prd.googlegroups.com...
>I have a live database server which I want to mirror on a test server
> every month. I have around 30 databases on each server, all with the
> same structure and filegroups. Each database has two filegroups,
> PRIMARY and AUDITLOGS.
>
> PRIMARY contains the actual application data that I want restored to
> the test server.
>
> AUDITLOGS contains audit data which I don't want restored. There are
> two reasons why I dont want AUDITLOGS restored 1) I want seperate
> audit data for test and live environments 2) AUDITLOGS takes up around
> 90% of the total database size.
>
> I am taking filegroup backups on live, a backup of PRIMARY every
> night, and a backup of AUDITLOGS every week. What I want to do is to
> copy all the PRIMARY backups once a month and restore on test.
>
> When I restore just the PRIMARY filegroup it leaves the database in a
> 'restoring' state. The exact message after restore is:
>
> "This RESTORE statement successfully performed some actions, but the
> database could not be brought online because one or more RESTORE steps
> are needed. Previous messages indicate reasons why recovery cannot
> occur at this point."
>
> Can someone advise of the correct syntax for restoring a single
> filegroup, leaving the other intact?
>
> Many Thanks.
>
> Red.