Data files and filegroups

Hi all,

Can anyone tell me direct me to any resources on the net on SQL Server data files and filegroups. I want to manage historic data (which is about 500GB and growing) for reporting purposes. I was thinking about creating a data warehouse. But first I want to build a strong file structure for database objects like tables, views, indexes etc.

I got basic information about files and filegroups from books online but I need more juice before I start actual work.

All comments are appreciated and thank you in advance.

Yogi

Hi Yogi...is this SQL 2000 or 2005? (could be some big differences in how you want to handle things between the two...).

If 2005, respond and say so...here are some articles for 2000:

For an overview on the physical architecture and general usage, these are good:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_9sab.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_02_6epf.asp

For data placement and performance (see sub-topics also):

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/optimsql/odp_tun_1_2upf.asp

For moving data:

http://www.sqljunkies.com/How%20To/B9F7F302-964A-4825-9246-6143A8681900.scuk

Performance tips:

http://www.sql-server-performance.com/filegroups.asp

Also, note that there are some special considerations sometimes for backups, administration, etc. for filegroups. Go to Books Online, move to the index tab, and type 'filegroups' in the textbox...this should bring you to the top-level filegroups item which has 10-15 sub-items...look over those too, great information there.

HTH

|||

Hey,

Thank you very much but I have gone through these links already most of them are similary to BOL.
I am still collecting information comments are welcome!

sql

Data files & Transaction log recovery

How do I backup a data file & transaction log from one instance of SQL to a different sql server. I have done a complete backup and now I want to restore the database but on a different SQL server, but the *.mdf and *.ldf is missing, so how do I restore them also?When you did the full backup you created a backup file. When you restore
this the mdf and ldf will be created.
On the instance you want to restore to restore from the device. On the
second tab will be the location of the mdf and ldf - this will be the
file names from the other instance so you will have to overtype with
another filename or path.
Nigel Rivett
www.nigelrivett.net
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||If you restore to another SQL server you also have to think about databaseusers. The users from the database you restore stil are in the DB when it's on the new server but you not see them. You can either run the drop user or use sp_change_users_login 'Auto_Fix', 'username', NULL. This is call ophran users
/Joel|||Thanks a million. I completed the backup and did the restore on the other instance of SQL server and it worked like a charm. You're a life saver!

Data files & Log Files in SQL2000

