Restore database and filegroups question

I have a situation that involves restoring a database to a sencond SQL
Server with 3 filegroups and files.
The problem is that when the db is restored to svr2, all of the objects that
were in other file groups wind up in the PRIMARY/default filegroup.
I'm trying to understand how this is happening, but I haven't actually
watched the people doing this, but that's how it ends up.
Any idea of what I am missing?This should not happen.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"frankm" <frankm@.nospam.postalias> wrote in message
news:%23tW98$ZJFHA.2648@.TK2MSFTNGP14.phx.gbl...
>I have a situation that involves restoring a database to a sencond SQL
> Server with 3 filegroups and files.
> The problem is that when the db is restored to svr2, all of the objects
> that
> were in other file groups wind up in the PRIMARY/default filegroup.
> I'm trying to understand how this is happening, but I haven't actually
> watched the people doing this, but that's how it ends up.
> Any idea of what I am missing?
>|||Hi,
Can you contact the people who perform the back/restore operations to
clarify how they backup and restore the database? In addition, what is the
result of running "RESTORE FILELISTONLY"?
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>From: "frankm" <frankm@.nospam.postalias>
>Subject: Restore database and filegroups question
>Date: Thu, 10 Mar 2005 12:35:50 -0600
>Lines: 11
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2800.1437
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
>Message-ID: <#tW98$ZJFHA.2648@.TK2MSFTNGP14.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: assocfw1.ms.acxiom.com 206.66.66.1
>Path:
TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP1
4.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:381260
>X-Tomcat-NG: microsoft.public.sqlserver.server
>I have a situation that involves restoring a database to a sencond SQL
>Server with 3 filegroups and files.
>The problem is that when the db is restored to svr2, all of the objects
that
>were in other file groups wind up in the PRIMARY/default filegroup.
>I'm trying to understand how this is happening, but I haven't actually
>watched the people doing this, but that's how it ends up.
>Any idea of what I am missing?
>
>|||FILELISTONLY comes back correctly.
Can a WITH MOVE, move the tables and indexes of other filegroups into
default/primary?
I'm doing some testing to see if I can reproduce this.
I caught this while looking at Queue Lengths in perfmon vs trans/sec on the
db's. The pattern wasn't right.
I found that in the original db that the filegroups were correct.
When I looked at the restored db, all the objects were in PRIMARY/DEFAULT,
although all the files from the source db are there, all the objects have
been moved into PRIMARY/DEFAULT.
This is really messing me up...I didn't know this could happen...
I'm trying to get the script that was used to restore the db....
"William Wang[MSFT]" <v-rxwang@.online.microsoft.com> wrote in message
news:EmyuU0hJFHA.3548@.TK2MSFTNGXA02.phx.gbl...
> Hi,
> Can you contact the people who perform the back/restore operations to
> clarify how they backup and restore the database? In addition, what is the
> result of running "RESTORE FILELISTONLY"?
> Sincerely,
> William Wang
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> --
> >From: "frankm" <frankm@.nospam.postalias>
> >Subject: Restore database and filegroups question
> >Date: Thu, 10 Mar 2005 12:35:50 -0600
> >Lines: 11
> >X-Priority: 3
> >X-MSMail-Priority: Normal
> >X-Newsreader: Microsoft Outlook Express 6.00.2800.1437
> >X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
> >Message-ID: <#tW98$ZJFHA.2648@.TK2MSFTNGP14.phx.gbl>
> >Newsgroups: microsoft.public.sqlserver.server
> >NNTP-Posting-Host: assocfw1.ms.acxiom.com 206.66.66.1
> >Path:
>
TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP1
> 4.phx.gbl
> >Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:381260
> >X-Tomcat-NG: microsoft.public.sqlserver.server
> >
> >I have a situation that involves restoring a database to a sencond SQL
> >Server with 3 filegroups and files.
> >The problem is that when the db is restored to svr2, all of the objects
> that
> >were in other file groups wind up in the PRIMARY/default filegroup.
> >
> >I'm trying to understand how this is happening, but I haven't actually
> >watched the people doing this, but that's how it ends up.
> >
> >Any idea of what I am missing?
> >
> >
> >
>|||Hi Frank,
Would you please list the detailed steps on how you reproduce this issue as
I could not get the same result as yours? Regardless of whether WITH MOVE
is used, a RESTORE statement always produces a database that is identical
to the one that was backed up. The database produced by the RESTORE will
have the same number of files and filegroups, and the files will be of the
same size, and the tables are still placed in the filegroups where they
originally reside. Here are the steps I've performed for testing purpose:
1. Run the following script :
USE master
GO
-- Create the database with the default data
-- filegroup and the log file. Specify the
-- growth increment and the max size for the
-- primary data file.
CREATE DATABASE MyDB
ON PRIMARY
( NAME='MyDB_Primary',
FILENAME= 'c:\Program Files\Microsoft SQL Server\MSSQL\data\MyDB_Prm.mdf',
SIZE=4,
MAXSIZE=10,
FILEGROWTH=1),
FILEGROUP MyDB_FG1
( NAME = 'MyDB_FG1_Dat1',
FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL\data\MyDB_FG1_1.ndf',
SIZE = 1MB,
MAXSIZE=10,
FILEGROWTH=1),
( NAME = 'MyDB_FG1_Dat2',
FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL\data\MyDB_FG1_2.ndf',
SIZE = 1MB,
MAXSIZE=10,
FILEGROWTH=1)
LOG ON
( NAME='MyDB_log',
FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL\data\MyDB.ldf',
SIZE=1,
MAXSIZE=10,
FILEGROWTH=1)
GO
-- Create a table in the user-defined filegroup.
USE MyDB
CREATE TABLE MyTable
( cola int PRIMARY KEY,
colb char(8) )
ON MyDB_FG1
GO
2. Backup the newly created database and copy the backup file to another
SQL Server box.
3. Restore the database on the destination server.
4. View the properties of MyTable, it is still in the MyDB_FG1 filegroup.
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>From: "frankm" <frankm@.nospam.postalias>
>References: <#tW98$ZJFHA.2648@.TK2MSFTNGP14.phx.gbl>
<EmyuU0hJFHA.3548@.TK2MSFTNGXA02.phx.gbl>
>Subject: Re: Restore database and filegroups question
>Date: Mon, 14 Mar 2005 13:10:36 -0600
>Lines: 71
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2800.1437
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
>Message-ID: <#rdqBmMKFHA.3340@.TK2MSFTNGP14.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: assocfw1.ms.acxiom.com 206.66.66.1
>Path:
TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP1
4.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:381637
>X-Tomcat-NG: microsoft.public.sqlserver.server
>FILELISTONLY comes back correctly.
>Can a WITH MOVE, move the tables and indexes of other filegroups into
>default/primary?
>I'm doing some testing to see if I can reproduce this.
>I caught this while looking at Queue Lengths in perfmon vs trans/sec on the
>db's. The pattern wasn't right.
>I found that in the original db that the filegroups were correct.
>When I looked at the restored db, all the objects were in PRIMARY/DEFAULT,
>although all the files from the source db are there, all the objects have
>been moved into PRIMARY/DEFAULT.
>This is really messing me up...I didn't know this could happen...
>I'm trying to get the script that was used to restore the db....
>
>
>"William Wang[MSFT]" <v-rxwang@.online.microsoft.com> wrote in message
>news:EmyuU0hJFHA.3548@.TK2MSFTNGXA02.phx.gbl...
>> Hi,
>> Can you contact the people who perform the back/restore operations to
>> clarify how they backup and restore the database? In addition, what is
the
>> result of running "RESTORE FILELISTONLY"?
>> Sincerely,
>> William Wang
>> Microsoft Online Partner Support
>> When responding to posts, please "Reply to Group" via your newsreader so
>> that others may learn and benefit from your issue.
>> This posting is provided "AS IS" with no warranties, and confers no
>rights.
>> --
>> >From: "frankm" <frankm@.nospam.postalias>
>> >Subject: Restore database and filegroups question
>> >Date: Thu, 10 Mar 2005 12:35:50 -0600
>> >Lines: 11
>> >X-Priority: 3
>> >X-MSMail-Priority: Normal
>> >X-Newsreader: Microsoft Outlook Express 6.00.2800.1437
>> >X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
>> >Message-ID: <#tW98$ZJFHA.2648@.TK2MSFTNGP14.phx.gbl>
>> >Newsgroups: microsoft.public.sqlserver.server
>> >NNTP-Posting-Host: assocfw1.ms.acxiom.com 206.66.66.1
>> >Path:
>TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP
1
>> 4.phx.gbl
>> >Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:381260
>> >X-Tomcat-NG: microsoft.public.sqlserver.server
>> >
>> >I have a situation that involves restoring a database to a sencond SQL
>> >Server with 3 filegroups and files.
>> >The problem is that when the db is restored to svr2, all of the objects
>> that
>> >were in other file groups wind up in the PRIMARY/default filegroup.
>> >
>> >I'm trying to understand how this is happening, but I haven't actually
>> >watched the people doing this, but that's how it ends up.
>> >
>> >Any idea of what I am missing?
>> >
>> >
>> >
>
>