Hey guys I have a VLDB and I would like to point or move several tables to another filegroup.
1). Add a filegroup to the database
ALTER DATABASE dbname
ADD FILEGROUP filename_table
go
2). Add a file to the file group
ALTER DATABASE dbname
ADD FILE
....
TO FILEGROUP filename_table
go
If a table is already part of the primary filegroup:
Can I change it to another filegroup?
When changed to the another filegroup will it move the whole table to the other filegroup or just start to write data to the newly created filegroup?In order to move the table to another filegroup, you need to first move the data, then move any indexes that you also want on that filegroup or another filegroup. To move the data, you need to create or alter the clustered index. To move the indicies, you need to drop and recreate the indicies.
-PatP|||thanks Pat for the heads up I will work towards that...
Showing posts with label point. Show all posts
Showing posts with label point. Show all posts
Wednesday, March 21, 2012
Monday, March 12, 2012
Filegroup recommendation
Are there any general recommendations concerning filegroups? My personal point of view is to place large tables in their own filegroups and group smaller, more static, tables in a single filegroup. Is it also good practice to group small and large index in two separate filegroups or should each large index have their own filegroup? Are there any useful links out there concerning filegroups and configuration?http://www.sql-server-performance.com/filegroups.asp
Friday, March 9, 2012
File/FileGroup Restore to point in time...
Hello all...
I'm trying to do a "point in time" File/Filegroup restore. The purpose of
the restore is to retrieve a table that was deleted by accident.
The problem with my "point in time" restore... When I restore the active
log, required for file/filegroup restores, after the filegroup differential,
I end up still missing the table because I have to restore logs that include
d
the transaction that deleted the table.
My question is... How do I do a "point in time" file/filegroup restore that
restores the table that was deleted by accident.
Thanks for you help...
BATMANHi
Use the STOPAT clause. Stop before the table was dropped. You loose all
transactions after that as it will not be possible to restore any further
logs after that.
e.g.
RESTORE LOG MyNwind
FROM MyNwindLog2
WITH RECOVERY, STOPAT = 'Apr 15, 1998 12:00 AM'
Regards
Mike
"BATMAN" wrote:
> Hello all...
> I'm trying to do a "point in time" File/Filegroup restore. The purpose of
> the restore is to retrieve a table that was deleted by accident.
> The problem with my "point in time" restore... When I restore the active
> log, required for file/filegroup restores, after the filegroup differentia
l,
> I end up still missing the table because I have to restore logs that inclu
ded
> the transaction that deleted the table.
> My question is... How do I do a "point in time" file/filegroup restore th
at
> restores the table that was deleted by accident.
> Thanks for you help...
> BATMAN|||> My question is... How do I do a "point in time" file/filegroup restore thatn">
> restores the table that was deleted by accident.
You don't. That would leave the database in an inconsistent state (some data
is old, some is new).
What you do is that you restore into a new database, stopat there and then c
opy the desired table(s)
into your production database. You can check out the PARTIAL option for the
restore command, might
be useful.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"BATMAN" <BATMAN@.discussions.microsoft.com> wrote in message
news:8BE2F91C-2313-4E88-BACE-B0B8AC280C36@.microsoft.com...
> Hello all...
> I'm trying to do a "point in time" File/Filegroup restore. The purpose of
> the restore is to retrieve a table that was deleted by accident.
> The problem with my "point in time" restore... When I restore the active
> log, required for file/filegroup restores, after the filegroup differentia
l,
> I end up still missing the table because I have to restore logs that inclu
ded
> the transaction that deleted the table.
> My question is... How do I do a "point in time" file/filegroup restore th
at
> restores the table that was deleted by accident.
> Thanks for you help...
> BATMAN|||Wow... That sucks!
Thanks for your help though guys!
When dealing with a VLDB that has distributed files across multiple
filegroups, it's really inconvenient to have to restore to a temporary
database to retrieve one table. This means that you cannot do a “point in
time” recovery the way Books Online leads you to believe. But now thinkin
g
of the logic behind it all, it makes sense why you would have to restore log
s
up to the last active log.
So I guess the ideal solution is to make the primary filegroup as small as
possible so that you can use the PARTIAL restore option on a temporary
database, to only restore the filegroup needed, and then use the STOPAT
option to restore the logs prior to the table being dropped.
Again, thanks for the help!
BATMAN
"BATMAN" wrote:
> Hello all...
> I'm trying to do a "point in time" File/Filegroup restore. The purpose of
> the restore is to retrieve a table that was deleted by accident.
> The problem with my "point in time" restore... When I restore the active
> log, required for file/filegroup restores, after the filegroup differentia
l,
> I end up still missing the table because I have to restore logs that inclu
ded
> the transaction that deleted the table.
> My question is... How do I do a "point in time" file/filegroup restore th
at
> restores the table that was deleted by accident.
> Thanks for you help...
> BATMAN|||Make sure you test this thoroughly. Read about the PARTIAL option, it might
be that the backup in
question need to be a full backup. I'm not sure, it might do to have a file
group backup including
the file group in question as well as the primary file group. Check it out t
o make sure.
In 2005, you can mark a file group as read-only and when restoring such, you
don't need to apply
transaction log backups. Assuming it has been read-only since the db backup,
of course. It wouldn't
help in this particular situation, but worth mentioning...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"BATMAN" <BATMAN@.discussions.microsoft.com> wrote in message
news:D0F06F33-156C-4D63-AFB3-4080DB916335@.microsoft.com...[vbcol=seagreen]
> Wow... That sucks!
> Thanks for your help though guys!
> When dealing with a VLDB that has distributed files across multiple
> filegroups, it's really inconvenient to have to restore to a temporary
> database to retrieve one table. This means that you cannot do a "point in
> time" recovery the way Books Online leads you to believe. But now thinkin
g
> of the logic behind it all, it makes sense why you would have to restore l
ogs
> up to the last active log.
> So I guess the ideal solution is to make the primary filegroup as small as
> possible so that you can use the PARTIAL restore option on a temporary
> database, to only restore the filegroup needed, and then use the STOPAT
> option to restore the logs prior to the table being dropped.
> Again, thanks for the help!
> BATMAN
>
> "BATMAN" wrote:
>
I'm trying to do a "point in time" File/Filegroup restore. The purpose of
the restore is to retrieve a table that was deleted by accident.
The problem with my "point in time" restore... When I restore the active
log, required for file/filegroup restores, after the filegroup differential,
I end up still missing the table because I have to restore logs that include
d
the transaction that deleted the table.
My question is... How do I do a "point in time" file/filegroup restore that
restores the table that was deleted by accident.
Thanks for you help...
BATMANHi
Use the STOPAT clause. Stop before the table was dropped. You loose all
transactions after that as it will not be possible to restore any further
logs after that.
e.g.
RESTORE LOG MyNwind
FROM MyNwindLog2
WITH RECOVERY, STOPAT = 'Apr 15, 1998 12:00 AM'
Regards
Mike
"BATMAN" wrote:
> Hello all...
> I'm trying to do a "point in time" File/Filegroup restore. The purpose of
> the restore is to retrieve a table that was deleted by accident.
> The problem with my "point in time" restore... When I restore the active
> log, required for file/filegroup restores, after the filegroup differentia
l,
> I end up still missing the table because I have to restore logs that inclu
ded
> the transaction that deleted the table.
> My question is... How do I do a "point in time" file/filegroup restore th
at
> restores the table that was deleted by accident.
> Thanks for you help...
> BATMAN|||> My question is... How do I do a "point in time" file/filegroup restore thatn">
> restores the table that was deleted by accident.
You don't. That would leave the database in an inconsistent state (some data
is old, some is new).
What you do is that you restore into a new database, stopat there and then c
opy the desired table(s)
into your production database. You can check out the PARTIAL option for the
restore command, might
be useful.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"BATMAN" <BATMAN@.discussions.microsoft.com> wrote in message
news:8BE2F91C-2313-4E88-BACE-B0B8AC280C36@.microsoft.com...
> Hello all...
> I'm trying to do a "point in time" File/Filegroup restore. The purpose of
> the restore is to retrieve a table that was deleted by accident.
> The problem with my "point in time" restore... When I restore the active
> log, required for file/filegroup restores, after the filegroup differentia
l,
> I end up still missing the table because I have to restore logs that inclu
ded
> the transaction that deleted the table.
> My question is... How do I do a "point in time" file/filegroup restore th
at
> restores the table that was deleted by accident.
> Thanks for you help...
> BATMAN|||Wow... That sucks!
Thanks for your help though guys!
When dealing with a VLDB that has distributed files across multiple
filegroups, it's really inconvenient to have to restore to a temporary
database to retrieve one table. This means that you cannot do a “point in
time” recovery the way Books Online leads you to believe. But now thinkin
g
of the logic behind it all, it makes sense why you would have to restore log
s
up to the last active log.
So I guess the ideal solution is to make the primary filegroup as small as
possible so that you can use the PARTIAL restore option on a temporary
database, to only restore the filegroup needed, and then use the STOPAT
option to restore the logs prior to the table being dropped.
Again, thanks for the help!
BATMAN
"BATMAN" wrote:
> Hello all...
> I'm trying to do a "point in time" File/Filegroup restore. The purpose of
> the restore is to retrieve a table that was deleted by accident.
> The problem with my "point in time" restore... When I restore the active
> log, required for file/filegroup restores, after the filegroup differentia
l,
> I end up still missing the table because I have to restore logs that inclu
ded
> the transaction that deleted the table.
> My question is... How do I do a "point in time" file/filegroup restore th
at
> restores the table that was deleted by accident.
> Thanks for you help...
> BATMAN|||Make sure you test this thoroughly. Read about the PARTIAL option, it might
be that the backup in
question need to be a full backup. I'm not sure, it might do to have a file
group backup including
the file group in question as well as the primary file group. Check it out t
o make sure.
In 2005, you can mark a file group as read-only and when restoring such, you
don't need to apply
transaction log backups. Assuming it has been read-only since the db backup,
of course. It wouldn't
help in this particular situation, but worth mentioning...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"BATMAN" <BATMAN@.discussions.microsoft.com> wrote in message
news:D0F06F33-156C-4D63-AFB3-4080DB916335@.microsoft.com...[vbcol=seagreen]
> Wow... That sucks!
> Thanks for your help though guys!
> When dealing with a VLDB that has distributed files across multiple
> filegroups, it's really inconvenient to have to restore to a temporary
> database to retrieve one table. This means that you cannot do a "point in
> time" recovery the way Books Online leads you to believe. But now thinkin
g
> of the logic behind it all, it makes sense why you would have to restore l
ogs
> up to the last active log.
> So I guess the ideal solution is to make the primary filegroup as small as
> possible so that you can use the PARTIAL restore option on a temporary
> database, to only restore the filegroup needed, and then use the STOPAT
> option to restore the logs prior to the table being dropped.
> Again, thanks for the help!
> BATMAN
>
> "BATMAN" wrote:
>
File/FileGroup Restore to point in time...
Hello all...
I'm trying to do a "point in time" File/Filegroup restore. The purpose of
the restore is to retrieve a table that was deleted by accident.
The problem with my "point in time" restore... When I restore the active
log, required for file/filegroup restores, after the filegroup differential,
I end up still missing the table because I have to restore logs that included
the transaction that deleted the table.
My question is... How do I do a "point in time" file/filegroup restore that
restores the table that was deleted by accident.
Thanks for you help...
BATMANHi
Use the STOPAT clause. Stop before the table was dropped. You loose all
transactions after that as it will not be possible to restore any further
logs after that.
e.g.
RESTORE LOG MyNwind
FROM MyNwindLog2
WITH RECOVERY, STOPAT = 'Apr 15, 1998 12:00 AM'
Regards
Mike
"BATMAN" wrote:
> Hello all...
> I'm trying to do a "point in time" File/Filegroup restore. The purpose of
> the restore is to retrieve a table that was deleted by accident.
> The problem with my "point in time" restore... When I restore the active
> log, required for file/filegroup restores, after the filegroup differential,
> I end up still missing the table because I have to restore logs that included
> the transaction that deleted the table.
> My question is... How do I do a "point in time" file/filegroup restore that
> restores the table that was deleted by accident.
> Thanks for you help...
> BATMAN|||> My question is... How do I do a "point in time" file/filegroup restore that
> restores the table that was deleted by accident.
You don't. That would leave the database in an inconsistent state (some data is old, some is new).
What you do is that you restore into a new database, stopat there and then copy the desired table(s)
into your production database. You can check out the PARTIAL option for the restore command, might
be useful.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"BATMAN" <BATMAN@.discussions.microsoft.com> wrote in message
news:8BE2F91C-2313-4E88-BACE-B0B8AC280C36@.microsoft.com...
> Hello all...
> I'm trying to do a "point in time" File/Filegroup restore. The purpose of
> the restore is to retrieve a table that was deleted by accident.
> The problem with my "point in time" restore... When I restore the active
> log, required for file/filegroup restores, after the filegroup differential,
> I end up still missing the table because I have to restore logs that included
> the transaction that deleted the table.
> My question is... How do I do a "point in time" file/filegroup restore that
> restores the table that was deleted by accident.
> Thanks for you help...
> BATMAN|||Wow... That sucks!
Thanks for your help though guys!
When dealing with a VLDB that has distributed files across multiple
filegroups, it's really inconvenient to have to restore to a temporary
database to retrieve one table. This means that you cannot do a â'point in
timeâ' recovery the way Books Online leads you to believe. But now thinking
of the logic behind it all, it makes sense why you would have to restore logs
up to the last active log.
So I guess the ideal solution is to make the primary filegroup as small as
possible so that you can use the PARTIAL restore option on a temporary
database, to only restore the filegroup needed, and then use the STOPAT
option to restore the logs prior to the table being dropped.
Again, thanks for the help!
BATMAN
"BATMAN" wrote:
> Hello all...
> I'm trying to do a "point in time" File/Filegroup restore. The purpose of
> the restore is to retrieve a table that was deleted by accident.
> The problem with my "point in time" restore... When I restore the active
> log, required for file/filegroup restores, after the filegroup differential,
> I end up still missing the table because I have to restore logs that included
> the transaction that deleted the table.
> My question is... How do I do a "point in time" file/filegroup restore that
> restores the table that was deleted by accident.
> Thanks for you help...
> BATMAN|||Make sure you test this thoroughly. Read about the PARTIAL option, it might be that the backup in
question need to be a full backup. I'm not sure, it might do to have a file group backup including
the file group in question as well as the primary file group. Check it out to make sure.
In 2005, you can mark a file group as read-only and when restoring such, you don't need to apply
transaction log backups. Assuming it has been read-only since the db backup, of course. It wouldn't
help in this particular situation, but worth mentioning...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"BATMAN" <BATMAN@.discussions.microsoft.com> wrote in message
news:D0F06F33-156C-4D63-AFB3-4080DB916335@.microsoft.com...
> Wow... That sucks!
> Thanks for your help though guys!
> When dealing with a VLDB that has distributed files across multiple
> filegroups, it's really inconvenient to have to restore to a temporary
> database to retrieve one table. This means that you cannot do a "point in
> time" recovery the way Books Online leads you to believe. But now thinking
> of the logic behind it all, it makes sense why you would have to restore logs
> up to the last active log.
> So I guess the ideal solution is to make the primary filegroup as small as
> possible so that you can use the PARTIAL restore option on a temporary
> database, to only restore the filegroup needed, and then use the STOPAT
> option to restore the logs prior to the table being dropped.
> Again, thanks for the help!
> BATMAN
>
> "BATMAN" wrote:
>> Hello all...
>> I'm trying to do a "point in time" File/Filegroup restore. The purpose of
>> the restore is to retrieve a table that was deleted by accident.
>> The problem with my "point in time" restore... When I restore the active
>> log, required for file/filegroup restores, after the filegroup differential,
>> I end up still missing the table because I have to restore logs that included
>> the transaction that deleted the table.
>> My question is... How do I do a "point in time" file/filegroup restore that
>> restores the table that was deleted by accident.
>> Thanks for you help...
>> BATMAN
I'm trying to do a "point in time" File/Filegroup restore. The purpose of
the restore is to retrieve a table that was deleted by accident.
The problem with my "point in time" restore... When I restore the active
log, required for file/filegroup restores, after the filegroup differential,
I end up still missing the table because I have to restore logs that included
the transaction that deleted the table.
My question is... How do I do a "point in time" file/filegroup restore that
restores the table that was deleted by accident.
Thanks for you help...
BATMANHi
Use the STOPAT clause. Stop before the table was dropped. You loose all
transactions after that as it will not be possible to restore any further
logs after that.
e.g.
RESTORE LOG MyNwind
FROM MyNwindLog2
WITH RECOVERY, STOPAT = 'Apr 15, 1998 12:00 AM'
Regards
Mike
"BATMAN" wrote:
> Hello all...
> I'm trying to do a "point in time" File/Filegroup restore. The purpose of
> the restore is to retrieve a table that was deleted by accident.
> The problem with my "point in time" restore... When I restore the active
> log, required for file/filegroup restores, after the filegroup differential,
> I end up still missing the table because I have to restore logs that included
> the transaction that deleted the table.
> My question is... How do I do a "point in time" file/filegroup restore that
> restores the table that was deleted by accident.
> Thanks for you help...
> BATMAN|||> My question is... How do I do a "point in time" file/filegroup restore that
> restores the table that was deleted by accident.
You don't. That would leave the database in an inconsistent state (some data is old, some is new).
What you do is that you restore into a new database, stopat there and then copy the desired table(s)
into your production database. You can check out the PARTIAL option for the restore command, might
be useful.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"BATMAN" <BATMAN@.discussions.microsoft.com> wrote in message
news:8BE2F91C-2313-4E88-BACE-B0B8AC280C36@.microsoft.com...
> Hello all...
> I'm trying to do a "point in time" File/Filegroup restore. The purpose of
> the restore is to retrieve a table that was deleted by accident.
> The problem with my "point in time" restore... When I restore the active
> log, required for file/filegroup restores, after the filegroup differential,
> I end up still missing the table because I have to restore logs that included
> the transaction that deleted the table.
> My question is... How do I do a "point in time" file/filegroup restore that
> restores the table that was deleted by accident.
> Thanks for you help...
> BATMAN|||Wow... That sucks!
Thanks for your help though guys!
When dealing with a VLDB that has distributed files across multiple
filegroups, it's really inconvenient to have to restore to a temporary
database to retrieve one table. This means that you cannot do a â'point in
timeâ' recovery the way Books Online leads you to believe. But now thinking
of the logic behind it all, it makes sense why you would have to restore logs
up to the last active log.
So I guess the ideal solution is to make the primary filegroup as small as
possible so that you can use the PARTIAL restore option on a temporary
database, to only restore the filegroup needed, and then use the STOPAT
option to restore the logs prior to the table being dropped.
Again, thanks for the help!
BATMAN
"BATMAN" wrote:
> Hello all...
> I'm trying to do a "point in time" File/Filegroup restore. The purpose of
> the restore is to retrieve a table that was deleted by accident.
> The problem with my "point in time" restore... When I restore the active
> log, required for file/filegroup restores, after the filegroup differential,
> I end up still missing the table because I have to restore logs that included
> the transaction that deleted the table.
> My question is... How do I do a "point in time" file/filegroup restore that
> restores the table that was deleted by accident.
> Thanks for you help...
> BATMAN|||Make sure you test this thoroughly. Read about the PARTIAL option, it might be that the backup in
question need to be a full backup. I'm not sure, it might do to have a file group backup including
the file group in question as well as the primary file group. Check it out to make sure.
In 2005, you can mark a file group as read-only and when restoring such, you don't need to apply
transaction log backups. Assuming it has been read-only since the db backup, of course. It wouldn't
help in this particular situation, but worth mentioning...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"BATMAN" <BATMAN@.discussions.microsoft.com> wrote in message
news:D0F06F33-156C-4D63-AFB3-4080DB916335@.microsoft.com...
> Wow... That sucks!
> Thanks for your help though guys!
> When dealing with a VLDB that has distributed files across multiple
> filegroups, it's really inconvenient to have to restore to a temporary
> database to retrieve one table. This means that you cannot do a "point in
> time" recovery the way Books Online leads you to believe. But now thinking
> of the logic behind it all, it makes sense why you would have to restore logs
> up to the last active log.
> So I guess the ideal solution is to make the primary filegroup as small as
> possible so that you can use the PARTIAL restore option on a temporary
> database, to only restore the filegroup needed, and then use the STOPAT
> option to restore the logs prior to the table being dropped.
> Again, thanks for the help!
> BATMAN
>
> "BATMAN" wrote:
>> Hello all...
>> I'm trying to do a "point in time" File/Filegroup restore. The purpose of
>> the restore is to retrieve a table that was deleted by accident.
>> The problem with my "point in time" restore... When I restore the active
>> log, required for file/filegroup restores, after the filegroup differential,
>> I end up still missing the table because I have to restore logs that included
>> the transaction that deleted the table.
>> My question is... How do I do a "point in time" file/filegroup restore that
>> restores the table that was deleted by accident.
>> Thanks for you help...
>> BATMAN
File/FileGroup Restore to point in time...
Hello all...
I'm trying to do a "point in time" File/Filegroup restore. The purpose of
the restore is to retrieve a table that was deleted by accident.
The problem with my "point in time" restore... When I restore the active
log, required for file/filegroup restores, after the filegroup differential,
I end up still missing the table because I have to restore logs that included
the transaction that deleted the table.
My question is... How do I do a "point in time" file/filegroup restore that
restores the table that was deleted by accident.
Thanks for you help...
BATMAN
Hi
Use the STOPAT clause. Stop before the table was dropped. You loose all
transactions after that as it will not be possible to restore any further
logs after that.
e.g.
RESTORE LOG MyNwind
FROM MyNwindLog2
WITH RECOVERY, STOPAT = 'Apr 15, 1998 12:00 AM'
Regards
Mike
"BATMAN" wrote:
> Hello all...
> I'm trying to do a "point in time" File/Filegroup restore. The purpose of
> the restore is to retrieve a table that was deleted by accident.
> The problem with my "point in time" restore... When I restore the active
> log, required for file/filegroup restores, after the filegroup differential,
> I end up still missing the table because I have to restore logs that included
> the transaction that deleted the table.
> My question is... How do I do a "point in time" file/filegroup restore that
> restores the table that was deleted by accident.
> Thanks for you help...
> BATMAN
|||> My question is... How do I do a "point in time" file/filegroup restore that
> restores the table that was deleted by accident.
You don't. That would leave the database in an inconsistent state (some data is old, some is new).
What you do is that you restore into a new database, stopat there and then copy the desired table(s)
into your production database. You can check out the PARTIAL option for the restore command, might
be useful.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"BATMAN" <BATMAN@.discussions.microsoft.com> wrote in message
news:8BE2F91C-2313-4E88-BACE-B0B8AC280C36@.microsoft.com...
> Hello all...
> I'm trying to do a "point in time" File/Filegroup restore. The purpose of
> the restore is to retrieve a table that was deleted by accident.
> The problem with my "point in time" restore... When I restore the active
> log, required for file/filegroup restores, after the filegroup differential,
> I end up still missing the table because I have to restore logs that included
> the transaction that deleted the table.
> My question is... How do I do a "point in time" file/filegroup restore that
> restores the table that was deleted by accident.
> Thanks for you help...
> BATMAN
|||Wow... That sucks!
Thanks for your help though guys!
When dealing with a VLDB that has distributed files across multiple
filegroups, it's really inconvenient to have to restore to a temporary
database to retrieve one table. This means that you cannot do a “point in
time” recovery the way Books Online leads you to believe. But now thinking
of the logic behind it all, it makes sense why you would have to restore logs
up to the last active log.
So I guess the ideal solution is to make the primary filegroup as small as
possible so that you can use the PARTIAL restore option on a temporary
database, to only restore the filegroup needed, and then use the STOPAT
option to restore the logs prior to the table being dropped.
Again, thanks for the help!
BATMAN
"BATMAN" wrote:
> Hello all...
> I'm trying to do a "point in time" File/Filegroup restore. The purpose of
> the restore is to retrieve a table that was deleted by accident.
> The problem with my "point in time" restore... When I restore the active
> log, required for file/filegroup restores, after the filegroup differential,
> I end up still missing the table because I have to restore logs that included
> the transaction that deleted the table.
> My question is... How do I do a "point in time" file/filegroup restore that
> restores the table that was deleted by accident.
> Thanks for you help...
> BATMAN
|||Make sure you test this thoroughly. Read about the PARTIAL option, it might be that the backup in
question need to be a full backup. I'm not sure, it might do to have a file group backup including
the file group in question as well as the primary file group. Check it out to make sure.
In 2005, you can mark a file group as read-only and when restoring such, you don't need to apply
transaction log backups. Assuming it has been read-only since the db backup, of course. It wouldn't
help in this particular situation, but worth mentioning...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"BATMAN" <BATMAN@.discussions.microsoft.com> wrote in message
news:D0F06F33-156C-4D63-AFB3-4080DB916335@.microsoft.com...[vbcol=seagreen]
> Wow... That sucks!
> Thanks for your help though guys!
> When dealing with a VLDB that has distributed files across multiple
> filegroups, it's really inconvenient to have to restore to a temporary
> database to retrieve one table. This means that you cannot do a "point in
> time" recovery the way Books Online leads you to believe. But now thinking
> of the logic behind it all, it makes sense why you would have to restore logs
> up to the last active log.
> So I guess the ideal solution is to make the primary filegroup as small as
> possible so that you can use the PARTIAL restore option on a temporary
> database, to only restore the filegroup needed, and then use the STOPAT
> option to restore the logs prior to the table being dropped.
> Again, thanks for the help!
> BATMAN
>
> "BATMAN" wrote:
I'm trying to do a "point in time" File/Filegroup restore. The purpose of
the restore is to retrieve a table that was deleted by accident.
The problem with my "point in time" restore... When I restore the active
log, required for file/filegroup restores, after the filegroup differential,
I end up still missing the table because I have to restore logs that included
the transaction that deleted the table.
My question is... How do I do a "point in time" file/filegroup restore that
restores the table that was deleted by accident.
Thanks for you help...
BATMAN
Hi
Use the STOPAT clause. Stop before the table was dropped. You loose all
transactions after that as it will not be possible to restore any further
logs after that.
e.g.
RESTORE LOG MyNwind
FROM MyNwindLog2
WITH RECOVERY, STOPAT = 'Apr 15, 1998 12:00 AM'
Regards
Mike
"BATMAN" wrote:
> Hello all...
> I'm trying to do a "point in time" File/Filegroup restore. The purpose of
> the restore is to retrieve a table that was deleted by accident.
> The problem with my "point in time" restore... When I restore the active
> log, required for file/filegroup restores, after the filegroup differential,
> I end up still missing the table because I have to restore logs that included
> the transaction that deleted the table.
> My question is... How do I do a "point in time" file/filegroup restore that
> restores the table that was deleted by accident.
> Thanks for you help...
> BATMAN
|||> My question is... How do I do a "point in time" file/filegroup restore that
> restores the table that was deleted by accident.
You don't. That would leave the database in an inconsistent state (some data is old, some is new).
What you do is that you restore into a new database, stopat there and then copy the desired table(s)
into your production database. You can check out the PARTIAL option for the restore command, might
be useful.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"BATMAN" <BATMAN@.discussions.microsoft.com> wrote in message
news:8BE2F91C-2313-4E88-BACE-B0B8AC280C36@.microsoft.com...
> Hello all...
> I'm trying to do a "point in time" File/Filegroup restore. The purpose of
> the restore is to retrieve a table that was deleted by accident.
> The problem with my "point in time" restore... When I restore the active
> log, required for file/filegroup restores, after the filegroup differential,
> I end up still missing the table because I have to restore logs that included
> the transaction that deleted the table.
> My question is... How do I do a "point in time" file/filegroup restore that
> restores the table that was deleted by accident.
> Thanks for you help...
> BATMAN
|||Wow... That sucks!
Thanks for your help though guys!
When dealing with a VLDB that has distributed files across multiple
filegroups, it's really inconvenient to have to restore to a temporary
database to retrieve one table. This means that you cannot do a “point in
time” recovery the way Books Online leads you to believe. But now thinking
of the logic behind it all, it makes sense why you would have to restore logs
up to the last active log.
So I guess the ideal solution is to make the primary filegroup as small as
possible so that you can use the PARTIAL restore option on a temporary
database, to only restore the filegroup needed, and then use the STOPAT
option to restore the logs prior to the table being dropped.
Again, thanks for the help!
BATMAN
"BATMAN" wrote:
> Hello all...
> I'm trying to do a "point in time" File/Filegroup restore. The purpose of
> the restore is to retrieve a table that was deleted by accident.
> The problem with my "point in time" restore... When I restore the active
> log, required for file/filegroup restores, after the filegroup differential,
> I end up still missing the table because I have to restore logs that included
> the transaction that deleted the table.
> My question is... How do I do a "point in time" file/filegroup restore that
> restores the table that was deleted by accident.
> Thanks for you help...
> BATMAN
|||Make sure you test this thoroughly. Read about the PARTIAL option, it might be that the backup in
question need to be a full backup. I'm not sure, it might do to have a file group backup including
the file group in question as well as the primary file group. Check it out to make sure.
In 2005, you can mark a file group as read-only and when restoring such, you don't need to apply
transaction log backups. Assuming it has been read-only since the db backup, of course. It wouldn't
help in this particular situation, but worth mentioning...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"BATMAN" <BATMAN@.discussions.microsoft.com> wrote in message
news:D0F06F33-156C-4D63-AFB3-4080DB916335@.microsoft.com...[vbcol=seagreen]
> Wow... That sucks!
> Thanks for your help though guys!
> When dealing with a VLDB that has distributed files across multiple
> filegroups, it's really inconvenient to have to restore to a temporary
> database to retrieve one table. This means that you cannot do a "point in
> time" recovery the way Books Online leads you to believe. But now thinking
> of the logic behind it all, it makes sense why you would have to restore logs
> up to the last active log.
> So I guess the ideal solution is to make the primary filegroup as small as
> possible so that you can use the PARTIAL restore option on a temporary
> database, to only restore the filegroup needed, and then use the STOPAT
> option to restore the logs prior to the table being dropped.
> Again, thanks for the help!
> BATMAN
>
> "BATMAN" wrote:
Sunday, February 26, 2012
File Size Best Pratice
Hi,
There may be a FAQ covering this. If so, please point that to me.
I have data about the size of 40GB to be loaded onto a RAID configuration
for SQL Server 2000 on Windows Server 2003. Should I use one database
file of the size 40GB, 10 database files (each about 4GB), or 20 database
files (each about 2GB)?
What is the best practice for this? What are the pros and cons?
Thanks,
Wenbin ZhangThere is no official best practice that I know of. But with a DB of only
40GB a single file should be fine. SQL Server 2000 can use multiple threads
to read the same file and the Raid will also split the data between multiple
physical drives as well. You can certainly add multiple files if you like
but the more you add the harder it gets to maintain them. And with a DB that
small you probably don't have much to gain by adding more files unless you
want to isolate tables or indexes onto separate filegroups.
--
Andrew J. Kelly SQL MVP
"Wenbin Zhang" <wzhang@.onesource.com> wrote in message
news:uHk3xKh3EHA.2156@.TK2MSFTNGP10.phx.gbl...
> Hi,
> There may be a FAQ covering this. If so, please point that to me.
> I have data about the size of 40GB to be loaded onto a RAID configuration
> for SQL Server 2000 on Windows Server 2003. Should I use one database
> file of the size 40GB, 10 database files (each about 4GB), or 20 database
> files (each about 2GB)?
> What is the best practice for this? What are the pros and cons?
> Thanks,
> Wenbin Zhang
>
There may be a FAQ covering this. If so, please point that to me.
I have data about the size of 40GB to be loaded onto a RAID configuration
for SQL Server 2000 on Windows Server 2003. Should I use one database
file of the size 40GB, 10 database files (each about 4GB), or 20 database
files (each about 2GB)?
What is the best practice for this? What are the pros and cons?
Thanks,
Wenbin ZhangThere is no official best practice that I know of. But with a DB of only
40GB a single file should be fine. SQL Server 2000 can use multiple threads
to read the same file and the Raid will also split the data between multiple
physical drives as well. You can certainly add multiple files if you like
but the more you add the harder it gets to maintain them. And with a DB that
small you probably don't have much to gain by adding more files unless you
want to isolate tables or indexes onto separate filegroups.
--
Andrew J. Kelly SQL MVP
"Wenbin Zhang" <wzhang@.onesource.com> wrote in message
news:uHk3xKh3EHA.2156@.TK2MSFTNGP10.phx.gbl...
> Hi,
> There may be a FAQ covering this. If so, please point that to me.
> I have data about the size of 40GB to be loaded onto a RAID configuration
> for SQL Server 2000 on Windows Server 2003. Should I use one database
> file of the size 40GB, 10 database files (each about 4GB), or 20 database
> files (each about 2GB)?
> What is the best practice for this? What are the pros and cons?
> Thanks,
> Wenbin Zhang
>
File Size Best Pratice
Hi,
There may be a FAQ covering this. If so, please point that to me.
I have data about the size of 40GB to be loaded onto a RAID configuration
for SQL Server 2000 on Windows Server 2003. Should I use one database
file of the size 40GB, 10 database files (each about 4GB), or 20 database
files (each about 2GB)?
What is the best practice for this? What are the pros and cons?
Thanks,
Wenbin Zhang
There is no official best practice that I know of. But with a DB of only
40GB a single file should be fine. SQL Server 2000 can use multiple threads
to read the same file and the Raid will also split the data between multiple
physical drives as well. You can certainly add multiple files if you like
but the more you add the harder it gets to maintain them. And with a DB that
small you probably don't have much to gain by adding more files unless you
want to isolate tables or indexes onto separate filegroups.
Andrew J. Kelly SQL MVP
"Wenbin Zhang" <wzhang@.onesource.com> wrote in message
news:uHk3xKh3EHA.2156@.TK2MSFTNGP10.phx.gbl...
> Hi,
> There may be a FAQ covering this. If so, please point that to me.
> I have data about the size of 40GB to be loaded onto a RAID configuration
> for SQL Server 2000 on Windows Server 2003. Should I use one database
> file of the size 40GB, 10 database files (each about 4GB), or 20 database
> files (each about 2GB)?
> What is the best practice for this? What are the pros and cons?
> Thanks,
> Wenbin Zhang
>
There may be a FAQ covering this. If so, please point that to me.
I have data about the size of 40GB to be loaded onto a RAID configuration
for SQL Server 2000 on Windows Server 2003. Should I use one database
file of the size 40GB, 10 database files (each about 4GB), or 20 database
files (each about 2GB)?
What is the best practice for this? What are the pros and cons?
Thanks,
Wenbin Zhang
There is no official best practice that I know of. But with a DB of only
40GB a single file should be fine. SQL Server 2000 can use multiple threads
to read the same file and the Raid will also split the data between multiple
physical drives as well. You can certainly add multiple files if you like
but the more you add the harder it gets to maintain them. And with a DB that
small you probably don't have much to gain by adding more files unless you
want to isolate tables or indexes onto separate filegroups.
Andrew J. Kelly SQL MVP
"Wenbin Zhang" <wzhang@.onesource.com> wrote in message
news:uHk3xKh3EHA.2156@.TK2MSFTNGP10.phx.gbl...
> Hi,
> There may be a FAQ covering this. If so, please point that to me.
> I have data about the size of 40GB to be loaded onto a RAID configuration
> for SQL Server 2000 on Windows Server 2003. Should I use one database
> file of the size 40GB, 10 database files (each about 4GB), or 20 database
> files (each about 2GB)?
> What is the best practice for this? What are the pros and cons?
> Thanks,
> Wenbin Zhang
>
File Placement
Can anyone point me to any Microsoft articals giving reccomendations for file placement for SQL server? We are trying to convince our Hardware guys that we need separate disks for data/log/tempdb files and need some ammo.
Thanks,
Jason
This article may be a good starting point:-
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/storage-top-10.mspx
HTH!
|||This article reccomends RAID 1, but lots of others say RAID 5?
What's your opinions on what is better?
|||It really does depend on your data (and what you're willing to spend!!).
For read/write intensive operations (eg tempdb) you want to go with RAID1 as you get the benefit of reading from 2 disks without a heavy penalty of writing. For mainly read intensive you should go for RAID5.
Check out the different RAID levels explained in Books Online.
HTH!
Subscribe to:
Posts (Atom)