Dear all,
I have a question of the Data files & Log Files in SQL2000.
Is that once someone got the data files and log files of the database, and
he had a SQL server 2000 installed, he can use sp_attach_db command to
restore the database?
Is there any control for this case?
Best Regards,
IvanOnce you have the mdf and or ldf files of a particular db, then you can
attach the database, as far as I know, there is no remedy for this
"Ivan" <ivan@.microsoft.com> wrote in message
news:OjQEjMx1GHA.476@.TK2MSFTNGP06.phx.gbl...
> Dear all,
> I have a question of the Data files & Log Files in SQL2000.
> Is that once someone got the data files and log files of the database, and
> he had a SQL server 2000 installed, he can use sp_attach_db command to
> restore the database?
> Is there any control for this case?
> Best Regards,
> Ivan
>|||Yes , just before detaching you need to kick off all users of the database
"Ivan" <ivan@.microsoft.com> wrote in message
news:OjQEjMx1GHA.476@.TK2MSFTNGP06.phx.gbl...
> Dear all,
> I have a question of the Data files & Log Files in SQL2000.
> Is that once someone got the data files and log files of the database, and
> he had a SQL server 2000 installed, he can use sp_attach_db command to
> restore the database?
> Is there any control for this case?
> Best Regards,
> Ivan
>|||If someone stop the sql server service, copy out the data file and log file,
and start the service...
Then, he can easily duplicate the database.
Ivan
"Uri Dimant" <urid@.iscar.co.il> ¼¶¼g©ó¶l¥ó·s»D:ekHRZox1GHA.480@.TK2MSFTNGP06.phx.gbl...
> Yes , just before detaching you need to kick off all users of the
> database
>
> "Ivan" <ivan@.microsoft.com> wrote in message
> news:OjQEjMx1GHA.476@.TK2MSFTNGP06.phx.gbl...
>> Dear all,
>> I have a question of the Data files & Log Files in SQL2000.
>> Is that once someone got the data files and log files of the database,
>> and he had a SQL server 2000 installed, he can use sp_attach_db command
>> to restore the database?
>> Is there any control for this case?
>> Best Regards,
>> Ivan
>|||Ivan
Well , not in the same name unless the old database will be droped.
Actually you don't need to stop Services because you can use BACKUP/RESTORE
commands
"Ivan" <ivan@.microsoft.com> wrote in message
news:eyYLC9x1GHA.2036@.TK2MSFTNGP05.phx.gbl...
> If someone stop the sql server service, copy out the data file and log
> file, and start the service...
> Then, he can easily duplicate the database.
> Ivan
> "Uri Dimant" <urid@.iscar.co.il>
> ¼¶¼g©ó¶l¥ó·s»D:ekHRZox1GHA.480@.TK2MSFTNGP06.phx.gbl...
>> Yes , just before detaching you need to kick off all users of the
>> database
>>
>> "Ivan" <ivan@.microsoft.com> wrote in message
>> news:OjQEjMx1GHA.476@.TK2MSFTNGP06.phx.gbl...
>> Dear all,
>> I have a question of the Data files & Log Files in SQL2000.
>> Is that once someone got the data files and log files of the database,
>> and he had a SQL server 2000 installed, he can use sp_attach_db command
>> to restore the database?
>> Is there any control for this case?
>> Best Regards,
>> Ivan
>>
>|||> Is there any control for this case?
You can secure your database files (and backups) with NTFS permissions so
that only authorized users have access.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ivan" <ivan@.microsoft.com> wrote in message
news:OjQEjMx1GHA.476@.TK2MSFTNGP06.phx.gbl...
> Dear all,
> I have a question of the Data files & Log Files in SQL2000.
> Is that once someone got the data files and log files of the database, and
> he had a SQL server 2000 installed, he can use sp_attach_db command to
> restore the database?
> Is there any control for this case?
> Best Regards,
> Ivan
>|||... and even use EFS to encrypt the files, in case someone get their hands on the machine and
installs another OS on top of the old OS.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eqcp3My1GHA.4228@.TK2MSFTNGP06.phx.gbl...
>> Is there any control for this case?
> You can secure your database files (and backups) with NTFS permissions so that only authorized
> users have access.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ivan" <ivan@.microsoft.com> wrote in message news:OjQEjMx1GHA.476@.TK2MSFTNGP06.phx.gbl...
>> Dear all,
>> I have a question of the Data files & Log Files in SQL2000.
>> Is that once someone got the data files and log files of the database, and he had a SQL server
>> 2000 installed, he can use sp_attach_db command to restore the database?
>> Is there any control for this case?
>> Best Regards,
>> Ivan
>|||Hi,
To add on to tibor; take a look into the below article on implementing EFS
on sql server and its benfits.
http://www.sqlservercentral.com/columnists/bkelley/implementing_efs.asp
With EFS, database files are encrypted under the identity of the account
running SQL Server. Only this account can decrypt the files. If you need to
change the account that runs SQL Server, you should first decrypt the files
under the old account, then re-encrypt them under the new account.
Thanks
Hari
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OfrIXSy1GHA.4752@.TK2MSFTNGP05.phx.gbl...
> ... and even use EFS to encrypt the files, in case someone get their hands
> on the machine and installs another OS on top of the old OS.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:eqcp3My1GHA.4228@.TK2MSFTNGP06.phx.gbl...
>> Is there any control for this case?
>> You can secure your database files (and backups) with NTFS permissions so
>> that only authorized users have access.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Ivan" <ivan@.microsoft.com> wrote in message
>> news:OjQEjMx1GHA.476@.TK2MSFTNGP06.phx.gbl...
>> Dear all,
>> I have a question of the Data files & Log Files in SQL2000.
>> Is that once someone got the data files and log files of the database,
>> and he had a SQL server 2000 installed, he can use sp_attach_db command
>> to restore the database?
>> Is there any control for this case?
>> Best Regards,
>> Ivan
>>
>

