Format Alignment: primary

Showing posts with label primary. Show all posts

Monday, March 26, 2012

fill factor problem

Hi !
I have a problem with my current database.
All the fill factor of primary key and index in the table are set to
90% and it slow down the performance of store procedure.
I had manually change the fill factor to 0 ( this process take quite
sometime) for some table and I see the store procedure performance had
increased significially.
There are just too many table involve, how can i write a script to
change the fill factor of index for every table ? can i use dbcc
reindex ?
thanks !A fill factor of 100% across the board is not necessarily a good thing. I
recently had to change a fill factor from 90 down to 60 for a client because
of high insert activity. Offhand, the only place I can think of where you'd
want 100% fill factors is in a read-only DB or a data warehouse that gets
refreshed infrequently.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"pizza" <jeffchongonly@.gmail.com> wrote in message
news:1142043562.406691.187630@.j33g2000cwa.googlegroups.com...
Hi !
I have a problem with my current database.
All the fill factor of primary key and index in the table are set to
90% and it slow down the performance of store procedure.
I had manually change the fill factor to 0 ( this process take quite
sometime) for some table and I see the store procedure performance had
increased significially.
There are just too many table involve, how can i write a script to
change the fill factor of index for every table ? can i use dbcc
reindex ?
thanks !sql

fill factor problem

Hi !
I have a problem with my current database.
All the fill factor of primary key and index in the table are set to
90% and it slow down the performance of store procedure.
I had manually change the fill factor to 0 ( this process take quite
sometime) for some table and I see the store procedure performance had
increased significially.
There are just too many table involve, how can i write a script to
change the fill factor of index for every table ? can i use dbcc
reindex ?
thanks !A fill factor of 100% across the board is not necessarily a good thing. I
recently had to change a fill factor from 90 down to 60 for a client because
of high insert activity. Offhand, the only place I can think of where you'd
want 100% fill factors is in a read-only DB or a data warehouse that gets
refreshed infrequently.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"pizza" <jeffchongonly@.gmail.com> wrote in message
news:1142043562.406691.187630@.j33g2000cwa.googlegroups.com...
Hi !
I have a problem with my current database.
All the fill factor of primary key and index in the table are set to
90% and it slow down the performance of store procedure.
I had manually change the fill factor to 0 ( this process take quite
sometime) for some table and I see the store procedure performance had
increased significially.
There are just too many table involve, how can i write a script to
change the fill factor of index for every table ? can i use dbcc
reindex ?
thanks !

fill factor problem

Hi !
I have a problem with my current database.
All the fill factor of primary key and index in the table are set to
90% and it slow down the performance of store procedure.
I had manually change the fill factor to 0 ( this process take quite
sometime) for some table and I see the store procedure performance had
increased significially.
There are just too many table involve, how can i write a script to
change the fill factor of index for every table ? can i use dbcc
reindex ?
thanks !
A fill factor of 100% across the board is not necessarily a good thing. I
recently had to change a fill factor from 90 down to 60 for a client because
of high insert activity. Offhand, the only place I can think of where you'd
want 100% fill factors is in a read-only DB or a data warehouse that gets
refreshed infrequently.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"pizza" <jeffchongonly@.gmail.com> wrote in message
news:1142043562.406691.187630@.j33g2000cwa.googlegr oups.com...
Hi !
I have a problem with my current database.
All the fill factor of primary key and index in the table are set to
90% and it slow down the performance of store procedure.
I had manually change the fill factor to 0 ( this process take quite
sometime) for some table and I see the store procedure performance had
increased significially.
There are just too many table involve, how can i write a script to
change the fill factor of index for every table ? can i use dbcc
reindex ?
thanks !

Friday, March 23, 2012

Fill factor

Hi !
I have a problem with my current database.
All the fill factor of primary key and index in the table are set to
90% and it slow down the performance of store procedure.
I had manually change the fill factor to 0 ( this process take quite
sometime) for some table and I see the store procedure performance had
increased significially.
There are just too many table involve, how can i write a script to
change the fill factor of index for every table ? can i use dbcc
reindex ?
thanks !I suspect that the cause of the significant improvment in performance
is not the difference between a fill factor of 90 and one of 0. That
doesn't seem to be enough of a change for a large improvement.
Large changes in performance almost always result from changes in
execution plans. That isn't that likely from a fill factor change
directly.
Fill factor is only used when an index is created, or re-organized. If
the table has undergone major updates since either of those last
happened the original fill factor probably does not describe the
current state of the table. So unless the change was from a fresh
index at 90 it is uncertain what the real state of the table and
indexes was before.
One possible reason for the change in performance is that reorganizing
the index(es) brought the table back down to a reasonable number of
pages from its formerly

