Filegroup restore problems

Using SQL Server 2000 SP3 I'm developing a data warehouse where data
will be archived off to a filegroup, this filegroup backed up and the
tables in this filegroup truncated to free up space on the server.

So using Enterprise Manager I've copied data to the filegroup tables,
backed up the file group, truncated the tables, but when I have
restored the file group the database is stuck in a state of
'(loading...)' and I can't get it to resume. I've tried running the
command RESTORE LOG databaseName WITH RECOVERY but get an error message
that the database has been restored to different time (sorry I can't
remember exact syntax but I'm not at my work computer)

Is this familiar to anyone? Ignoring the filegroups is there a way I
can get the database .MDF back up and running?

ThanksHi

From "How to restore files and filegroups over existing files (Enterprise
Manager)" in BOL

Under Recovery completion state, do one of the following:
a.. Click Leave database operational. No additional transaction logs can
be restored if no further transaction log backups are to be applied.

b.. Click Leave database nonoperational, but able to restore additional
transaction logs if another transaction log backup is to be applied
It seems that the log file backup was not taken before the filegroup backup.
If you were not going to restore the logs then the 'Leave database
operational' should be checked.

HTH

John

<si.downes@.gmail.com> wrote in message
news:1131487927.857991.20510@.o13g2000cwo.googlegro ups.com...
> Using SQL Server 2000 SP3 I'm developing a data warehouse where data
> will be archived off to a filegroup, this filegroup backed up and the
> tables in this filegroup truncated to free up space on the server.
> So using Enterprise Manager I've copied data to the filegroup tables,
> backed up the file group, truncated the tables, but when I have
> restored the file group the database is stuck in a state of
> '(loading...)' and I can't get it to resume. I've tried running the
> command RESTORE LOG databaseName WITH RECOVERY but get an error message
> that the database has been restored to different time (sorry I can't
> remember exact syntax but I'm not at my work computer)
> Is this familiar to anyone? Ignoring the filegroups is there a way I
> can get the database .MDF back up and running?
> Thanks|||Hi,

try the command
restore database 'yourdatabase' 'with NORECOVERY

it will then disable loading state ... read restore in BOL with very
carefully theirs a option of norecovery in it , BOL is your friend.

ActiveCrypt
---------
http://www.activecrypt.com

Filegroup restore problem.

Hi!

Last time I applied new filegroup to our database. I call it HISTORY, bacause it stores all moditications of tables on PRIMARY filegroup. I.e. if on PRIMARY filegroup is table User, then on HISTORY filegroup is table UserHistory which, stores changes apllied to User table. It's simple database changes monitoring. We use SQL SERVER 2000 Standard Edition.

One person of our team (Artur) needs copy tables from PRIMARY filegroup to his computer to perform some experiments. Previously there aren't problem. He make full backup of our database, copy it to DVD drive and restore it on his computer. Backup was size of 3GB.

When we added monitoring full backup is size of 20GB which is too large to DVD disk. So I thoght that we can backup only PRIMARY filegroup, because history of changes is useless for Artur.

Unfortunately it is not simple as we wish. I tried those steps:

Perform PRIMARY filegroup backup to file.

Copy this file to Artur's computer by DVD disk.

Perform our database's Trnsaction Log on Artur's computer.

Restore PRIMARY filegroup on Artur's computer (then database is "Loading" is Enterprise Manager)

Restore backup done in step 3. Step 5. yelds error (File 'TeleDB' has been rolled forward to LSN 51...01. This log terminates at LSN 50...01, which is too early to apply the WITH RECOVERY option. Reissue the RESTORE LOG statement WITH NORECOVERY.

What is wrong?
Is it possible to move data to Artur's computer this way, without full backup and serie of log backups?
Can I use other alternative solution to move data?

Best regards,
Walter

OK.

There are several issues going on here, but the most fundamental one is that SQL 2000 does not support partial database availability. The ability to restore only the primary filegroup and bring the database online is a new feature in SQL 2005.

I believe that the reason for the LSN message you're getting is that you haven't overwritten the HISTORY filegroup in the database you're restoring to, and it has a higher LSN than the primary filegroup that you're restoring. Even if that mismatch didn't exist (if you deleted the entire database before starting the restore), you wouldn't be able to bring the database online until you restored all of the filegroups.

About the best you'll get in SQL 2000 is to back up each filegroup separately and transfer it on its own DVD, but you can't omit a filegroup and still bring the DB online.

filegroup restore problem

Hi,
We are planning to implement filegroup backup strategy for one of our big
database. We are planning to divide the database by dates so that jan data
will be in 1 filegroup and feb data in separate filegroup so basically we
will have 12 filegroups per year. As the month finish we will put the
filegroup as read only and take the filegroup backup and then later on if we
need to recover this filegroup in case of disaster we just need to restore
this filegroup backup and don’t need to apply all the log files after the
filegroup as this is read only and sql server should assume that since this
is read only it should not expect log files after this filegroup restore. But
this is not happening: when I restore the filegroup backup sql server still
force me to apply all the log files after that. But this will mean we have to
keep all the log files need for recovery ..so in fact we don’t have advantage
of putting filegroup as readonly. So any suggestions on how to avoid applying
log files or we are looking for feedback about how other people are doing
this?
Thanks
--Harvinder
Note: Already reviewed this article
http://support.microsoft.com/default...;EN-US;Q295371
Following are the steps I am testing this:
1) complete/full database backup
2) create Jan filegroup
3) populate data into Jan as well as primary filegroup
4) transaction log backup
5) put Jan as Read only
6) Jan filegroup backup
7) create Feb filegroup
8) populate data into Feb as well as primary filegroup
9) transaction log backup
10) put Feb as Read only
11) Feb filegroup backup
12) create Mar filegroup
13) populate data into Mar as well as primary filegroup
14) transaction log backup
15) put Mar as Read only
16) Mar filegroup backup
17) Create Apr filegroup
18) populate data into Apr as well as primary filegroup
19) If at this point we lost Datafile belonging to Feb filegroup I expect
only to apply backup taken at step 11) but SQL Server forced me to take the
log backup of tail and apply backups taken at step 11, 14, t-log tail backup
i.e. all the transaction log backups after filegroup backup
Consider differential backups,, perhaps on a weekly basis. This way, you
restore the filegroup, then the most-recent differential, then the remaining
logs.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Harvinder" <Harvinder@.discussions.microsoft.com> wrote in message
news:60FA9D13-D045-4C91-B224-A09EA45855BC@.microsoft.com...
Hi,
We are planning to implement filegroup backup strategy for one of our big
database. We are planning to divide the database by dates so that jan data
will be in 1 filegroup and feb data in separate filegroup so basically we
will have 12 filegroups per year. As the month finish we will put the
filegroup as read only and take the filegroup backup and then later on if we
need to recover this filegroup in case of disaster we just need to restore
this filegroup backup and don’t need to apply all the log files after the
filegroup as this is read only and sql server should assume that since this
is read only it should not expect log files after this filegroup restore.
But
this is not happening: when I restore the filegroup backup sql server still
force me to apply all the log files after that. But this will mean we have
to
keep all the log files need for recovery ..so in fact we don’t have
advantage
of putting filegroup as readonly. So any suggestions on how to avoid
applying
log files or we are looking for feedback about how other people are doing
this?
Thanks
--Harvinder
Note: Already reviewed this article
http://support.microsoft.com/default...;EN-US;Q295371
Following are the steps I am testing this:
1) complete/full database backup
2) create Jan filegroup
3) populate data into Jan as well as primary filegroup
4) transaction log backup
5) put Jan as Read only
6) Jan filegroup backup
7) create Feb filegroup
8) populate data into Feb as well as primary filegroup
9) transaction log backup
10) put Feb as Read only
11) Feb filegroup backup
12) create Mar filegroup
13) populate data into Mar as well as primary filegroup
14) transaction log backup
15) put Mar as Read only
16) Mar filegroup backup
17) Create Apr filegroup
18) populate data into Apr as well as primary filegroup
19) If at this point we lost Datafile belonging to Feb filegroup I expect
only to apply backup taken at step 11) but SQL Server forced me to take the
log backup of tail and apply backups taken at step 11, 14, t-log tail backup
i.e. all the transaction log backups after filegroup backup
|||In addition to Tom's post:
What you are asking for is a planned feature for SQL Server 2005.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Harvinder" <Harvinder@.discussions.microsoft.com> wrote in message
news:60FA9D13-D045-4C91-B224-A09EA45855BC@.microsoft.com...
> Hi,
> We are planning to implement filegroup backup strategy for one of our big
> database. We are planning to divide the database by dates so that jan data
> will be in 1 filegroup and feb data in separate filegroup so basically we
> will have 12 filegroups per year. As the month finish we will put the
> filegroup as read only and take the filegroup backup and then later on if we
> need to recover this filegroup in case of disaster we just need to restore
> this filegroup backup and don't need to apply all the log files after the
> filegroup as this is read only and sql server should assume that since this
> is read only it should not expect log files after this filegroup restore. But
> this is not happening: when I restore the filegroup backup sql server still
> force me to apply all the log files after that. But this will mean we have to
> keep all the log files need for recovery ..so in fact we don't have advantage
> of putting filegroup as readonly. So any suggestions on how to avoid applying
> log files or we are looking for feedback about how other people are doing
> this?
> Thanks
> --Harvinder
> Note: Already reviewed this article
> http://support.microsoft.com/default...;EN-US;Q295371
> Following are the steps I am testing this:
> 1) complete/full database backup
> 2) create Jan filegroup
> 3) populate data into Jan as well as primary filegroup
> 4) transaction log backup
> 5) put Jan as Read only
> 6) Jan filegroup backup
> 7) create Feb filegroup
> 8) populate data into Feb as well as primary filegroup
> 9) transaction log backup
> 10) put Feb as Read only
> 11) Feb filegroup backup
> 12) create Mar filegroup
> 13) populate data into Mar as well as primary filegroup
> 14) transaction log backup
> 15) put Mar as Read only
> 16) Mar filegroup backup
> 17) Create Apr filegroup
> 18) populate data into Apr as well as primary filegroup
> 19) If at this point we lost Datafile belonging to Feb filegroup I expect
> only to apply backup taken at step 11) but SQL Server forced me to take the
> log backup of tail and apply backups taken at step 11, 14, t-log tail backup
> i.e. all the transaction log backups after filegroup backup
>
>
|||Tibor,
You mentioned that this will be new feature in sql server 2005. I don't see
any white paper on microsoft web site regarding backup on sql server 2005.If
you get this message and if u have any information on this topic do let me
know
Thanks
--Harvinder
"Tibor Karaszi" wrote:

> In addition to Tom's post:
> What you are asking for is a planned feature for SQL Server 2005.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Harvinder" <Harvinder@.discussions.microsoft.com> wrote in message
> news:60FA9D13-D045-4C91-B224-A09EA45855BC@.microsoft.com...
>
>

filegroup restore problem

Hi,
We are planning to implement filegroup backup strategy for one of our big
database. We are planning to divide the database by dates so that jan data
will be in 1 filegroup and feb data in separate filegroup so basically we
will have 12 filegroups per year. As the month finish we will put the
filegroup as read only and take the filegroup backup and then later on if we
need to recover this filegroup in case of disaster we just need to restore
this filegroup backup and don’t need to apply all the log files after the
filegroup as this is read only and sql server should assume that since this
is read only it should not expect log files after this filegroup restore. Bu
t
this is not happening: when I restore the filegroup backup sql server still
force me to apply all the log files after that. But this will mean we have t
o
keep all the log files need for recovery ..so in fact we don’t have advant
age
of putting filegroup as readonly. So any suggestions on how to avoid applyin
g
log files or we are looking for feedback about how other people are doing
this?
Thanks
--Harvinder
Note: Already reviewed this article
http://support.microsoft.com/defaul...b;EN-US;Q295371
Following are the steps I am testing this:
1) complete/full database backup
2) create Jan filegroup
3) populate data into Jan as well as primary filegroup
4) transaction log backup
5) put Jan as Read only
6) Jan filegroup backup
7) create Feb filegroup
8) populate data into Feb as well as primary filegroup
9) transaction log backup
10) put Feb as Read only
11) Feb filegroup backup
12) create Mar filegroup
13) populate data into Mar as well as primary filegroup
14) transaction log backup
15) put Mar as Read only
16) Mar filegroup backup
17) Create Apr filegroup
18) populate data into Apr as well as primary filegroup
19) If at this point we lost Datafile belonging to Feb filegroup I expect
only to apply backup taken at step 11) but SQL Server forced me to take the
log backup of tail and apply backups taken at step 11, 14, t-log tail backup
i.e. all the transaction log backups after filegroup backupConsider differential backups,, perhaps on a weekly basis. This way, you
restore the filegroup, then the most-recent differential, then the remaining
logs.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Harvinder" <Harvinder@.discussions.microsoft.com> wrote in message
news:60FA9D13-D045-4C91-B224-A09EA45855BC@.microsoft.com...
Hi,
We are planning to implement filegroup backup strategy for one of our big
database. We are planning to divide the database by dates so that jan data
will be in 1 filegroup and feb data in separate filegroup so basically we
will have 12 filegroups per year. As the month finish we will put the
filegroup as read only and take the filegroup backup and then later on if we
need to recover this filegroup in case of disaster we just need to restore
this filegroup backup and don’t need to apply all the log files after the
filegroup as this is read only and sql server should assume that since this
is read only it should not expect log files after this filegroup restore.
But
this is not happening: when I restore the filegroup backup sql server still
force me to apply all the log files after that. But this will mean we have
to
keep all the log files need for recovery ..so in fact we don’t have
advantage
of putting filegroup as readonly. So any suggestions on how to avoid
applying
log files or we are looking for feedback about how other people are doing
this?
Thanks
--Harvinder
Note: Already reviewed this article
http://support.microsoft.com/defaul...b;EN-US;Q295371
Following are the steps I am testing this:
1) complete/full database backup
2) create Jan filegroup
3) populate data into Jan as well as primary filegroup
4) transaction log backup
5) put Jan as Read only
6) Jan filegroup backup
7) create Feb filegroup
8) populate data into Feb as well as primary filegroup
9) transaction log backup
10) put Feb as Read only
11) Feb filegroup backup
12) create Mar filegroup
13) populate data into Mar as well as primary filegroup
14) transaction log backup
15) put Mar as Read only
16) Mar filegroup backup
17) Create Apr filegroup
18) populate data into Apr as well as primary filegroup
19) If at this point we lost Datafile belonging to Feb filegroup I expect
only to apply backup taken at step 11) but SQL Server forced me to take the
log backup of tail and apply backups taken at step 11, 14, t-log tail backup
i.e. all the transaction log backups after filegroup backup|||In addition to Tom's post:
What you are asking for is a planned feature for SQL Server 2005.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Harvinder" <Harvinder@.discussions.microsoft.com> wrote in message
news:60FA9D13-D045-4C91-B224-A09EA45855BC@.microsoft.com...
> Hi,
> We are planning to implement filegroup backup strategy for one of our big
> database. We are planning to divide the database by dates so that jan data
> will be in 1 filegroup and feb data in separate filegroup so basically we
> will have 12 filegroups per year. As the month finish we will put the
> filegroup as read only and take the filegroup backup and then later on if
we
> need to recover this filegroup in case of disaster we just need to restore
> this filegroup backup and don't need to apply all the log files after the
> filegroup as this is read only and sql server should assume that since thi
s
> is read only it should not expect log files after this filegroup restore.
But
> this is not happening: when I restore the filegroup backup sql server stil
l
> force me to apply all the log files after that. But this will mean we have
to
> keep all the log files need for recovery ..so in fact we don't have advant
age
> of putting filegroup as readonly. So any suggestions on how to avoid apply
ing
> log files or we are looking for feedback about how other people are doing
> this?
> Thanks
> --Harvinder
> Note: Already reviewed this article
> http://support.microsoft.com/defaul...b;EN-US;Q295371
> Following are the steps I am testing this:
> 1) complete/full database backup
> 2) create Jan filegroup
> 3) populate data into Jan as well as primary filegroup
> 4) transaction log backup
> 5) put Jan as Read only
> 6) Jan filegroup backup
> 7) create Feb filegroup
> 8) populate data into Feb as well as primary filegroup
> 9) transaction log backup
> 10) put Feb as Read only
> 11) Feb filegroup backup
> 12) create Mar filegroup
> 13) populate data into Mar as well as primary filegroup
> 14) transaction log backup
> 15) put Mar as Read only
> 16) Mar filegroup backup
> 17) Create Apr filegroup
> 18) populate data into Apr as well as primary filegroup
> 19) If at this point we lost Datafile belonging to Feb filegroup I expec
t
> only to apply backup taken at step 11) but SQL Server forced me to take th
e
> log backup of tail and apply backups taken at step 11, 14, t-log tail back
up
> i.e. all the transaction log backups after filegroup backup
>
>|||Tibor,
You mentioned that this will be new feature in sql server 2005. I don't see
any white paper on microsoft web site regarding backup on sql server 2005.If
you get this message and if u have any information on this topic do let me
know
Thanks
--Harvinder
"Tibor Karaszi" wrote:

