Filegroups and physical files

Hello All,
I am trying to analyse a DB schema created by someone
else. There is a file groups. How can I find out how many
files (and on what drives) belong to that file group?
Thanks a lot in advance for your help.
regards,
Abhishek.Hi,
If you are using enterprise manager right click on the database and look at
the properties.
From TSQL do this
select * from master.dbo.sysaltfiles
--
I hope this helps
regards
Greg O MCSD
SQL Scribe Documentation Builder
Document any SQL server database in minutes
Programmers love it, DBA dream of it
AGS SQL Scribe download a 30 day trial today
http://www.ag-software.com/ags_scribe_index.asp
"Abhishek Srivastava" <abhishek@.nospam.net> wrote in message
news:085701c345d8$7178cae0$a301280a@.phx.gbl...
> Hello All,
> I am trying to analyse a DB schema created by someone
> else. There is a file groups. How can I find out how many
> files (and on what drives) belong to that file group?
> Thanks a lot in advance for your help.
> regards,
> Abhishek.|||You can list all database files along with the file group name with
sp_helpdb:
EXEC sp_helpdb 'MyDatabase'
You can list files in a specific file group with sp_helpfilegroup:
USE MyDatabase
EXEC sp_helpfilegroup 'PRIMARY'
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Abhishek Srivastava" <abhishek@.nospam.net> wrote in message
news:085701c345d8$7178cae0$a301280a@.phx.gbl...
> Hello All,
> I am trying to analyse a DB schema created by someone
> else. There is a file groups. How can I find out how many
> files (and on what drives) belong to that file group?
> Thanks a lot in advance for your help.
> regards,
> Abhishek.

filegroup is offline after restore

I am using SQL-2005 Beta. I created 7 filegroups and associate them to
a table "Myhour" through partition function and schema. It works fine.
Then I tried the following statements to backup and retore the first
filegroup:
BACKUP DATABASE Mydb
FILE='20050204' -- my first filegroup
TO DISK='C:\backup\20050204.bak'
GO
RESTORE DATABASE Mydb FROM DISK='C:\backup\20050204.bak'
GO
The execution is OK. However, after that, I could not access data from
Myhour table:
SELECT * from Myhour
GO
The error message is that '20050204' filegroup is offline. I tried
many ways. I could not figure out how to bring this filegroup online.
I event tried to remove the filegroup. I got the same offline message.
Any way to bring filegroup online?
David Chu
Information regarding filegroup restore is available in Books Online. Basically, you restored part
of the database to an earlier point in time. You don't want SQL Server to be all happy and let you
use this possibly inconsistent database? So you need to apply the transaction log backups since the
database backup was performed so that SQL Server can re-apply the work that has been performed for
that filegroup. New for SQL Server 2005 is that you can set that filegroup to read-only (before the
backup and not change it back to read write) and then restore of transaction logs are not necessary
as SQL Server would know that no data has been changed in the filegroup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"David Chu" <chudq@.hotmail.com> wrote in message
news:ec313994.0502090925.2966a624@.posting.google.c om...
>I am using SQL-2005 Beta. I created 7 filegroups and associate them to
> a table "Myhour" through partition function and schema. It works fine.
> Then I tried the following statements to backup and retore the first
> filegroup:
> BACKUP DATABASE Mydb
> FILE='20050204' -- my first filegroup
> TO DISK='C:\backup\20050204.bak'
> GO
> RESTORE DATABASE Mydb FROM DISK='C:\backup\20050204.bak'
> GO
> The execution is OK. However, after that, I could not access data from
> Myhour table:
> SELECT * from Myhour
> GO
> The error message is that '20050204' filegroup is offline. I tried
> many ways. I could not figure out how to bring this filegroup online.
> I event tried to remove the filegroup. I got the same offline message.
> Any way to bring filegroup online?
> David Chu
|||OK. I may do something not correctly to restore filegroup. For my
current position, is there way to bring my offline filegroup
(20050204) back on line? Without it back on line, I could not do
anything about my table Myhour because 20050204 is a partition of the
table.
|||Try backing up the transaction log, restore the file group and then restore the transaction log
backup. But check Books Online first. It was a while since I worked with filegroup backup and
restore, so I might be a bit rusty on the details.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"David Chu" <chudq@.hotmail.com> wrote in message
news:ec313994.0502100818.72e989f3@.posting.google.c om...
> OK. I may do something not correctly to restore filegroup. For my
> current position, is there way to bring my offline filegroup
> (20050204) back on line? Without it back on line, I could not do
> anything about my table Myhour because 20050204 is a partition of the
> table.

filegroup is offline after restore