state.
Perhaps more likely is that the reorganized index(es) benefitted from
fresh statistics.
Roy Harvey
Beacon Falls, CT
On 10 Mar 2006 18:20:47 -0800, "pizza" <jeffchongonly@.gmail.com>
wrote:

>Hi !
>I have a problem with my current database.
>All the fill factor of primary key and index in the table are set to
>90% and it slow down the performance of store procedure.
>I had manually change the fill factor to 0 ( this process take quite
>sometime) for some table and I see the store procedure performance had
>increased significially.
>There are just too many table involve, how can i write a script to
>change the fill factor of index for every table ? can i use dbcc
>reindex ?
>thanks !|||Hi Roy Harvey,
Thanks for the reply,
How should i refresh the statistics ?
Shall I use DBCC Reindex for everytable, and then use sp_updatestats ?
Thanks !|||>Shall I use DBCC Reindex for everytable, and then use sp_updatestats ?
That should work fine. You may find that there are a few key tables
that are updated and joined to often that could benefit from periodic
refresh of the indexes.
Roy Harvey
Beacon Falls, CT
On 12 Mar 2006 22:19:31 -0800, "pizza" <jeffchongonly@.gmail.com>
wrote:

>Hi Roy Harvey,
>Thanks for the reply,
>How should i refresh the statistics ?
>Shall I use DBCC Reindex for everytable, and then use sp_updatestats ?
>Thanks !|||Take a look at this example (and the note at the bottom):
http://milambda.blogspot.com/2005/0...in-current.html
ML
http://milambda.blogspot.com/

Monday, March 19, 2012

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.

Monday, March 12, 2012

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

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 co
mmitted, filegroup
backup will not help you. This is because a filegroup restore work in the wa
y that you restore the
filegroup. Then all subsequent log backups until now. And you have already c
ommitted the delete...
However, you can restore an fg from a full backup into a new database (the d
esired user fg along
with the PRIMARY fg). You do this using the PARTIAL option of the RESTORE co
mmand. 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 mess
age
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 b
y
> 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 th
e database using
filegroup backup/restore.IMO, your most viable option is what I mentioned ea
rlier 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 mess
age
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 wi
thout 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 mess
age
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 l
og 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 ne
ed to restore all
transaction log backups you have dine since Jan 1 2002 until Feb 23 2004! Un
til 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 backup
s, 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 mess
age
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

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 question

Hi
I have database with multiples files (.ndf) but it resides in one filegrp
which is PRIMARY.
If i wanted to restore, do i just perform restore from full backup. Any
additional steps since there are multiple files in that filegrp?
TIA
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:DE6A17FE-9030-44B1-987C-392D73B11C8B@.microsoft.com...
> Hi
> I have database with multiples files (.ndf) but it resides in one filegrp
> which is PRIMARY.
> If i wanted to restore, do i just perform restore from full backup. Any
> additional steps since there are multiple files in that filegrp?
> TIA
It depends on how you performed your backup. If you did a database backup,
then you simply need to perform a restore from that backup.
If you did a filegroup backup, (which you probably did not do as you only
have the default PRIMARY filegroup), then you could restore the filegroup
backup and rerun all logs since the filegroup backup occurred.
Rick Sawtell
MCT, MCSD, MCDBA

Filegroup question

Hi
I have database with multiples files (.ndf) but it resides in one filegrp
which is PRIMARY.
If i wanted to restore, do i just perform restore from full backup. Any
additional steps since there are multiple files in that filegrp?
TIA"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:DE6A17FE-9030-44B1-987C-392D73B11C8B@.microsoft.com...
> Hi
> I have database with multiples files (.ndf) but it resides in one filegrp
> which is PRIMARY.
> If i wanted to restore, do i just perform restore from full backup. Any
> additional steps since there are multiple files in that filegrp?
> TIA
It depends on how you performed your backup. If you did a database backup,
then you simply need to perform a restore from that backup.
If you did a filegroup backup, (which you probably did not do as you only
have the default PRIMARY filegroup), then you could restore the filegroup
backup and rerun all logs since the filegroup backup occurred.
Rick Sawtell
MCT, MCSD, MCDBA