> In addition to Tom's post:
> What you are asking for is a planned feature for SQL Server 2005.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Harvinder" <Harvinder@.discussions.microsoft.com> wrote in message
> news:60FA9D13-D045-4C91-B224-A09EA45855BC@.microsoft.com...
>
>

filegroup restore problem

Hi,
We are planning to implement filegroup backup strategy for one of our big
database. We are planning to divide the database by dates so that jan data
will be in 1 filegroup and feb data in separate filegroup so basically we
will have 12 filegroups per year. As the month finish we will put the
filegroup as read only and take the filegroup backup and then later on if we
need to recover this filegroup in case of disaster we just need to restore
this filegroup backup and donâ't need to apply all the log files after the
filegroup as this is read only and sql server should assume that since this
is read only it should not expect log files after this filegroup restore. But
this is not happening: when I restore the filegroup backup sql server still
force me to apply all the log files after that. But this will mean we have to
keep all the log files need for recovery ..so in fact we donâ't have advantage
of putting filegroup as readonly. So any suggestions on how to avoid applying
log files or we are looking for feedback about how other people are doing
this?
Thanks
--Harvinder
Note: Already reviewed this article
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q295371
Following are the steps I am testing this:
1) complete/full database backup
2) create Jan filegroup
3) populate data into Jan as well as primary filegroup
4) transaction log backup
5) put Jan as Read only
6) Jan filegroup backup
7) create Feb filegroup
8) populate data into Feb as well as primary filegroup
9) transaction log backup
10) put Feb as Read only
11) Feb filegroup backup
12) create Mar filegroup
13) populate data into Mar as well as primary filegroup
14) transaction log backup
15) put Mar as Read only
16) Mar filegroup backup
17) Create Apr filegroup
18) populate data into Apr as well as primary filegroup
19) If at this point we lost Datafile belonging to Feb filegroup I expect
only to apply backup taken at step 11) but SQL Server forced me to take the
log backup of tail and apply backups taken at step 11, 14, t-log tail backup
i.e. all the transaction log backups after filegroup backupConsider differential backups,, perhaps on a weekly basis. This way, you
restore the filegroup, then the most-recent differential, then the remaining
logs.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Harvinder" <Harvinder@.discussions.microsoft.com> wrote in message
news:60FA9D13-D045-4C91-B224-A09EA45855BC@.microsoft.com...
Hi,
We are planning to implement filegroup backup strategy for one of our big
database. We are planning to divide the database by dates so that jan data
will be in 1 filegroup and feb data in separate filegroup so basically we
will have 12 filegroups per year. As the month finish we will put the
filegroup as read only and take the filegroup backup and then later on if we
need to recover this filegroup in case of disaster we just need to restore
this filegroup backup and donâ't need to apply all the log files after the
filegroup as this is read only and sql server should assume that since this
is read only it should not expect log files after this filegroup restore.
But
this is not happening: when I restore the filegroup backup sql server still
force me to apply all the log files after that. But this will mean we have
to
keep all the log files need for recovery ..so in fact we donâ't have
advantage
of putting filegroup as readonly. So any suggestions on how to avoid
applying
log files or we are looking for feedback about how other people are doing
this?
Thanks
--Harvinder
Note: Already reviewed this article
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q295371
Following are the steps I am testing this:
1) complete/full database backup
2) create Jan filegroup
3) populate data into Jan as well as primary filegroup
4) transaction log backup
5) put Jan as Read only
6) Jan filegroup backup
7) create Feb filegroup
8) populate data into Feb as well as primary filegroup
9) transaction log backup
10) put Feb as Read only
11) Feb filegroup backup
12) create Mar filegroup
13) populate data into Mar as well as primary filegroup
14) transaction log backup
15) put Mar as Read only
16) Mar filegroup backup
17) Create Apr filegroup
18) populate data into Apr as well as primary filegroup
19) If at this point we lost Datafile belonging to Feb filegroup I expect
only to apply backup taken at step 11) but SQL Server forced me to take the
log backup of tail and apply backups taken at step 11, 14, t-log tail backup
i.e. all the transaction log backups after filegroup backup|||In addition to Tom's post:
What you are asking for is a planned feature for SQL Server 2005.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Harvinder" <Harvinder@.discussions.microsoft.com> wrote in message
news:60FA9D13-D045-4C91-B224-A09EA45855BC@.microsoft.com...
> Hi,
> We are planning to implement filegroup backup strategy for one of our big
> database. We are planning to divide the database by dates so that jan data
> will be in 1 filegroup and feb data in separate filegroup so basically we
> will have 12 filegroups per year. As the month finish we will put the
> filegroup as read only and take the filegroup backup and then later on if we
> need to recover this filegroup in case of disaster we just need to restore
> this filegroup backup and don't need to apply all the log files after the
> filegroup as this is read only and sql server should assume that since this
> is read only it should not expect log files after this filegroup restore. But
> this is not happening: when I restore the filegroup backup sql server still
> force me to apply all the log files after that. But this will mean we have to
> keep all the log files need for recovery ..so in fact we don't have advantage
> of putting filegroup as readonly. So any suggestions on how to avoid applying
> log files or we are looking for feedback about how other people are doing
> this?
> Thanks
> --Harvinder
> Note: Already reviewed this article
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q295371
> Following are the steps I am testing this:
> 1) complete/full database backup
> 2) create Jan filegroup
> 3) populate data into Jan as well as primary filegroup
> 4) transaction log backup
> 5) put Jan as Read only
> 6) Jan filegroup backup
> 7) create Feb filegroup
> 8) populate data into Feb as well as primary filegroup
> 9) transaction log backup
> 10) put Feb as Read only
> 11) Feb filegroup backup
> 12) create Mar filegroup
> 13) populate data into Mar as well as primary filegroup
> 14) transaction log backup
> 15) put Mar as Read only
> 16) Mar filegroup backup
> 17) Create Apr filegroup
> 18) populate data into Apr as well as primary filegroup
> 19) If at this point we lost Datafile belonging to Feb filegroup I expect
> only to apply backup taken at step 11) but SQL Server forced me to take the
> log backup of tail and apply backups taken at step 11, 14, t-log tail backup
> i.e. all the transaction log backups after filegroup backup
>
>|||Tibor,
You mentioned that this will be new feature in sql server 2005. I don't see
any white paper on microsoft web site regarding backup on sql server 2005.If
you get this message and if u have any information on this topic do let me
know
Thanks
--Harvinder
"Tibor Karaszi" wrote:
> In addition to Tom's post:
> What you are asking for is a planned feature for SQL Server 2005.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Harvinder" <Harvinder@.discussions.microsoft.com> wrote in message
> news:60FA9D13-D045-4C91-B224-A09EA45855BC@.microsoft.com...
> > Hi,
> >
> > We are planning to implement filegroup backup strategy for one of our big
> > database. We are planning to divide the database by dates so that jan data
> > will be in 1 filegroup and feb data in separate filegroup so basically we
> > will have 12 filegroups per year. As the month finish we will put the
> > filegroup as read only and take the filegroup backup and then later on if we
> > need to recover this filegroup in case of disaster we just need to restore
> > this filegroup backup and don't need to apply all the log files after the
> > filegroup as this is read only and sql server should assume that since this
> > is read only it should not expect log files after this filegroup restore. But
> > this is not happening: when I restore the filegroup backup sql server still
> > force me to apply all the log files after that. But this will mean we have to
> > keep all the log files need for recovery ..so in fact we don't have advantage
> > of putting filegroup as readonly. So any suggestions on how to avoid applying
> > log files or we are looking for feedback about how other people are doing
> > this?
> >
> > Thanks
> > --Harvinder
> > Note: Already reviewed this article
> > http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q295371
> > Following are the steps I am testing this:
> > 1) complete/full database backup
> > 2) create Jan filegroup
> > 3) populate data into Jan as well as primary filegroup
> > 4) transaction log backup
> > 5) put Jan as Read only
> > 6) Jan filegroup backup
> > 7) create Feb filegroup
> > 8) populate data into Feb as well as primary filegroup
> > 9) transaction log backup
> > 10) put Feb as Read only
> > 11) Feb filegroup backup
> > 12) create Mar filegroup
> > 13) populate data into Mar as well as primary filegroup
> > 14) transaction log backup
> > 15) put Mar as Read only
> > 16) Mar filegroup backup
> > 17) Create Apr filegroup
> > 18) populate data into Apr as well as primary filegroup
> > 19) If at this point we lost Datafile belonging to Feb filegroup I expect
> > only to apply backup taken at step 11) but SQL Server forced me to take the
> > log backup of tail and apply backups taken at step 11, 14, t-log tail backup
> > i.e. all the transaction log backups after filegroup backup
> >
> >
> >
>
>

