HomeBlogs & NewsServers & HostingMS Sql ServerHow to Add/Delete Data or Log Files in MSSQL Server?

How to Add/Delete Data or Log Files in MSSQL Server?

  • October 13, 2020
  • Posted by: administrator
  • Category: MS Sql Server
No Comments

In this tutorial, we can check how to add/delete data or log files in MSSQL Server.

The MSSQL server is a database server that acts as a relational database management system. The MSSQL server is a software product that helps to store and retrieve user data as required by other products or applications. The SQL server can be either installed on the same system or a system across a network.

In this tutorial, you acquire a knowledge of how to add or delete data/log files to a database and also to attach a database file to SQL Server Express.

How to Add Data/Log Files to a Database

Open the Object Explorer and connect to a SQL Server Database Engine instance.

Expand the ‘instance’. Then, Expand the ‘Databases’.

Open the database ‘Properties’ by right-clicking the specific database to add the files.

This above step opens the ‘Database Properties’ box; in there, select the ‘Files’ page.

In the ‘Files’ page, click the ‘Add’ button to add a data or transaction log file.

Then, select the file type that you are going to add, such as data or log.

If you want to add a data file, you should have a filegroup available. Select the filegroup to which you are going to add the file from the list or select ‘<new filegroup>’ if you want to add the file to a new filegroup. Note that there is no need to put the transaction logs file in the filegroups.

Then, specify the initial size of the files. Always make sure that the data file size gets specified in no small value possible, as you can expect a maximum amount of data.

Specify how your files should grow by clicking the three-dot icon. This icon opens the ‘Autogrowth’ column. From the column, select any of the following options to specify the growth of the file.

Check the ‘Enable Autogrowth’ option, if you want to allow the recently selected file to grow as more space gets required for the data. After enabling Autogrowth, select any of the following options to specify the value.

  1. If your file should grow by fixed increments, then select the ‘In Megabytes’ option and specify the value.
  2. To specify the growth value in the percentage of the current file, select the ‘In Percent’ option, and specify the percentage value.

Next, specify the maximum file size limit by selecting any one of the following options.

  1. Select the ‘Restricted File Growth (MB)’ option to specify the value of the maximum file size that should be allowed for the file to grow.
  2. If you don’t want to set a maximum value for the file size, then select the ‘Unrestricted File Growth’ option.
  3. To completely stop the growth of the file, then clear the ‘Enable Autogrowth’ checkbox. In this case, the file won’t grow beyond the specified ‘Initial size (MB).

Next, mention the path of the file. Make sure that the file path is already existing, before adding the file.

Click the ‘Ok’ button to add the data or log file.

How to Delete Data/Log Files from a Database

  1. Open the ‘Object Explorer’ option and connect to the SQL Server Database Engine instance.
  2. Expand the selected instance and under that expand the Databases.
  3. Under the database, right-click the database from which you want to delete the file to open the Properties window.
  4. From the ‘Database Properties’ window, select the ‘Files’ page.
  5. Select the file that you need to delete from the Database files grid, and click the ‘Remove’ button.
  6. Then, click the ‘Ok’ button to save the changes.

How to Attach a Database File

The ‘sqlcmd’ tool helps you to attach a database file to an instance of the SQL Server Database Engine. If you want to link a database file available on your old SQL installation to the new installation, then you can attach the database file to the new SQL installation. Also, this process helps you to attach the backed-up copy of your database if it gets corrupted. To attach the file, follow the below process.

Log in to the server via RDP and open the command prompt.

Run the following sqlcmd command to connect to a SQL Server instance.

> sqlcmd -S <Server_name>\<instance>

In the above command, replace <Server_name> and <instance> flags with your server name and the name of the instance, respectively.

To attach the file type the following command after successfully connecting to the instance. In this command, the databasename flag represents the name of the database that you want to attach. The FILENAME flag represents the path and the file name of logs and database files.

USE [master]

GO

CREATE DATABASE [databasename] ON

( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\<databasename>.mdf' ),

( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\<databasename>.ldf' )

FOR ATTACH ;

GO

Run the following command to verify if the database is attached. This command asks the sqlcmd tool to display the names of all the databases attached to the connected SQL Server Express instance. Check if the database you have attached in step 3 is available on the list.

select name from sys.databases

go


Easysoftonic