Filegroup question

Hi
I have database with multiples files (.ndf) but it resides in one filegrp
which is PRIMARY.
If i wanted to restore, do i just perform restore from full backup. Any
additional steps since there are multiple files in that filegrp?
TIA"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:DE6A17FE-9030-44B1-987C-392D73B11C8B@.microsoft.com...
> Hi
> I have database with multiples files (.ndf) but it resides in one filegrp
> which is PRIMARY.
> If i wanted to restore, do i just perform restore from full backup. Any
> additional steps since there are multiple files in that filegrp?
> TIA
It depends on how you performed your backup. If you did a database backup,
then you simply need to perform a restore from that backup.
If you did a filegroup backup, (which you probably did not do as you only
have the default PRIMARY filegroup), then you could restore the filegroup
backup and rerun all logs since the filegroup backup occurred.
Rick Sawtell
MCT, MCSD, MCDBA

Friday, March 9, 2012

Filegroup

I am looking for a Microsoft article to explain how to convert from primary
file group to multi file group. I have a SQL Server 2000 database with
numerous tables, stored procedures, and views. Additional articles would be
helpful to explain the benefit of using file groups and how they function.
Thank You,Hi
Visually everything that needs to be said is on BOL under the heading
"filegroups".
What else do you need to know?
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/
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:16CF3E05-AB29-4B41-90F8-8CF2FDE48D80@.microsoft.com...
> I am looking for a Microsoft article to explain how to convert from
primary
> file group to multi file group. I have a SQL Server 2000 database with
> numerous tables, stored procedures, and views. Additional articles would
be
> helpful to explain the benefit of using file groups and how they function.
> Thank You,
>
>

file will not attach to SQL reason file is not primary file

I have a customer they are running raid 5 on a windows 2000 server one of the drives went bad. The customer replaced the drive and raid rebuilt the drive, every thing seamed to be fine but there is one database file that cannot be attached to SQL. The file is 15G so I know there is information the error states that the file is not a Primary file. Any clue on how to fix this?

mdf file size 5,738,944 KB

ldf file size 10,176 KB

You need the primary file to attach this. I guess your last resort will be your backups|||There's another file out there somewhere - probably another mdf. Make sure you've got them all and the attach should work.|||Thanks the file looks to be corupted. the customer did not run any backup so I guess the information is lost. thanks again for your help.

file will not attach to SQL reason file is not primary file

mdf file size 5,738,944 KB

ldf file size 10,176 KB

Sunday, February 19, 2012

File Group Changes

How do i place table and index objects which resides in same Primary Group to seperate User File Groups?
How can i achieve this task through T-SQL statements instead of Enterprise Manager?
sentil
If your table has a clustered index sp you can re-create a clustered index
to specify a filegroup. The data will be followed by clustered index.
For more details please refer to the BOL
"senthil" <senthil@.discussions.microsoft.com> wrote in message
news:A429215F-C3EF-4EED-A208-B215002FF45E@.microsoft.com...
> How do i place table and index objects which resides in same Primary Group
to seperate User File Groups?
> How can i achieve this task through T-SQL statements instead of
Enterprise Manager?
|||As Uri says, when you move the clustered index, the data moves as well...
The generic syntax you would use would be
Create index .....
on myfilegroup
with Drop_Existing
Read about drop_existing in BOL under "Create Index"
You would do this for each index you wish to move...
Be aware that the t-log can grow tremendously during this process... So
either back it up during, or go to simple recovery mode (kicking everyone
else off ) do the builds, and move back to full recovery.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"senthil" <senthil@.discussions.microsoft.com> wrote in message
news:A429215F-C3EF-4EED-A208-B215002FF45E@.microsoft.com...
> How do i place table and index objects which resides in same Primary Group
to seperate User File Groups?
> How can i achieve this task through T-SQL statements instead of
Enterprise Manager?
|||Hi ,
FYI, If you need to move the non clustered index as well to the new file
group you have to execute the below command for each of the non clustered
indexes.
For Non-clustered index
create index <index_name> on table_name(clumn1,column2...) with
drop_existing on <file_group>
For CLustered index and data
create clustered index <index_name> on table_name(clumn1,column2...) with
drop_existing on <file_group>
Command to list the table name with file group names
select
object_name(i.id) as table_name,
groupname as [filegroup]
from sysfilegroups s, sysindexes i
where i.indid < 2
and i.groupid = s.groupid
Thanks
Hari
MCDBA
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:u2$dw4sUEHA.1604@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> As Uri says, when you move the clustered index, the data moves as well...
> The generic syntax you would use would be
> Create index .....
> on myfilegroup
> with Drop_Existing
>
> Read about drop_existing in BOL under "Create Index"
> You would do this for each index you wish to move...
> Be aware that the t-log can grow tremendously during this process... So
> either back it up during, or go to simple recovery mode (kicking everyone
> else off ) do the builds, and move back to full recovery.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "senthil" <senthil@.discussions.microsoft.com> wrote in message
> news:A429215F-C3EF-4EED-A208-B215002FF45E@.microsoft.com...
Group
> to seperate User File Groups?
> Enterprise Manager?
>

