How to add Data or Log Files to a Database in SQL Server

In this article, we will see how to add Data or Log Files to a Database in SQL Server step by step.

Permissions required:

Requires ALTER permission on the database.

Limitations and Restrictions

1. We cannot add or remove a file while a BACKUP statement is running.
2. A maximum of 32,767 files and 32,767 filegroups can be specified for each database.

Method1: Using SQL Server Management Studio
1. Connect to SQL Server Instance and expand Databases, right-click the database from which to add the files, and then click Properties.

2. In the Database Properties dialog box, select the Files page.

3. To add a data or transaction log file, click Add.

4. In the Database files grid, enter a logical name for the file. The file name must be unique within the database.

5. Select the file type, data or log.

6. For a data file, select the filegroup in which the file should be included from the list, or select to create a new filegroup. Transaction logs cannot be put in filegroups.

7. Specify the initial size of the file. Make the data file as large as possible, based on the maximum amount of data you expect in the database.

8. To specify how the file should grow, click (…) in the Autogrowth column.

9. To specify the maximum file size limit.

10. Specify the path for the file location. The specified path must exist before adding the file.

11. Click OK.

Method2: Using Transact-SQL script

1. From the Standard bar, click New Query and provide the script and press F5.

USE [master]
GO
ALTER DATABASE [test] ADD FILE ( NAME = N'test2_data', FILENAME = N'E:\sql_data\test2_data.mdf' , SIZE = 102400KB , MAXSIZE = 102400KB , FILEGROWTH = 65536KB ) TO FILEGROUP [PRIMARY]
GO
ALTER DATABASE [test] ADD LOG FILE ( NAME = N'test2_log', FILENAME = N'E:\sql_log\test2_log.ldf' , SIZE = 102400KB , MAXSIZE = 102400KB , FILEGROWTH = 65536KB )
GO 

So, in this article we have seen to add data and log files using SQL Server Management Studio(SSMS) and SQL script successfully.