Filegroup restore Issue

I restored a file group of a database. The restore came back saying success. But when I looked at the state of that particular filegroup using select * from Sys.database_Files it says "restoring" any thoughts on this? I restored that file group with full recovery did I do any thing special for a file group restore?

with smiles
santhoshDoes any one face this issue yet? Any resolution.|||

Hi Santosh,

You need to perform recovery using transaction log backups taken after the backup you used to restore the filegroup and then finally recover the last log backup taken using No_truncate.

I was wondering if you did a backup log with no_truncate before restoring the filegroup.

regards

Jag

|||Hi Jag,
oh I didnt try with no_truncate thing. what If I dont have a transaction log and I didnt get a chance to take the tail log backup. Wont the filegroup restore work? Thanks for your reply Jag.|||

Hi Santosh,

You need to do a tail-log back up and recover the database using the all the log backups (including tail backup in the end.) taken after database backup, that used for restoring the filegroup.

This is because you are performing partial restore and for this to you need to perform complete recovery.

if the complete recovery is not performed the LSNs of restored filegroup are different from rest of the database.

regards

Jag

Filegroup restore Issue

I restored a file group of a database. The restore came back saying success. But when I looked at the state of that particular filegroup using select * from Sys.database_Files it says "restoring" any thoughts on this? I restored that file group with full recovery did I do any thing special for a file group restore?

with smiles
santhoshDoes any one face this issue yet? Any resolution.|||

Hi Santosh,

You need to perform recovery using transaction log backups taken after the backup you used to restore the filegroup and then finally recover the last log backup taken using No_truncate.

I was wondering if you did a backup log with no_truncate before restoring the filegroup.

regards

Jag

|||Hi Jag,
oh I didnt try with no_truncate thing. what If I dont have a transaction log and I didnt get a chance to take the tail log backup. Wont the filegroup restore work? Thanks for your reply Jag.|||

Hi Santosh,

You need to do a tail-log back up and recover the database using the all the log backups (including tail backup in the end.) taken after database backup, that used for restoring the filegroup.

This is because you are performing partial restore and for this to you need to perform complete recovery.

if the complete recovery is not performed the LSNs of restored filegroup are different from rest of the database.

regards

Jag