File Group Changes

How do i place table and index objects which resides in same Primary Group t
o seperate User File Groups?
How can i achieve this task through T-SQL statements instead of Enterprise M
anager?sentil
If your table has a clustered index sp you can re-create a clustered index
to specify a filegroup. The data will be followed by clustered index.
For more details please refer to the BOL
"senthil" <senthil@.discussions.microsoft.com> wrote in message
news:A429215F-C3EF-4EED-A208-B215002FF45E@.microsoft.com...
> How do i place table and index objects which resides in same Primary Group
to seperate User File Groups?
> How can i achieve this task through T-SQL statements instead of
Enterprise Manager?|||As Uri says, when you move the clustered index, the data moves as well...
The generic syntax you would use would be
Create index .....
on myfilegroup
with Drop_Existing
Read about drop_existing in BOL under "Create Index"
You would do this for each index you wish to move...
Be aware that the t-log can grow tremendously during this process... So
either back it up during, or go to simple recovery mode (kicking everyone
else off ) do the builds, and move back to full recovery.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"senthil" <senthil@.discussions.microsoft.com> wrote in message
news:A429215F-C3EF-4EED-A208-B215002FF45E@.microsoft.com...
> How do i place table and index objects which resides in same Primary Group
to seperate User File Groups?
> How can i achieve this task through T-SQL statements instead of
Enterprise Manager?|||Hi ,
FYI, If you need to move the non clustered index as well to the new file
group you have to execute the below command for each of the non clustered
indexes.
For Non-clustered index
--
create index <index_name> on table_name(clumn1,column2...) with
drop_existing on <file_group>
For CLustered index and data
--
create clustered index <index_name> on table_name(clumn1,column2...) with
drop_existing on <file_group>
Command to list the table name with file group names
----
select
object_name(i.id) as table_name,
groupname as [filegroup]
from sysfilegroups s, sysindexes i
where i.indid < 2
and i.groupid = s.groupid
Thanks
Hari
MCDBA
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:u2$dw4sUEHA.1604@.TK2MSFTNGP12.phx.gbl...
> As Uri says, when you move the clustered index, the data moves as well...
> The generic syntax you would use would be
> Create index .....
> on myfilegroup
> with Drop_Existing
>
> Read about drop_existing in BOL under "Create Index"
> You would do this for each index you wish to move...
> Be aware that the t-log can grow tremendously during this process... So
> either back it up during, or go to simple recovery mode (kicking everyone
> else off ) do the builds, and move back to full recovery.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "senthil" <senthil@.discussions.microsoft.com> wrote in message
> news:A429215F-C3EF-4EED-A208-B215002FF45E@.microsoft.com...
Group[vbcol=seagreen]
> to seperate User File Groups?
> Enterprise Manager?
>

File Group Changes

