Adding/Modifying files and filegroup in Database - SQL Server by serverku

You all know about the database file structure as whenever we created databases without specifying a file location and file groups, they will be created in default path and default filegroup. Here I have tried to create a new database without defining any options, look the file structure on the default location and the default group.

CREATE DATABASE FILEGROUPDB
GO

SP_HELPDB FileGroupDB
GO

(Click on image to enlarge)

We can change the default filegroup with following script.

ALTER DATABASE AdventureWorks2008R2 
MODIFY FILEGROUP [PRIMARY] DEFAULT; 
GO

As a better perspective for the database Architecture, some frequently updated or used tables should be on different filegroup and the database secondary and log files also be one different filegroup. TempDB data and log files should have alone disk and filegroup as well.

Let us we go ahead with some of activities as how can we can create a new filegroup, how can we add the new secondary files to that file group. We will also look for the modifying the database file and changing the location. First capture the above image of the database file structure before doing any activities with database files. Please go to details explained by the comments in the script below and then we will compare the database file structure with original one.

USE MASTER 
GO

-- Adding a new filegroup to database 
ALTER DATABASE FileGroupDB 
ADD FILEGROUP NewFileGroup; 
GO

--Adding a new seconday and log files to a database to above created filegroup 
ALTER DATABASE FileGroupDB 
ADD FILE 
( 
    -- New secondary files added here 
    NAME = FileGroupDB_Data_1, 
    FILENAME = 'C:\FileGroupDB_Data_1.ndf', 
    SIZE = 15 MB, 
    MAXSIZE = 100 MB, 
    FILEGROWTH = 5 MB 
), 
( 
    -- New log file added here 
    NAME = FileGroupDB_Log_1, 
    FILENAME = 'C:\FileGroupDB_Log_1.ldf', 
    SIZE = 5 MB, 
    MAXSIZE = 100 MB, 
    FILEGROWTH = 5 MB 
) 
TO FILEGROUP NewFileGroup; -- Defining filegroup name here 
GO

-- Modifying size of the file 
ALTER DATABASE FileGroupDB 
MODIFY FILE  
( 
-- Changing log file size here 
NAME = FileGroupDB_Log, 
SIZE = 20 MB 
)

-- Moving log file to another location 
ALTER DATABASE FileGroupDB 
MODIFY FILE 
( 
NAME = FILEGROUPDB_log, -- Moving first log file to new location here 
FILENAME = 'C:\FileGroupDB_Log.ldf' 
) 
GO

(Click on image to enlarge)

After running the last script you will notified by following message,
"The file "FILEGROUPDB_log" has been modified in the system catalogue. The new path will be used the next time the database is started.
It will use the new file location when SQL Service will be restarted. We can have another option is taking database offline and use the same scripts written above and again database needs to back online which does not need to restart the database service.