Filegroup restore from full backup

Hello!
I'm trying to figure out how some things about filegroup restore work.
I have a primary filegroup that is very small (2 MB)
and another filegroup that is rather large (2 GB).
The transaction log if very small (1 MB).
The full backup is about 2 GB.
I'm doing a filegroup restore of only the primary filegroup from the full
backup.
RESTORE DATABASE Test FILEGROUP = 'PRIMARY' FROM DISK = 'C:\Test.bak'
If would expect this to be very fast, but it's not. Could it be that SQL
Server is reading the complete backup file and not only the filegroup that is
needed?
I'm using SQL Server 2005 SP2.
Best regards
Ola Hallengren
Thanks, Tibor. I understand.
I'm thinking about using it in a human data error (a record deleted)
scenario. If you have a really large database and a good filegroup strategy
this feature would be very useful.
Does it work the same way in SQL Server 2008?
/Ola
"Tibor Karaszi" wrote:

> Hej Ola,
> To the best of my knowledge, SQL Server do not have any type of allocation structure in the
> beginning of the backup with which it know where pages from some particular file exist. I.e., it
> will have to read the backup file from beginning to end and for each extent see what page it belongs
> in order to determine whether to write the extent to the database file or not.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
> news:C1A1452A-2361-49D0-8BF9-05D9A5B9392B@.microsoft.com...
>
>
|||The best way to plan for restoring at the file or filegroup level is to
never place any user objects in the primary filegroup. That is because to
restore any file or filegroup you must always restore the primary filegroup
first and keeping only the system objects will speed this dramatically. Then
place user objects in separate secondary filegroups based on their usage
within the schema. Then you can do individual file or filegroup backups so
that you don't have to read an entire full backup each time. For more
details I suggest you read up on Piecemeal Restores in BooksOnLine.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
news:759D0E49-7C55-4DEC-8EA1-8D39C827DF3A@.microsoft.com...[vbcol=seagreen]
> Thanks, Tibor. I understand.
> I'm thinking about using it in a human data error (a record deleted)
> scenario. If you have a really large database and a good filegroup
> strategy
> this feature would be very useful.
> Does it work the same way in SQL Server 2008?
> /Ola
>
> "Tibor Karaszi" wrote:
|||I still think that it would be smart if it was possible to restore filegroups
from a full backup without having to read the entire backup file. (And yes it
is a good practise to only have system objects in the Primary filegroup.)
Thanks.
/Ola
"Andrew J. Kelly" wrote:

> The best way to plan for restoring at the file or filegroup level is to
> never place any user objects in the primary filegroup. That is because to
> restore any file or filegroup you must always restore the primary filegroup
> first and keeping only the system objects will speed this dramatically. Then
> place user objects in separate secondary filegroups based on their usage
> within the schema. Then you can do individual file or filegroup backups so
> that you don't have to read an entire full backup each time. For more
> details I suggest you read up on Piecemeal Restores in BooksOnLine.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
> news:759D0E49-7C55-4DEC-8EA1-8D39C827DF3A@.microsoft.com...
>

Filegroup restore from full backup