How do i place table and index objects which resides in same Primary Group to seperate User File Groups?
How can i achieve this task through T-SQL statements instead of Enterprise Manager?sentil
If your table has a clustered index sp you can re-create a clustered index
to specify a filegroup. The data will be followed by clustered index.
For more details please refer to the BOL
"senthil" <senthil@.discussions.microsoft.com> wrote in message
news:A429215F-C3EF-4EED-A208-B215002FF45E@.microsoft.com...
> How do i place table and index objects which resides in same Primary Group
to seperate User File Groups?
> How can i achieve this task through T-SQL statements instead of
Enterprise Manager?|||As Uri says, when you move the clustered index, the data moves as well...
The generic syntax you would use would be
Create index .....
on myfilegroup
with Drop_Existing
Read about drop_existing in BOL under "Create Index"
You would do this for each index you wish to move...
Be aware that the t-log can grow tremendously during this process... So
either back it up during, or go to simple recovery mode (kicking everyone
else off ) do the builds, and move back to full recovery.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"senthil" <senthil@.discussions.microsoft.com> wrote in message
news:A429215F-C3EF-4EED-A208-B215002FF45E@.microsoft.com...
> How do i place table and index objects which resides in same Primary Group
to seperate User File Groups?
> How can i achieve this task through T-SQL statements instead of
Enterprise Manager?|||Hi ,
FYI, If you need to move the non clustered index as well to the new file
group you have to execute the below command for each of the non clustered
indexes.
For Non-clustered index
--
create index <index_name> on table_name(clumn1,column2...) with
drop_existing on <file_group>
For CLustered index and data
--
create clustered index <index_name> on table_name(clumn1,column2...) with
drop_existing on <file_group>
Command to list the table name with file group names
----
select
object_name(i.id) as table_name,
groupname as [filegroup]
from sysfilegroups s, sysindexes i
where i.indid < 2
and i.groupid = s.groupid
Thanks
Hari
MCDBA
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:u2$dw4sUEHA.1604@.TK2MSFTNGP12.phx.gbl...
> As Uri says, when you move the clustered index, the data moves as well...
> The generic syntax you would use would be
> Create index .....
> on myfilegroup
> with Drop_Existing
>
> Read about drop_existing in BOL under "Create Index"
> You would do this for each index you wish to move...
> Be aware that the t-log can grow tremendously during this process... So
> either back it up during, or go to simple recovery mode (kicking everyone
> else off ) do the builds, and move back to full recovery.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "senthil" <senthil@.discussions.microsoft.com> wrote in message
> news:A429215F-C3EF-4EED-A208-B215002FF45E@.microsoft.com...
> >
> > How do i place table and index objects which resides in same Primary
Group
> to seperate User File Groups?
> >
> > How can i achieve this task through T-SQL statements instead of
> Enterprise Manager?
>

File Group Backup and Restore

