Filegroup restore

Say I have a database with 3 user filegroups (FG1,FG2,FG3) and have 3 tables
(T1,T2,T3) created on each of the filegroup respectively.
Do I need to perform a full database backup before I start performing
individual FG backups ?
If not, and say I backup FG1 and do not have backups for FG2 and FG3, Can I
restore the database with just FG1 and have atleast the table T1 tied to it
? or do I need to restore all the FGs to make the database active again ?
I know SQL 2005 has something where we can restore just the Primary FG and
the database can be up again .. Just dont know about SQL 2000.
Any help here would be much appreciated . Thanks
Hi
The partial DB online is a new feature in SQL Server 2005.
From BOL for SQL Server 2000:
"Use BACKUP to back up database files and filegroups instead of the full
database when time constraints make a full database backup impractical. To
back up a file instead of the full database, put procedures in place to
ensure that all files in the database are backed up regularly. Also,
separate transaction log backups must be performed. After restoring a file
backup, apply the transaction log to roll the file contents forward to make
it consistent with the rest of the database"
The key point is that if you do a restore, you need all the transaction
logs from the time the file group backup was made, up to the other most
current file group's transaction. An the Db need to be put into a loading
state, so you can not restore whilst users are using the DB.
Even on our very big DB's, we don't use filegroup backups as the chances for
a problem occurring a re so much bigger as each transaction log needs to be
fully accounted for.
If space is an issue for you, look at full Backup, Transaction log and
Incremental Backup cycle as an alternative, but more manageable solution.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:ukxDZ#XLFHA.3356@.TK2MSFTNGP12.phx.gbl...
> Say I have a database with 3 user filegroups (FG1,FG2,FG3) and have 3
tables
> (T1,T2,T3) created on each of the filegroup respectively.
> Do I need to perform a full database backup before I start performing
> individual FG backups ?
> If not, and say I backup FG1 and do not have backups for FG2 and FG3, Can
I
> restore the database with just FG1 and have atleast the table T1 tied to
it
> ? or do I need to restore all the FGs to make the database active again ?
> I know SQL 2005 has something where we can restore just the Primary FG and
> the database can be up again .. Just dont know about SQL 2000.
> Any help here would be much appreciated . Thanks
>
|||Well its the backups we were looking at but most important being able to
scale . Looking at multiple tables on different FGs and then using a
partitioned view.. And that being.. historical data would stay in a
filegroup that would never change.. So all data before this year would be in
some FGs that would never be updated and could be in read only state. So
backing those once a month may suffice. So this is all in thinking stage
right now and hence wanted to know what to do when say a server crashes
and I may not have the latest FG backup i,e of this year.. but does that
mean I can restore all the previous years FGs that I may have and have the
database up and running ?
Thats where Im a bit confused on what I need to restore and would it work
Thanks
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:eGwGiRYLFHA.4028@.tk2msftngp13.phx.gbl...
> Hi
> The partial DB online is a new feature in SQL Server 2005.
> From BOL for SQL Server 2000:
> "Use BACKUP to back up database files and filegroups instead of the full
> database when time constraints make a full database backup impractical. To
> back up a file instead of the full database, put procedures in place to
> ensure that all files in the database are backed up regularly. Also,
> separate transaction log backups must be performed. After restoring a file
> backup, apply the transaction log to roll the file contents forward to
make
> it consistent with the rest of the database"
> The key point is that if you do a restore, you need all the transaction
> logs from the time the file group backup was made, up to the other most
> current file group's transaction. An the Db need to be put into a loading
> state, so you can not restore whilst users are using the DB.
> Even on our very big DB's, we don't use filegroup backups as the chances
for
> a problem occurring a re so much bigger as each transaction log needs to
be[vbcol=seagreen]
> fully accounted for.
> If space is an issue for you, look at full Backup, Transaction log and
> Incremental Backup cycle as an alternative, but more manageable solution.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:ukxDZ#XLFHA.3356@.TK2MSFTNGP12.phx.gbl...
> tables
Can[vbcol=seagreen]
> I
> it
?[vbcol=seagreen]
and
>
|||Hi
Currently with SQL Server 2000, if you have a FG backup done 1 June 2004 and
have a failure today. You need that FG backup, plus all transaction logs
since then (~10 months of log dumps). Not a feasible solution for you.
The exact same rule applies to SQL Server 2005. You need to transaction logs
as SQL Server can not assume that nothing has been done to those pages in
that filegroup since the backup was taken.
Currently, having the data in a separate DB, presented as a View would be
your answer.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:ehIALXYLFHA.3076@.tk2msftngp13.phx.gbl...
> Well its the backups we were looking at but most important being able to
> scale . Looking at multiple tables on different FGs and then using a
> partitioned view.. And that being.. historical data would stay in a
> filegroup that would never change.. So all data before this year would be
in[vbcol=seagreen]
> some FGs that would never be updated and could be in read only state. So
> backing those once a month may suffice. So this is all in thinking stage
> right now and hence wanted to know what to do when say a server crashes
> and I may not have the latest FG backup i,e of this year.. but does that
> mean I can restore all the previous years FGs that I may have and have the
> database up and running ?
> Thats where Im a bit confused on what I need to restore and would it work
> Thanks
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:eGwGiRYLFHA.4028@.tk2msftngp13.phx.gbl...
To[vbcol=seagreen]
file[vbcol=seagreen]
> make
loading[vbcol=seagreen]
> for
> be
solution.[vbcol=seagreen]
> Can
to[vbcol=seagreen]
again
> ?
> and
>
|||do i need the Tlogs to recover the db or just get it to point in time ? I am
not worried about getting it to the point in time .. If i restore the June
2004 FG, can I recover the database and have data up until June 2004 ?
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:OZmJ5gYLFHA.576@.TK2MSFTNGP15.phx.gbl...
> Hi
> Currently with SQL Server 2000, if you have a FG backup done 1 June 2004
and
> have a failure today. You need that FG backup, plus all transaction logs
> since then (~10 months of log dumps). Not a feasible solution for you.
> The exact same rule applies to SQL Server 2005. You need to transaction
logs[vbcol=seagreen]
> as SQL Server can not assume that nothing has been done to those pages in
> that filegroup since the backup was taken.
> Currently, having the data in a separate DB, presented as a View would be
> your answer.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:ehIALXYLFHA.3076@.tk2msftngp13.phx.gbl...
be[vbcol=seagreen]
> in
crashes[vbcol=seagreen]
the[vbcol=seagreen]
work[vbcol=seagreen]
full[vbcol=seagreen]
impractical.[vbcol=seagreen]
> To
to[vbcol=seagreen]
> file
transaction[vbcol=seagreen]
most[vbcol=seagreen]
> loading
chances[vbcol=seagreen]
to[vbcol=seagreen]
> solution.
3[vbcol=seagreen]
performing[vbcol=seagreen]
FG3,[vbcol=seagreen]
tied[vbcol=seagreen]
> to
> again
FG
>
|||Hi
Yes, but then no other FG can be later than June 2004.
Try this on your test machine and then document it as it becomes very
difficult to figure out things in a DR scenario.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eKcl0xcLFHA.244@.TK2MSFTNGP12.phx.gbl...
> do i need the Tlogs to recover the db or just get it to point in time ? I
am
> not worried about getting it to the point in time .. If i restore the
June[vbcol=seagreen]
> 2004 FG, can I recover the database and have data up until June 2004 ?
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:OZmJ5gYLFHA.576@.TK2MSFTNGP15.phx.gbl...
> and
> logs
in[vbcol=seagreen]
be[vbcol=seagreen]
to[vbcol=seagreen]
> be
So[vbcol=seagreen]
stage[vbcol=seagreen]
> crashes
that[vbcol=seagreen]
> the
> work
> full
> impractical.
> to
a[vbcol=seagreen]
to[vbcol=seagreen]
> transaction
> most
> chances
needs[vbcol=seagreen]
> to
and[vbcol=seagreen]
have[vbcol=seagreen]
> 3
> performing
> FG3,
> tied
Primary
> FG
>
|||Hassan
>Do I need to perform a full database backup before I >start performing
>individual FG backups ?
Yes , you have to do FULL BACKUP DATABASE and as Mike mentioned to perform
T-LOG BACKUP as well
CREATE DATABASE test
GO
ALTER DATABASE test SET RECOVERY FULL
ALTER DATABASE test
ADD FILEGROUP ww_Group
GO
ALTER DATABASE test
ADD FILE
( NAME = ww,
FILENAME = 'D:\wwdat1.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
TO FILEGROUP ww_Group
create table test..test(id int identity) on [primary]
create table test..test_GR(id int identity) on ww_Group
insert test..test default values
insert test..test_GR default values
SELECT * FROM test..test_GR
SELECT * FROM test..test
BACKUP DATABASE test
TO disk='D:\Test_backup.bak'with init
BACKUP DATABASE test
FILE = 'ww',
FILEGROUP = 'ww_Group'
TO disk='D:\CROUPFILES.bak'WITH INIT
BACKUP LOG test
TO disk='D:\Test__log.ldf'WITH INIT
BACKUP LOG test
TO disk='D:\Test__log.ldf' WITH NOINIT
GO
TRUNCATE TABLE test..test_GR
GO
RESTORE DATABASE test
from disk='D:\Test_backup.bak'WITH NORECOVERY
RESTORE DATABASE test
FILE = 'ww',
FILEGROUP = 'ww_Group'
FROM DISK ='D:\CROUPFILES.bak'
WITH FILE = 1,NORECOVERY
RESTORE LOG test
FROM disk='D:\Test__log.ldf'
WITH FILE = 1, NORECOVERY
RESTORE LOG test
FROM disk='D:\Test__log.ldf'
WITH FILE = 2, RECOVERY
GO
DROP DATABASE test
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eKcl0xcLFHA.244@.TK2MSFTNGP12.phx.gbl...
> do i need the Tlogs to recover the db or just get it to point in time ? I
am
> not worried about getting it to the point in time .. If i restore the
June[vbcol=seagreen]
> 2004 FG, can I recover the database and have data up until June 2004 ?
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:OZmJ5gYLFHA.576@.TK2MSFTNGP15.phx.gbl...
> and
> logs
in[vbcol=seagreen]
be[vbcol=seagreen]
to[vbcol=seagreen]
> be
So[vbcol=seagreen]
stage[vbcol=seagreen]
> crashes
that[vbcol=seagreen]
> the
> work
> full
> impractical.
> to
a[vbcol=seagreen]
to[vbcol=seagreen]
> transaction
> most
> chances
needs[vbcol=seagreen]
> to
and[vbcol=seagreen]
have[vbcol=seagreen]
> 3
> performing
> FG3,
> tied
Primary
> FG
>
|||In addition to the other posts: In SQL Server 2005, you will not need to apply the tlog backups if
the file group has been read only since the backup you restored.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Hassan" <fatima_ja@.hotmail.com> wrote in message news:eKcl0xcLFHA.244@.TK2MSFTNGP12.phx.gbl...
> do i need the Tlogs to recover the db or just get it to point in time ? I am
> not worried about getting it to the point in time .. If i restore the June
> 2004 FG, can I recover the database and have data up until June 2004 ?
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:OZmJ5gYLFHA.576@.TK2MSFTNGP15.phx.gbl...
> and
> logs
> be
> crashes
> the
> work
> full
> impractical.
> to
> transaction
> most
> chances
> to
> 3
> performing
> FG3,
> tied
> FG
>
|||Hi Uri
Thanks for such a good example. I played around your example and landed on
one more question.
After the backup of database and transaction log stage I add more changes to
the Primary group table by running "insert test..test default values". I
backed up the primary file group files and transaction log files and I ran
following steps
RESTORE DATABASE test
from disk='D:\Test_backup.bak'WITH NORECOVERY
RESTORE DATABASE test
FILE = 'test',
FILEGROUP = 'primary'
FROM DISK ='D:\CROUPFILES1.bak'
WITH FILE = 1,NORECOVERY
RESTORE LOG test
FROM disk='D:\Test__log.ldf'
WITH FILE = 1, NORECOVERY
RESTORE LOG test
FROM disk='D:\Test__log.ldf'
WITH FILE = 2, RECOVERY
I do not see new changes made after the full backup
Why so?
Regards
Mangesh
All other steps being the same I loose changes made after backup in the
primary file.
Does it mean that you can use FG (filegroup backup ) as a means of driving
element in the database recovery. I though
"Uri Dimant" wrote:

> Hassan
> Yes , you have to do FULL BACKUP DATABASE and as Mike mentioned to perform
> T-LOG BACKUP as well
> CREATE DATABASE test
> GO
> ALTER DATABASE test SET RECOVERY FULL
> ALTER DATABASE test
> ADD FILEGROUP ww_Group
> GO
> ALTER DATABASE test
> ADD FILE
> ( NAME = ww,
> FILENAME = 'D:\wwdat1.ndf',
> SIZE = 5MB,
> MAXSIZE = 100MB,
> FILEGROWTH = 5MB)
> TO FILEGROUP ww_Group
>
> create table test..test(id int identity) on [primary]
> create table test..test_GR(id int identity) on ww_Group
>
> insert test..test default values
> insert test..test_GR default values
> SELECT * FROM test..test_GR
> SELECT * FROM test..test
>
> BACKUP DATABASE test
> TO disk='D:\Test_backup.bak'with init
> BACKUP DATABASE test
> FILE = 'ww',
> FILEGROUP = 'ww_Group'
> TO disk='D:\CROUPFILES.bak'WITH INIT
> BACKUP LOG test
> TO disk='D:\Test__log.ldf'WITH INIT
> BACKUP LOG test
> TO disk='D:\Test__log.ldf' WITH NOINIT
> GO
> TRUNCATE TABLE test..test_GR
> GO
>
> RESTORE DATABASE test
> from disk='D:\Test_backup.bak'WITH NORECOVERY
>
> RESTORE DATABASE test
> FILE = 'ww',
> FILEGROUP = 'ww_Group'
> FROM DISK ='D:\CROUPFILES.bak'
> WITH FILE = 1,NORECOVERY
> RESTORE LOG test
> FROM disk='D:\Test__log.ldf'
> WITH FILE = 1, NORECOVERY
> RESTORE LOG test
> FROM disk='D:\Test__log.ldf'
> WITH FILE = 2, RECOVERY
> GO
> DROP DATABASE test
>
>
>
>
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:eKcl0xcLFHA.244@.TK2MSFTNGP12.phx.gbl...
> am
> June
> in
> be
> to
> So
> stage
> that
> a
> to
> needs
> and
> have
> Primary
>
>
|||This works for me fine
CREATE DATABASE test
GO
ALTER DATABASE test SET RECOVERY FULL
ALTER DATABASE test
ADD FILEGROUP ww_Group
GO
ALTER DATABASE test
ADD FILE
( NAME = ww,
FILENAME = 'D:\wwdat1.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
TO FILEGROUP ww_Group
create table test..test(id int identity) on [primary]
create table test..test_GR(id int identity) on ww_Group
insert test..test default values
insert test..test_GR default values
SELECT * FROM test..test_GR
SELECT * FROM test..test
BACKUP DATABASE test
TO disk='D:\Test_backup.bak'with init
BACKUP DATABASE test
FILE = 'test ',
FILEGROUP = 'primary'
TO disk='D:\CROUPFILES.bak'WITH INIT
BACKUP LOG test
TO disk='D:\Test__log.ldf'WITH INIT
BACKUP LOG test
TO disk='D:\Test__log.ldf' WITH NOINIT
GO
TRUNCATE TABLE test..test_GR
GO
RESTORE DATABASE test
from disk='D:\Test_backup.bak'WITH noRECOVERY
RESTORE DATABASE test
FILE = 'test',
FILEGROUP = 'primary'
FROM DISK ='D:\CROUPFILES.bak'
WITH FILE = 1,NORECOVERY
RESTORE LOG test
FROM disk='D:\Test__log.ldf'
WITH FILE = 1, NORECOVERY
RESTORE LOG test
FROM disk='D:\Test__log.ldf'
WITH FILE = 2, RECOVERY
GO
DROP DATABASE test
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:4E9B24F7-8A13-43B6-BF4D-208FB115429E@.microsoft.com...
> Hi Uri
> Thanks for such a good example. I played around your example and landed
on
> one more question.
> After the backup of database and transaction log stage I add more changes
to
> the Primary group table by running "insert test..test default values". I
> backed up the primary file group files and transaction log files and I ran
> following steps
> RESTORE DATABASE test
> from disk='D:\Test_backup.bak'WITH NORECOVERY
> RESTORE DATABASE test
> FILE = 'test',
> FILEGROUP = 'primary'
> FROM DISK ='D:\CROUPFILES1.bak'
> WITH FILE = 1,NORECOVERY
> RESTORE LOG test
> FROM disk='D:\Test__log.ldf'
> WITH FILE = 1, NORECOVERY
> RESTORE LOG test
> FROM disk='D:\Test__log.ldf'
> WITH FILE = 2, RECOVERY
>
> I do not see new changes made after the full backup
> Why so?
> Regards
> Mangesh
>
> All other steps being the same I loose changes made after backup in the
> primary file.
> Does it mean that you can use FG (filegroup backup ) as a means of
driving[vbcol=seagreen]
> element in the database recovery. I though
>
>
>
> "Uri Dimant" wrote:
perform[vbcol=seagreen]
? I[vbcol=seagreen]
2004[vbcol=seagreen]
logs[vbcol=seagreen]
you.[vbcol=seagreen]
transaction[vbcol=seagreen]
pages[vbcol=seagreen]
would[vbcol=seagreen]
able[vbcol=seagreen]
a[vbcol=seagreen]
a[vbcol=seagreen]
would[vbcol=seagreen]
state.[vbcol=seagreen]
does[vbcol=seagreen]
have[vbcol=seagreen]
it[vbcol=seagreen]
the[vbcol=seagreen]
place[vbcol=seagreen]
Also,[vbcol=seagreen]
restoring[vbcol=seagreen]
forward[vbcol=seagreen]
other[vbcol=seagreen]
a[vbcol=seagreen]
log[vbcol=seagreen]
and[vbcol=seagreen]
T1[vbcol=seagreen]
active[vbcol=seagreen]