Hello!
I'm trying to figure out how some things about filegroup restore work.
I have a primary filegroup that is very small (2 MB)
and another filegroup that is rather large (2 GB).
The transaction log if very small (1 MB).
The full backup is about 2 GB.
I'm doing a filegroup restore of only the primary filegroup from the full
backup.
RESTORE DATABASE Test FILEGROUP = 'PRIMARY' FROM DISK = 'C:\Test.bak'
If would expect this to be very fast, but it's not. Could it be that SQL
Server is reading the complete backup file and not only the filegroup that is
needed?
I'm using SQL Server 2005 SP2.
Best regards
Ola HallengrenHej Ola,
To the best of my knowledge, SQL Server do not have any type of allocation structure in the
beginning of the backup with which it know where pages from some particular file exist. I.e., it
will have to read the backup file from beginning to end and for each extent see what page it belongs
in order to determine whether to write the extent to the database file or not.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
news:C1A1452A-2361-49D0-8BF9-05D9A5B9392B@.microsoft.com...
> Hello!
> I'm trying to figure out how some things about filegroup restore work.
> I have a primary filegroup that is very small (2 MB)
> and another filegroup that is rather large (2 GB).
> The transaction log if very small (1 MB).
> The full backup is about 2 GB.
> I'm doing a filegroup restore of only the primary filegroup from the full
> backup.
> RESTORE DATABASE Test FILEGROUP = 'PRIMARY' FROM DISK = 'C:\Test.bak'
> If would expect this to be very fast, but it's not. Could it be that SQL
> Server is reading the complete backup file and not only the filegroup that is
> needed?
> I'm using SQL Server 2005 SP2.
> Best regards
> Ola Hallengren|||Thanks, Tibor. I understand.
I'm thinking about using it in a human data error (a record deleted)
scenario. If you have a really large database and a good filegroup strategy
this feature would be very useful.
Does it work the same way in SQL Server 2008?
/Ola
"Tibor Karaszi" wrote:
> Hej Ola,
> To the best of my knowledge, SQL Server do not have any type of allocation structure in the
> beginning of the backup with which it know where pages from some particular file exist. I.e., it
> will have to read the backup file from beginning to end and for each extent see what page it belongs
> in order to determine whether to write the extent to the database file or not.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
> news:C1A1452A-2361-49D0-8BF9-05D9A5B9392B@.microsoft.com...
> > Hello!
> >
> > I'm trying to figure out how some things about filegroup restore work.
> >
> > I have a primary filegroup that is very small (2 MB)
> > and another filegroup that is rather large (2 GB).
> > The transaction log if very small (1 MB).
> >
> > The full backup is about 2 GB.
> >
> > I'm doing a filegroup restore of only the primary filegroup from the full
> > backup.
> >
> > RESTORE DATABASE Test FILEGROUP = 'PRIMARY' FROM DISK = 'C:\Test.bak'
> >
> > If would expect this to be very fast, but it's not. Could it be that SQL
> > Server is reading the complete backup file and not only the filegroup that is
> > needed?
> >
> > I'm using SQL Server 2005 SP2.
> >
> > Best regards
> >
> > Ola Hallengren
>
>|||The best way to plan for restoring at the file or filegroup level is to
never place any user objects in the primary filegroup. That is because to
restore any file or filegroup you must always restore the primary filegroup
first and keeping only the system objects will speed this dramatically. Then
place user objects in separate secondary filegroups based on their usage
within the schema. Then you can do individual file or filegroup backups so
that you don't have to read an entire full backup each time. For more
details I suggest you read up on Piecemeal Restores in BooksOnLine.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
news:759D0E49-7C55-4DEC-8EA1-8D39C827DF3A@.microsoft.com...
> Thanks, Tibor. I understand.
> I'm thinking about using it in a human data error (a record deleted)
> scenario. If you have a really large database and a good filegroup
> strategy
> this feature would be very useful.
> Does it work the same way in SQL Server 2008?
> /Ola
>
> "Tibor Karaszi" wrote:
>> Hej Ola,
>> To the best of my knowledge, SQL Server do not have any type of
>> allocation structure in the
>> beginning of the backup with which it know where pages from some
>> particular file exist. I.e., it
>> will have to read the backup file from beginning to end and for each
>> extent see what page it belongs
>> in order to determine whether to write the extent to the database file or
>> not.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in
>> message
>> news:C1A1452A-2361-49D0-8BF9-05D9A5B9392B@.microsoft.com...
>> > Hello!
>> >
>> > I'm trying to figure out how some things about filegroup restore work.
>> >
>> > I have a primary filegroup that is very small (2 MB)
>> > and another filegroup that is rather large (2 GB).
>> > The transaction log if very small (1 MB).
>> >
>> > The full backup is about 2 GB.
>> >
>> > I'm doing a filegroup restore of only the primary filegroup from the
>> > full
>> > backup.
>> >
>> > RESTORE DATABASE Test FILEGROUP = 'PRIMARY' FROM DISK = 'C:\Test.bak'
>> >
>> > If would expect this to be very fast, but it's not. Could it be that
>> > SQL
>> > Server is reading the complete backup file and not only the filegroup
>> > that is
>> > needed?
>> >
>> > I'm using SQL Server 2005 SP2.
>> >
>> > Best regards
>> >
>> > Ola Hallengren
>>|||I still think that it would be smart if it was possible to restore filegroups
from a full backup without having to read the entire backup file. (And yes it
is a good practise to only have system objects in the Primary filegroup.)
Thanks.
/Ola
"Andrew J. Kelly" wrote:
> The best way to plan for restoring at the file or filegroup level is to
> never place any user objects in the primary filegroup. That is because to
> restore any file or filegroup you must always restore the primary filegroup
> first and keeping only the system objects will speed this dramatically. Then
> place user objects in separate secondary filegroups based on their usage
> within the schema. Then you can do individual file or filegroup backups so
> that you don't have to read an entire full backup each time. For more
> details I suggest you read up on Piecemeal Restores in BooksOnLine.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
> news:759D0E49-7C55-4DEC-8EA1-8D39C827DF3A@.microsoft.com...
> > Thanks, Tibor. I understand.
> >
> > I'm thinking about using it in a human data error (a record deleted)
> > scenario. If you have a really large database and a good filegroup
> > strategy
> > this feature would be very useful.
> >
> > Does it work the same way in SQL Server 2008?
> >
> > /Ola
> >
> >
> >
> > "Tibor Karaszi" wrote:
> >
> >> Hej Ola,
> >>
> >> To the best of my knowledge, SQL Server do not have any type of
> >> allocation structure in the
> >> beginning of the backup with which it know where pages from some
> >> particular file exist. I.e., it
> >> will have to read the backup file from beginning to end and for each
> >> extent see what page it belongs
> >> in order to determine whether to write the extent to the database file or
> >> not.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in
> >> message
> >> news:C1A1452A-2361-49D0-8BF9-05D9A5B9392B@.microsoft.com...
> >> > Hello!
> >> >
> >> > I'm trying to figure out how some things about filegroup restore work.
> >> >
> >> > I have a primary filegroup that is very small (2 MB)
> >> > and another filegroup that is rather large (2 GB).
> >> > The transaction log if very small (1 MB).
> >> >
> >> > The full backup is about 2 GB.
> >> >
> >> > I'm doing a filegroup restore of only the primary filegroup from the
> >> > full
> >> > backup.
> >> >
> >> > RESTORE DATABASE Test FILEGROUP = 'PRIMARY' FROM DISK = 'C:\Test.bak'
> >> >
> >> > If would expect this to be very fast, but it's not. Could it be that
> >> > SQL
> >> > Server is reading the complete backup file and not only the filegroup
> >> > that is
> >> > needed?
> >> >
> >> > I'm using SQL Server 2005 SP2.
> >> >
> >> > Best regards
> >> >
> >> > Ola Hallengren
> >>
> >>
> >>
>|||In addition to Andrew's reply:
> Does it work the same way in SQL Server 2008?
AFAIK, yes. I haven't seen or heard about this type of architectural changes for backup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
news:759D0E49-7C55-4DEC-8EA1-8D39C827DF3A@.microsoft.com...
> Thanks, Tibor. I understand.
> I'm thinking about using it in a human data error (a record deleted)
> scenario. If you have a really large database and a good filegroup strategy
> this feature would be very useful.
> Does it work the same way in SQL Server 2008?
> /Ola
>
> "Tibor Karaszi" wrote:
>> Hej Ola,
>> To the best of my knowledge, SQL Server do not have any type of allocation structure in the
>> beginning of the backup with which it know where pages from some particular file exist. I.e., it
>> will have to read the backup file from beginning to end and for each extent see what page it
>> belongs
>> in order to determine whether to write the extent to the database file or not.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
>> news:C1A1452A-2361-49D0-8BF9-05D9A5B9392B@.microsoft.com...
>> > Hello!
>> >
>> > I'm trying to figure out how some things about filegroup restore work.
>> >
>> > I have a primary filegroup that is very small (2 MB)
>> > and another filegroup that is rather large (2 GB).
>> > The transaction log if very small (1 MB).
>> >
>> > The full backup is about 2 GB.
>> >
>> > I'm doing a filegroup restore of only the primary filegroup from the full
>> > backup.
>> >
>> > RESTORE DATABASE Test FILEGROUP = 'PRIMARY' FROM DISK = 'C:\Test.bak'
>> >
>> > If would expect this to be very fast, but it's not. Could it be that SQL
>> > Server is reading the complete backup file and not only the filegroup that is
>> > needed?
>> >
>> > I'm using SQL Server 2005 SP2.
>> >
>> > Best regards
>> >
>> > Ola Hallengren
>>

Filegroup Restore