Data files & Log Files in SQL2000

Dear all,
I have a question of the Data files & Log Files in SQL2000.
Is that once someone got the data files and log files of the database, and
he had a SQL server 2000 installed, he can use sp_attach_db command to
restore the database?
Is there any control for this case?
Best Regards,
Ivan
Once you have the mdf and or ldf files of a particular db, then you can
attach the database, as far as I know, there is no remedy for this
"Ivan" <ivan@.microsoft.com> wrote in message
news:OjQEjMx1GHA.476@.TK2MSFTNGP06.phx.gbl...
> Dear all,
> I have a question of the Data files & Log Files in SQL2000.
> Is that once someone got the data files and log files of the database, and
> he had a SQL server 2000 installed, he can use sp_attach_db command to
> restore the database?
> Is there any control for this case?
> Best Regards,
> Ivan
>
|||Yes , just before detaching you need to kick off all users of the database
"Ivan" <ivan@.microsoft.com> wrote in message
news:OjQEjMx1GHA.476@.TK2MSFTNGP06.phx.gbl...
> Dear all,
> I have a question of the Data files & Log Files in SQL2000.
> Is that once someone got the data files and log files of the database, and
> he had a SQL server 2000 installed, he can use sp_attach_db command to
> restore the database?
> Is there any control for this case?
> Best Regards,
> Ivan
>
|||If someone stop the sql server service, copy out the data file and log file,
and start the service...
Then, he can easily duplicate the database.
Ivan
"Uri Dimant" <urid@.iscar.co.il> glsD:ekHRZox1GHA.480@.TK2MSFTNGP06.phx.gb l...
> Yes , just before detaching you need to kick off all users of the
> database
>
> "Ivan" <ivan@.microsoft.com> wrote in message
> news:OjQEjMx1GHA.476@.TK2MSFTNGP06.phx.gbl...
>
|||Ivan
Well , not in the same name unless the old database will be droped.
Actually you don't need to stop Services because you can use BACKUP/RESTORE
commands
"Ivan" <ivan@.microsoft.com> wrote in message
news:eyYLC9x1GHA.2036@.TK2MSFTNGP05.phx.gbl...
> If someone stop the sql server service, copy out the data file and log
> file, and start the service...
> Then, he can easily duplicate the database.
> Ivan
> "Uri Dimant" <urid@.iscar.co.il>
> glsD:ekHRZox1GHA.480@.TK2MSFTNGP06.phx.gb l...
>
|||> Is there any control for this case?
You can secure your database files (and backups) with NTFS permissions so
that only authorized users have access.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ivan" <ivan@.microsoft.com> wrote in message
news:OjQEjMx1GHA.476@.TK2MSFTNGP06.phx.gbl...
> Dear all,
> I have a question of the Data files & Log Files in SQL2000.
> Is that once someone got the data files and log files of the database, and
> he had a SQL server 2000 installed, he can use sp_attach_db command to
> restore the database?
> Is there any control for this case?
> Best Regards,
> Ivan
>
|||... and even use EFS to encrypt the files, in case someone get their hands on the machine and
installs another OS on top of the old OS.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eqcp3My1GHA.4228@.TK2MSFTNGP06.phx.gbl...
> You can secure your database files (and backups) with NTFS permissions so that only authorized
> users have access.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ivan" <ivan@.microsoft.com> wrote in message news:OjQEjMx1GHA.476@.TK2MSFTNGP06.phx.gbl...
>
|||Hi,
To add on to tibor; take a look into the below article on implementing EFS
on sql server and its benfits.
http://www.sqlservercentral.com/colu...enting_efs.asp
With EFS, database files are encrypted under the identity of the account
running SQL Server. Only this account can decrypt the files. If you need to
change the account that runs SQL Server, you should first decrypt the files
under the old account, then re-encrypt them under the new account.
Thanks
Hari
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OfrIXSy1GHA.4752@.TK2MSFTNGP05.phx.gbl...
> ... and even use EFS to encrypt the files, in case someone get their hands
> on the machine and installs another OS on top of the old OS.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:eqcp3My1GHA.4228@.TK2MSFTNGP06.phx.gbl...
>