I am using SQL-2005 Beta. I created 7 filegroups and associate them to
a table "Myhour" through partition function and schema. It works fine.
Then I tried the following statements to backup and retore the first
filegroup:
BACKUP DATABASE Mydb
FILE='20050204' -- my first filegroup
TO DISK='C:\backup\20050204.bak'
GO
RESTORE DATABASE Mydb FROM DISK='C:\backup\20050204.bak'
GO
The execution is OK. However, after that, I could not access data from
Myhour table:
SELECT * from Myhour
GO
The error message is that '20050204' filegroup is offline. I tried
many ways. I could not figure out how to bring this filegroup online.
I event tried to remove the filegroup. I got the same offline message.
Any way to bring filegroup online?
David ChuInformation regarding filegroup restore is available in Books Online. Basica
lly, you restored part
of the database to an earlier point in time. You don't want SQL Server to be
all happy and let you
use this possibly inconsistent database? So you need to apply the transactio
n log backups since the
database backup was performed so that SQL Server can re-apply the work that
has been performed for
that filegroup. New for SQL Server 2005 is that you can set that filegroup t
o read-only (before the
backup and not change it back to read write) and then restore of transaction
logs are not necessary
as SQL Server would know that no data has been changed in the filegroup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"David Chu" <chudq@.hotmail.com> wrote in message
news:ec313994.0502090925.2966a624@.posting.google.com...
>I am using SQL-2005 Beta. I created 7 filegroups and associate them to
> a table "Myhour" through partition function and schema. It works fine.
> Then I tried the following statements to backup and retore the first
> filegroup:
> BACKUP DATABASE Mydb
> FILE='20050204' -- my first filegroup
> TO DISK='C:\backup\20050204.bak'
> GO
> RESTORE DATABASE Mydb FROM DISK='C:\backup\20050204.bak'
> GO
> The execution is OK. However, after that, I could not access data from
> Myhour table:
> SELECT * from Myhour
> GO
> The error message is that '20050204' filegroup is offline. I tried
> many ways. I could not figure out how to bring this filegroup online.
> I event tried to remove the filegroup. I got the same offline message.
> Any way to bring filegroup online?
> David Chu|||OK. I may do something not correctly to restore filegroup. For my
current position, is there way to bring my offline filegroup
(20050204) back on line? Without it back on line, I could not do
anything about my table Myhour because 20050204 is a partition of the
table.|||Try backing up the transaction log, restore the file group and then restore
the transaction log
backup. But check Books Online first. It was a while since I worked with fil
egroup backup and
restore, so I might be a bit rusty on the details.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"David Chu" <chudq@.hotmail.com> wrote in message
news:ec313994.0502100818.72e989f3@.posting.google.com...
> OK. I may do something not correctly to restore filegroup. For my
> current position, is there way to bring my offline filegroup
> (20050204) back on line? Without it back on line, I could not do
> anything about my table Myhour because 20050204 is a partition of the
> table.

filegroup is offline after restore

I am using SQL-2005 Beta. I created 7 filegroups and associate them to
a table "Myhour" through partition function and schema. It works fine.
Then I tried the following statements to backup and retore the first
filegroup:
BACKUP DATABASE Mydb
FILE='20050204' -- my first filegroup
TO DISK='C:\backup\20050204.bak'
GO
RESTORE DATABASE Mydb FROM DISK='C:\backup\20050204.bak'
GO
The execution is OK. However, after that, I could not access data from
Myhour table:
SELECT * from Myhour
GO
The error message is that '20050204' filegroup is offline. I tried
many ways. I could not figure out how to bring this filegroup online.
I event tried to remove the filegroup. I got the same offline message.
Any way to bring filegroup online?
David ChuInformation regarding filegroup restore is available in Books Online. Basically, you restored part
of the database to an earlier point in time. You don't want SQL Server to be all happy and let you
use this possibly inconsistent database? So you need to apply the transaction log backups since the
database backup was performed so that SQL Server can re-apply the work that has been performed for
that filegroup. New for SQL Server 2005 is that you can set that filegroup to read-only (before the
backup and not change it back to read write) and then restore of transaction logs are not necessary
as SQL Server would know that no data has been changed in the filegroup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"David Chu" <chudq@.hotmail.com> wrote in message
news:ec313994.0502090925.2966a624@.posting.google.com...
>I am using SQL-2005 Beta. I created 7 filegroups and associate them to
> a table "Myhour" through partition function and schema. It works fine.
> Then I tried the following statements to backup and retore the first
> filegroup:
> BACKUP DATABASE Mydb
> FILE='20050204' -- my first filegroup
> TO DISK='C:\backup\20050204.bak'
> GO
> RESTORE DATABASE Mydb FROM DISK='C:\backup\20050204.bak'
> GO
> The execution is OK. However, after that, I could not access data from
> Myhour table:
> SELECT * from Myhour
> GO
> The error message is that '20050204' filegroup is offline. I tried
> many ways. I could not figure out how to bring this filegroup online.
> I event tried to remove the filegroup. I got the same offline message.
> Any way to bring filegroup online?
> David Chu|||OK. I may do something not correctly to restore filegroup. For my
current position, is there way to bring my offline filegroup
(20050204) back on line? Without it back on line, I could not do
anything about my table Myhour because 20050204 is a partition of the
table.|||Try backing up the transaction log, restore the file group and then restore the transaction log
backup. But check Books Online first. It was a while since I worked with filegroup backup and
restore, so I might be a bit rusty on the details.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"David Chu" <chudq@.hotmail.com> wrote in message
news:ec313994.0502100818.72e989f3@.posting.google.com...
> OK. I may do something not correctly to restore filegroup. For my
> current position, is there way to bring my offline filegroup
> (20050204) back on line? Without it back on line, I could not do
> anything about my table Myhour because 20050204 is a partition of the
> table.