Thank this author by sharing:
By Alexander Chigrik,
There are no devices and segments in SQL Server 7.0 and SQL Server 2000,
as in SQL Server 6.5. Now databases reside on operating-system files.
There are three types of such files:
Each database consists of at least two files: one is a primary data
file (by default, with the .mdf extension), the other is log file
(by default, with the .ldf extension). There are also secondary data
files (by default, with the .ndf extension). A database can have only
one primary data file, zero or more secondary data files, and one or
more log files. Each database file can be used by only one database.
So there is no such situation (as in SQL Server 6.5 was) in which you
can create some databases with their logs on the same device (on the
same file with the .dat extension).
The database files are combined into filegroups. Each data file can be
a member of only one filegroup, but the log files cannot be members
of any filegroups. In other words, log files are managed separately
from one another. There are three types of filegroups:
Each database can have only one primary filegroup, only one default
filegroup, and zero or more user-defined filegroups. If you don't
specify user-defined filegroups, your database will contain only
one primary filegroup, which will also be the default filegroup. The
primary filegroup contains the primary data file with all system
objects in it (system tables, system stored procedures, extended
stored procedures and so on). You cannot remove system objects from
the primary filegroup, but you can create user objects in the
user-defined filegroups for allocation, performance, and administration
purposes. To create user-defined filegroup, you should use a
CREATE DATABASE or ALTER DATABASE statement with the FILEGROUP keyword.
The default filegroup is the filegroup, where all the new user
objects will be created. You can change the default filegroup
by using ALTER DATABASE statement with the DEFAULT keyword.
SQL Server database files can be configured to grow and shrink
automatically, reducing the need for database management and
eliminating many problems that occur when logs or databases run
out of space. The autogrow feature is set by default; the autoshrink
feature is set by default only for the Desktop Edition of
SQL Server 7.0. When you create a database, you must set an initial
size for the data and log files. If you want to set database files
to grow automatically, you should also specify the autogrow increment
in megabytes, kilobytes, gigabytes, terabytes, or percent. The default
is MB. You can also specify a maximum file size to prevent disk drives
from running out of space.
The default database is one of the options when creating a login in SQL Server. This is the...
sql data filegroup1 set as default but writes on primary filegroup
Database Restoring using Filegroup Backup
As a member of SQLServerCentral, you get free access to loads of fresh content: thousands
of articles and SQL scripts, a library of free eBooks, a weekly database news roundup,
a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals
that makes it such a success.