Data files & Log Files in SQL2000

Dear all,
I have a question of the Data files & Log Files in SQL2000.
Is that once someone got the data files and log files of the database, and
he had a SQL server 2000 installed, he can use sp_attach_db command to
restore the database?
Is there any control for this case?
Best Regards,
IvanOnce you have the mdf and or ldf files of a particular db, then you can
attach the database, as far as I know, there is no remedy for this
"Ivan" <ivan@.microsoft.com> wrote in message
news:OjQEjMx1GHA.476@.TK2MSFTNGP06.phx.gbl...
> Dear all,
> I have a question of the Data files & Log Files in SQL2000.
> Is that once someone got the data files and log files of the database, and
> he had a SQL server 2000 installed, he can use sp_attach_db command to
> restore the database?
> Is there any control for this case?
> Best Regards,
> Ivan
>|||Yes , just before detaching you need to kick off all users of the database
"Ivan" <ivan@.microsoft.com> wrote in message
news:OjQEjMx1GHA.476@.TK2MSFTNGP06.phx.gbl...
> Dear all,
> I have a question of the Data files & Log Files in SQL2000.
> Is that once someone got the data files and log files of the database, and
> he had a SQL server 2000 installed, he can use sp_attach_db command to
> restore the database?
> Is there any control for this case?
> Best Regards,
> Ivan
>|||If someone stop the sql server service, copy out the data file and log file,
and start the service...
Then, he can easily duplicate the database.
Ivan
"Uri Dimant" <urid@.iscar.co.il> glsD:ekHRZox1GHA.480@.TK2MSFTNGP06.phx.gbl...[vbcol
=seagreen]
> Yes , just before detaching you need to kick off all users of the
> database
>
> "Ivan" <ivan@.microsoft.com> wrote in message
> news:OjQEjMx1GHA.476@.TK2MSFTNGP06.phx.gbl...
>[/vbcol]|||Ivan
Well , not in the same name unless the old database will be droped.
Actually you don't need to stop Services because you can use BACKUP/RESTORE
commands
"Ivan" <ivan@.microsoft.com> wrote in message
news:eyYLC9x1GHA.2036@.TK2MSFTNGP05.phx.gbl...
> If someone stop the sql server service, copy out the data file and log
> file, and start the service...
> Then, he can easily duplicate the database.
> Ivan
> "Uri Dimant" <urid@.iscar.co.il>
> glsD:ekHRZox1GHA.480@.TK2MSFTNGP06.phx.gbl...
>|||> Is there any control for this case?
You can secure your database files (and backups) with NTFS permissions so
that only authorized users have access.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ivan" <ivan@.microsoft.com> wrote in message
news:OjQEjMx1GHA.476@.TK2MSFTNGP06.phx.gbl...
> Dear all,
> I have a question of the Data files & Log Files in SQL2000.
> Is that once someone got the data files and log files of the database, and
> he had a SQL server 2000 installed, he can use sp_attach_db command to
> restore the database?
> Is there any control for this case?
> Best Regards,
> Ivan
>|||... and even use EFS to encrypt the files, in case someone get their hands
on the machine and
installs another OS on top of the old OS.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eqcp3My1GHA.4228@.TK2MSFTNGP06.phx.gbl...
> You can secure your database files (and backups) with NTFS permissions so
that only authorized
> users have access.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ivan" <ivan@.microsoft.com> wrote in message news:OjQEjMx1GHA.476@.TK2MSFTN
GP06.phx.gbl...
>|||Hi,
To add on to tibor; take a look into the below article on implementing EFS
on sql server and its benfits.
http://www.sqlservercentral.com/col...menting_efs.asp
With EFS, database files are encrypted under the identity of the account
running SQL Server. Only this account can decrypt the files. If you need to
change the account that runs SQL Server, you should first decrypt the files
under the old account, then re-encrypt them under the new account.
Thanks
Hari
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OfrIXSy1GHA.4752@.TK2MSFTNGP05.phx.gbl...
> ... and even use EFS to encrypt the files, in case someone get their hands
> on the machine and installs another OS on top of the old OS.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:eqcp3My1GHA.4228@.TK2MSFTNGP06.phx.gbl...
>