I need to start using filegroups, because i have a very large Database.
I want to use the primary for the system tables and to have on filegroup by
year.
Example:
Year 2002 tables goes to filegroup 2002
Year 2003 tables goes to filegroup 2003
Year 2004 tables goes to filegroup 2004
If we acidently delete records on the 2004 tables,
is posssible to only restore the filegroup 2004 and the last Log backup?It doesn't work that way. If you deleted the rows in that table, and have committed, filegroup
backup will not help you. This is because a filegroup restore work in the way that you restore the
filegroup. Then all subsequent log backups until now. And you have already committed the delete...
However, you can restore an fg from a full backup into a new database (the desired user fg along
with the PRIMARY fg). You do this using the PARTIAL option of the RESTORE command. Then you can copy
the desired data to your production database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"José Paulo Coelho" <JosPauloCoelho@.discussions.microsoft.com> wrote in message
news:09FE6709-6219-4B81-AFA5-AF4D54BC1C14@.microsoft.com...
>I need to start using filegroups, because i have a very large Database.
> I want to use the primary for the system tables and to have on filegroup by
> year.
> Example:
> Year 2002 tables goes to filegroup 2002
> Year 2003 tables goes to filegroup 2003
> Year 2004 tables goes to filegroup 2004
> If we acidently delete records on the 2004 tables,
> is posssible to only restore the filegroup 2004 and the last Log backup?
>|||Thanks for your reply.
I understant that i can't only restore 2004 fg.
My idea now is to have a backup of 2002 and 2003 on tape on a safeplace.
And during the Year, i will do backups of the 2004 fg and the log.
Then if in the middle of the year, i acidentely delete some records.
I will restore 2002, 2003 fg and the last 2004 and the last log.
This will work, right?|||Please re-read my earlier reply. You cannot go back in time for a part of the database using
filegroup backup/restore.IMO, your most viable option is what I mentioned earlier and the PARTIAL
option of the RESTORE command.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"José Paulo Coelho" <JosPauloCoelho@.discussions.microsoft.com> wrote in message
news:94F4FCAC-3E42-4391-9AD7-B9BA7F79896D@.microsoft.com...
> Thanks for your reply.
> I understant that i can't only restore 2004 fg.
> My idea now is to have a backup of 2002 and 2003 on tape on a safeplace.
> And during the Year, i will do backups of the 2004 fg and the log.
> Then if in the middle of the year, i acidentely delete some records.
> I will restore 2002, 2003 fg and the last 2004 and the last log.
> This will work, right?
>|||Thanks again for your reply, I'm on my own on this.
There is no clear documentation about this
But i have tested and it seams that it works.
Can you check this.
Here is the script:
USE MASTER
GO
CREATE DATABASE SALES
GO
ALTER DATABASE SALES ADD FILEGROUP F2002
ALTER DATABASE SALES ADD FILEGROUP F2003
ALTER DATABASE SALES ADD FILEGROUP F2004
GO
ALTER DATABASE SALES ADD FILE
(NAME='2002',
FILENAME='c:\2002.dat1')
TO FILEGROUP F2002
go
ALTER DATABASE SALES ADD FILE
(NAME='2003',
FILENAME='c:\2003.dat1')
TO FILEGROUP F2003
go
ALTER DATABASE SALES ADD FILE
(NAME='2004',
FILENAME='c:\2004.dat1')
TO FILEGROUP F2004
go
use SALES
CREATE TABLE T_2002 (id int) ON F2002
CREATE TABLE T_2003 (id int) ON F2003
CREATE TABLE T_2004 (id int) ON F2004
INSERT INTO T_2002 VALUES (1)
INSERT INTO T_2003 VALUES (1)
INSERT INTO T_2004 VALUES (1)
GO
--
--BACKUP FOR TAPES--
--
USE master
BACKUP DATABASE SALES
FILE = 'Sales',
FILEGROUP = 'PRIMARY'
TO disk ='C:\a\Sales_PRIMARY.bak'
BACKUP DATABASE SALES
FILE = '2002',
FILEGROUP = 'F2002'
TO disk ='C:\a\Sales_F2002.bak'
BACKUP DATABASE SALES
FILE = '2003',
FILEGROUP = 'F2003'
TO disk ='C:\a\Sales_F2003.bak'
--
--RECORDS OK--
--
INSERT INTO T_2004 VALUES (2)
INSERT INTO T_2004 VALUES (3)
INSERT INTO T_2004 VALUES (4)
BACKUP DATABASE SALES
FILE = '2004',
FILEGROUP = 'F2004'
TO disk ='C:\a\Sales_F2004.bak'
BACKUP LOG SALES TO disk = 'C:\a\Sales_log.log'
GO
--
--Acidental Delete--
--
use Sales
delete from T_2004
--TAIL LOG BACKUP
BACKUP LOG SALES TO disk = 'C:\a\Sales_log2.log' WITH NO_TRUNCATE
---
---
---
--MUST DO ALL THE RESTORE--
---
USE master
RESTORE DATABASE SALES
FILE = 'Sales',
FILEGROUP = 'PRIMARY'
FROM DISK = 'C:\a\Sales_PRIMARY.bak'
WITH noRECOVERY
RESTORE DATABASE SALES
FILE = '2002',
FILEGROUP = 'F2002'
FROM DISK = 'C:\a\Sales_F2002.bak'
WITH noRECOVERY
RESTORE DATABASE SALES
FILE = '2003',
FILEGROUP = 'F2003'
FROM DISK = 'C:\a\Sales_F2003.bak'
WITH noRECOVERY
RESTORE DATABASE SALES
FILE = '2004',
FILEGROUP = 'F2004'
FROM DISK = 'C:\a\Sales_F2004.bak'
WITH noRECOVERY
RESTORE LOG SALES FROM disk = 'C:\a\Sales_log.log' WITH RECOVERY|||Yes, your script work, but you did indeed restore all parts of the database up to the point in time
prior to the accidental DELETE. I thought that you wanted to restore only a part of the database,
the part where you did the accidental delete? Perhaps I misunderstood you.
I've modified your script slightly below, so you can run it several times without needing to clean
up. Please continue on my modified script if you want to elaborate further. :-)
drop database sales
go
USE MASTER
GO
CREATE DATABASE SALES
GO
ALTER DATABASE SALES ADD FILEGROUP F2002
ALTER DATABASE SALES ADD FILEGROUP F2003
ALTER DATABASE SALES ADD FILEGROUP F2004
GO
ALTER DATABASE SALES ADD FILE
(NAME='2002',
FILENAME='c:\2002.dat1')
TO FILEGROUP F2002
go
ALTER DATABASE SALES ADD FILE
(NAME='2003',
FILENAME='c:\2003.dat1')
TO FILEGROUP F2003
go
ALTER DATABASE SALES ADD FILE
(NAME='2004',
FILENAME='c:\2004.dat1')
TO FILEGROUP F2004
go
CREATE TABLE Sales..T_2002 (id int) ON F2002
CREATE TABLE Sales..T_2003 (id int) ON F2003
CREATE TABLE Sales..T_2004 (id int) ON F2004
INSERT INTO Sales..T_2002 VALUES (1)
INSERT INTO Sales..T_2003 VALUES (1)
INSERT INTO Sales..T_2004 VALUES (1)
GO
--
--BACKUP FOR TAPES--
--
BACKUP DATABASE SALES
FILE = 'Sales',
FILEGROUP = 'PRIMARY'
TO disk ='C:\a\Sales_PRIMARY.bak'
WITH INIT
BACKUP DATABASE SALES
FILE = '2002',
FILEGROUP = 'F2002'
TO disk ='C:\a\Sales_F2002.bak'
WITH INIT
BACKUP DATABASE SALES
FILE = '2003',
FILEGROUP = 'F2003'
TO disk ='C:\a\Sales_F2003.bak'
WITH INIT
--
--RECORDS OK--
--
INSERT INTO sales..T_2004 VALUES (2)
INSERT INTO sales..T_2004 VALUES (3)
INSERT INTO sales..T_2004 VALUES (4)
select * from sales..T_2004
BACKUP DATABASE SALES
FILE = '2004',
FILEGROUP = 'F2004'
TO disk ='C:\a\Sales_F2004.bak'
WITH INIT
BACKUP LOG SALES TO disk = 'C:\a\Sales_log.log' WITH INIT
GO
--
--Acidental Delete--
--
delete from sales..T_2004
INSERT INTO sales..T_2003 VALUES (2)
--TAIL LOG BACKUP
BACKUP LOG SALES TO disk = 'C:\a\Sales_log2.log' WITH NO_TRUNCATE
---
---
---
--MUST DO ALL THE RESTORE--
---
RESTORE DATABASE SALES
FILE = 'Sales',
FILEGROUP = 'PRIMARY'
FROM DISK = 'C:\a\Sales_PRIMARY.bak'
WITH noRECOVERY
RESTORE DATABASE SALES
FILE = '2002',
FILEGROUP = 'F2002'
FROM DISK = 'C:\a\Sales_F2002.bak'
WITH noRECOVERY
RESTORE DATABASE SALES
FILE = '2003',
FILEGROUP = 'F2003'
FROM DISK = 'C:\a\Sales_F2003.bak'
WITH noRECOVERY
RESTORE DATABASE SALES
FILE = '2004',
FILEGROUP = 'F2004'
FROM DISK = 'C:\a\Sales_F2004.bak'
WITH noRECOVERY
RESTORE LOG SALES FROM disk = 'C:\a\Sales_log.log' WITH RECOVERY
SELECT * FROM sales..T_2004
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"José Paulo Coelho" <JosPauloCoelho@.discussions.microsoft.com> wrote in message
news:16AC95DA-C148-4CF9-A338-D9C2F11F0B8B@.microsoft.com...
> Thanks again for your reply, I'm on my own on this.
> There is no clear documentation about this
> But i have tested and it seams that it works.
> Can you check this.
> Here is the script:
> USE MASTER
> GO
> CREATE DATABASE SALES
> GO
> ALTER DATABASE SALES ADD FILEGROUP F2002
> ALTER DATABASE SALES ADD FILEGROUP F2003
> ALTER DATABASE SALES ADD FILEGROUP F2004
> GO
> ALTER DATABASE SALES ADD FILE
> (NAME='2002',
> FILENAME='c:\2002.dat1')
> TO FILEGROUP F2002
> go
> ALTER DATABASE SALES ADD FILE
> (NAME='2003',
> FILENAME='c:\2003.dat1')
> TO FILEGROUP F2003
> go
> ALTER DATABASE SALES ADD FILE
> (NAME='2004',
> FILENAME='c:\2004.dat1')
> TO FILEGROUP F2004
> go
> use SALES
> CREATE TABLE T_2002 (id int) ON F2002
> CREATE TABLE T_2003 (id int) ON F2003
> CREATE TABLE T_2004 (id int) ON F2004
> INSERT INTO T_2002 VALUES (1)
> INSERT INTO T_2003 VALUES (1)
> INSERT INTO T_2004 VALUES (1)
> GO
> --
> --BACKUP FOR TAPES--
> --
> USE master
> BACKUP DATABASE SALES
> FILE = 'Sales',
> FILEGROUP = 'PRIMARY'
> TO disk ='C:\a\Sales_PRIMARY.bak'
> BACKUP DATABASE SALES
> FILE = '2002',
> FILEGROUP = 'F2002'
> TO disk ='C:\a\Sales_F2002.bak'
> BACKUP DATABASE SALES
> FILE = '2003',
> FILEGROUP = 'F2003'
> TO disk ='C:\a\Sales_F2003.bak'
> --
> --RECORDS OK--
> --
> INSERT INTO T_2004 VALUES (2)
> INSERT INTO T_2004 VALUES (3)
> INSERT INTO T_2004 VALUES (4)
> BACKUP DATABASE SALES
> FILE = '2004',
> FILEGROUP = 'F2004'
> TO disk ='C:\a\Sales_F2004.bak'
> BACKUP LOG SALES TO disk = 'C:\a\Sales_log.log'
> GO
> --
> --Acidental Delete--
> --
> use Sales
> delete from T_2004
> --TAIL LOG BACKUP
> BACKUP LOG SALES TO disk = 'C:\a\Sales_log2.log' WITH NO_TRUNCATE
> ---
> ---
> ---
> --MUST DO ALL THE RESTORE--
> ---
> USE master
> RESTORE DATABASE SALES
> FILE = 'Sales',
> FILEGROUP = 'PRIMARY'
> FROM DISK = 'C:\a\Sales_PRIMARY.bak'
> WITH noRECOVERY
> RESTORE DATABASE SALES
> FILE = '2002',
> FILEGROUP = 'F2002'
> FROM DISK = 'C:\a\Sales_F2002.bak'
> WITH noRECOVERY
> RESTORE DATABASE SALES
> FILE = '2003',
> FILEGROUP = 'F2003'
> FROM DISK = 'C:\a\Sales_F2003.bak'
> WITH noRECOVERY
> RESTORE DATABASE SALES
> FILE = '2004',
> FILEGROUP = 'F2004'
> FROM DISK = 'C:\a\Sales_F2004.bak'
> WITH noRECOVERY
> RESTORE LOG SALES FROM disk = 'C:\a\Sales_log.log' WITH RECOVERY
>
>|||Yes, on the bigining that was my idea.
But i saw that that was not possible.
At least with this script, i can do one backup of the past year and store it
on one tape on a safe place.
And daily just do a backup of the current year.
with this i will save time and disk space of doing full Backups.
This is possibles, right?
Can you point out some links regarding Filegroups Backups?
Thanks for your help.|||Well, with filegroups backup, you need to apply all subsequent transaction log backups after the
filegroup backup occurred. Say you do a fg backup Jan 1 2002. Then at Feb 23 2004 you want to
restore that filegroup backup. After restoring that filegroup backup, you need to restore all
transaction log backups you have dine since Jan 1 2002 until Feb 23 2004! Until all those
transaction log backups has been restored, the database is *not* available. So, make sure that you
*really* test these scenarios well. What I've learned about filegroup backups, I have learned from
Books Online.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"José Paulo Coelho" <JosPauloCoelho@.discussions.microsoft.com> wrote in message
news:86095755-1364-49F6-8450-51A26DC1DC56@.microsoft.com...
> Yes, on the bigining that was my idea.
> But i saw that that was not possible.
> At least with this script, i can do one backup of the past year and store it
> on one tape on a safe place.
> And daily just do a backup of the current year.
> with this i will save time and disk space of doing full Backups.
> This is possibles, right?
> Can you point out some links regarding Filegroups Backups?
> Thanks for your help.
>|||I will pay attention to that.
Thanks for your help.

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 . ThanksHi
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
> 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
> >
> >
>|||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
> 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
> > 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
> > >
> > >
> >
> >
>|||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
> 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
> > 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
> > > 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
> > > >
> > > >
> > >
> > >
> >
> >
>|||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
> 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
> > 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
> > > 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
> > > > 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
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||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
> 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
> > 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
> > > 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
> > > > 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
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||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...
>> 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
>> > 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
>> > > 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
>> > > >
>> > > >
>> > >
>> > >
>> >
>> >
>>
>|||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
> >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
> > 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
> > > 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
> > > > 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
> > > > > 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
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>|||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
> element in the database recovery. I though
>
>
>
> "Uri Dimant" wrote:
> > 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
> > > 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
> > > > 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
> > > > > 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
> > > > > > 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
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
> >