Hi,
Done following things :
1. Created a database with three filegroups i.e. Primary, SalesGroup1, SalesGroup2.
2. Created a table in each of the filegroups i.e. Primarytable in Primary filegroup, TableGrp1 in SalesGroup1 filegroup and TableGrp2 in SalesGroup2 Filegroup.
3. After that you inserted two records in each of these tables.
4. So the state of each of the table after inserting the 2 records in it is as follows :
PrimaryTable-- TableGrp1-- TableGrp2
-- 1 -- 1 -- 1
-- 2 -- 2 -- 2
5. After this, you took the backup of FileGroup SalesGroup2 of Sales database at location C:\MSSQLTESTS\Backups\FileGroup3-1.bak
6. Then you inserted the record in Table TableGrp2 with the value of 10.
7. So now, the records in all the three tables are as follows :
PrimaryTable -- TableGrp1 -- TableGrp2
-- 1 -- 1 -- 1
-- 2 -- 2 -- 2
---10
8. At this state u took a transaction log backup of sales database at location C:\MSSQLTESTS\Backups\tlog-1.bak
9. Then, you inserted one more record in the Table TableGrp2 with the value of 20. So, the records in alll the three tables are as follows :
PrimaryTable -- TableGrp1 -- TableGrp2
-- 1 -- 1 -- 1
-- 2 -- 2 -- 2
---10
---20
10. Again, at this stage you took a backup of the transaction log for sales database at location C:\MSSQLTESTS\Backups\tLog-2.bak
11. Now, you inserted one more record in the Table TableGrp2 with the value 60. So the records in all the three tables of sales database are as follows:
PrimaryTable -- TableGrp1 -- TableGrp2
-- 1 -- 1 -- 1
-- 2 -- 2 -- 2
---10
---20
---60
12. Finally, you took the transaction log of sales database with WITH NO_TRUNCATE option at location C:\MSSQLTESTS\Backups\tLogtail-1.bak.
13. Now comes the main part of Restoring. First you restored the backup FileGroup3-1.bak which is at location C:\MSSQLTESTS\Backups\FileGroup3-1.bak.
14. After that you restored the transaction log backup named tlog-1.bak which is at location C:\MSSQLTESTS\Backups\tlog-1.bak. (The transaction log backup that is taken at the stage 8)
15. Then you restored the transaction log backup named tLog-2.bak at location C:\MSSQLTESTS\Backups\tLog-2.bak. (The transaction log backup that is taken at the stage 10).
16. Finally you restored the last transaction log backup of the sales database named tLogtail-1.bak that is at location C:\MSSQLTESTS\Backups\tLogtail-1.bak. (The transaction log backup taken at the stage 12)
17. So finally after restoring all the transaction logs, when u see the records in all the three tables of the database, they are as follows :
PrimaryTable -- TableGrp1 -- TableGrp2
-- 1 -- 1 -- 1
-- 2 -- 2 -- 2
---10
---20
---60
18. But what I want is, since the first backup of the filegroup that I had taken is at the stage ,
PrimaryTable -- TableGrp1 -- TableGrp2
-- 1 -- 1 -- 1
-- 2 -- 2 -- 2
When I will restore this database, my databases' tables should have the above same records.
19. The main problem is that, in case I will make some wrong updates or inserts or deletion in the table TableGrp2, and then if I will restore the database in this manner then the wrong records will also be seen again in the database table, which i
dont want. e.g. if the record of value 60 in table TableGrp2 is inserted in the database by mistake then with restoring the database using the backed up data will also restore the record with value 60 which I dont want.
20. I also tried restoring database without restoring the last transaction log backup i.e. tLogtail-1.bak. But then my database remains in the loading state about which I have no idea.
Please let me know how can I solve this problem or there is any other alternative for this.
Also, if I am going wrong conceptully or if this method will not solve my problem then let me know.
Thanking you in advance and waiting for your reply.
Thanks and regards,
a_k93
Hi,
I understand that you are trying to restore the transaction log upto a
particular point. The "Restore" command has a parameter for this.
The StopAT parameter can be used to restore the transaction upto a
particular datetime.
Restore a transaction log:
RESTORE LOG { database_name | @.database_name_var }
[ FROM < backup_device > [ ,...n ] ]
[ WITH
[ RESTRICTED_USER ]
[ [ , ] FILE = { file_number | @.file_number } ]
[ [ , ] PASSWORD = { password | @.password_variable } ]
[ [ , ] MOVE 'logical_file_name' TO 'operating_system_file_name' ]
[ ,...n ]
[ [ , ] MEDIANAME = { media_name | @.media_name_variable } ]
[ [ , ] MEDIAPASSWORD = { mediapassword | @.mediapassword_variable } ]
[ [ , ] KEEP_REPLICATION ]
[ [ , ] { NORECOVERY | RECOVERY | STANDBY =
{undo_file_name|@.undo_file_name_var} } ]
[ [ , ] { NOREWIND | REWIND } ]
[ [ , ] { NOUNLOAD | UNLOAD } ]
[ [ , ] RESTART ]
[ [ , ] STATS [= percentage ] ]
[ [ , ] STOPAT = { date_time | @.date_time_var }
| [ , ] STOPATMARK = 'mark_name' [ AFTER datetime ]
| [ , ] STOPBEFOREMARK = 'mark_name' [ AFTER datetime ]
]
]
Example :
RESTORE DATABASE MyNwind
FROM MyNwind_1, MyNwind_2
WITH NORECOVERY
RESTORE LOG MyNwind
FROM MyNwindLog1
WITH NORECOVERY
RESTORE LOG MyNwind
FROM MyNwindLog2
WITH RECOVERY, STOPAT = 'Apr 15, 1998 12:00 AM'
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.

Monday, March 26, 2012

fill factor problem

fill factor problem

fill factor problem

Friday, March 23, 2012

Fill factor

Monday, March 19, 2012

Filegroup restore problem.

Monday, March 12, 2012

Filegroup restore from full backup

Filegroup restore from full backup

Filegroup Restore

Filegroup Restore

Filegroup Restore

Filegroup question

Filegroup question

Filegroup question

Friday, March 9, 2012

Filegroup

file will not attach to SQL reason file is not primary file

file will not attach to SQL reason file is not primary file

Sunday, February 19, 2012

File Group Changes

File Group Changes

File Group Changes

File Group Backup and Restore

Format Alignment

Blog Archive

About Me