Data Files

Hi,
I've got one database with a 50GB file. I want to split that file on, for
example 12 files of 4GB each. What is the best aproach to this operation ?
Any ideas ?
Thanks in avance
CCHere's the FileGroup example from BOL, this will enable the creation of the
12 x 4Gb files. If you then re-create all your CLUSTERED indexes specifying
this new filegroup the data will move...
B. Add a filegroup with two files to a database
This example creates a filegroup in the Test 1 database created in Example A
and adds two 5-MB files to the filegroup. It then makes Test1FG1 the default
filegroup.
USE master
GO
ALTER DATABASE Test1
ADD FILEGROUP Test1FG1
GO
ALTER DATABASE Test1
ADD FILE
( NAME = test1dat3,
FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\t1dat3.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB),
( NAME = test1dat4,
FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\t1dat4.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
TO FILEGROUP Test1FG1
ALTER DATABASE Test1
MODIFY FILEGROUP Test1FG1 DEFAULT
GO
HTH. Ryan
"CC" <CC@.discussions.microsoft.com> wrote in message
news:28E95830-203F-4658-B585-DB92E47E305B@.microsoft.com...
> Hi,
> I've got one database with a 50GB file. I want to split that file on, for
> example 12 files of 4GB each. What is the best aproach to this operation ?
> Any ideas ?
> Thanks in avance
> CC|||Why do you want to split them? if you mean in operation, you can use
filegroups...
MC
"CC" <CC@.discussions.microsoft.com> wrote in message
news:28E95830-203F-4658-B585-DB92E47E305B@.microsoft.com...
> Hi,
> I've got one database with a 50GB file. I want to split that file on, for
> example 12 files of 4GB each. What is the best aproach to this operation ?
> Any ideas ?
> Thanks in avance
> CCsql

Data Files

Hi,
I've got one database with a 50GB file. I want to split that file on, for
example 12 files of 4GB each. What is the best aproach to this operation ?
Any ideas ?
Thanks in avance
CCHere's the FileGroup example from BOL, this will enable the creation of the
12 x 4Gb files. If you then re-create all your CLUSTERED indexes specifying
this new filegroup the data will move...
B. Add a filegroup with two files to a database
This example creates a filegroup in the Test 1 database created in Example A
and adds two 5-MB files to the filegroup. It then makes Test1FG1 the default
filegroup.
USE master
GO
ALTER DATABASE Test1
ADD FILEGROUP Test1FG1
GO
ALTER DATABASE Test1
ADD FILE
( NAME = test1dat3,
FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\t1dat3.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB),
( NAME = test1dat4,
FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\t1dat4.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
TO FILEGROUP Test1FG1
ALTER DATABASE Test1
MODIFY FILEGROUP Test1FG1 DEFAULT
GO
--
HTH. Ryan
"CC" <CC@.discussions.microsoft.com> wrote in message
news:28E95830-203F-4658-B585-DB92E47E305B@.microsoft.com...
> Hi,
> I've got one database with a 50GB file. I want to split that file on, for
> example 12 files of 4GB each. What is the best aproach to this operation ?
> Any ideas ?
> Thanks in avance
> CC|||Why do you want to split them? if you mean in operation, you can use
filegroups...
MC
"CC" <CC@.discussions.microsoft.com> wrote in message
news:28E95830-203F-4658-B585-DB92E47E305B@.microsoft.com...
> Hi,
> I've got one database with a 50GB file. I want to split that file on, for
> example 12 files of 4GB each. What is the best aproach to this operation ?
> Any ideas ?
> Thanks in avance
> CC