SQL Server move database files

You will face a situation when you will need to scale your database. For example, the size of the data is increasing in the database, but you do not have enough space on your disk. In such situations, you will need to move your database files from one location to another. If you do not know how to do this, don’t worry. We have created this article to discuss everything on SQL Server move database files.

We will discuss multiple ways in this article about how to move the database files in order to scale it. Also, we will see some examples.

But, first of all, let us discuss what are the important database files that we need to move in order to change the database location. These files are installed on the default location when you install the SQL Server.

There are two important files that we need to move. These are:

1. MDF(Master Database File): MDF stores all the information about the database. This file stores information like fields, records, tabels, indexes, etc that are created in the database. In short, all the data and database structure are stored in this file.
2. LDF(Log Database FIle): As the name suggests, this file contains the logs. When we perform some operations in our database like insertion, deletion, updation or any other transaction, logs are generated and stored in the LDF file. This file helps in the error recovery and debugging.

• Move database files SQL server step by step
• SQL server move database files management studio
• SQL server move database files online
• SQL server move database files detach attach
• SQL server move database files without downtime
• SQL server move database files in availability groups
• SQL server move database files to another server
• SQL server move database files to another filegroup
• SQL server move database files access denied
• SQL server move database files to another drive
• Move system database files in SQL server
• SQL server move database log files

Move database files SQL server step by step

In this section, you will learn to move the database files from one location to another in SQL Server. We will discuss the procedure step by step. Also, we will discuss an example for better understanding.

There are a number of ways for moving the database files. We will discuss one of the methods that are easy to understand and implement.

Procedure:

• Firstly, we will get the current location of the database files.
• Then we will specify the new location for the database files so that the SQL Server instance looks for the database files in the new location.
• We need to take stop the SQL Server instance in order to change the files location. Therefore, we will stop the SQL Server instance.
• After that, we will copy the files from the original location to the new location, manage the file permissions and start the SQL Server Instance again.

Example:

We have created a sample database DemoDB. We will use this database for demonstration. We will move the files of this database from the original location to the new location.

• In the Object Explorer Window, right click on the database and click on Properties.
• Navigate to the Files option and you can check the current path of the database files.

• You can also execute the following SQL query to know the current path of the database files.

SELECT name, physical_name AS [Current Path] FROM sys.master_files
   WHERE database_id = DB_ID('DemoDB');

• After knowing the location of the files, specify the new location of the database files using the below query:

ALTER DATABASE DemoDB MODIFY FILE
( NAME = 'DemoDB',
  FILENAME = 'E:\Microsoft SQL Server\Files\DemoDB.mdf');

ALTER DATABASE DemoDB MODIFY FILE
( NAME = 'DemoDB_log', FILENAME = 'E:\Microsoft SQL Server\Files\DemoDb_log.ldf');

• Then right click on the SQL Server Instance and click on Stop to stop the SQL Server Instance.

• In the Windows Explorer copy or move the files from the original location to the new location where you want to move the database files.
• You need to manage the file permissions. Right click on a file and click on Properties and navigate to the Security tab.
• Click on Edit to edit the file permissions.
• You have to add the same user or group which you use to login into the SQL Server Instance.

• In our example, we are using the Windows Authentication and use a Windows User for logging into the SQL Server Instance.
• Do this step for both then files i.e. the mdf and the ldf files.
• Once you have managed the necessary permissions, start the SQL Server Instance in SQL Server Management Studio.

• It will take some time to recover the database. But once it is recovered, you can use it flawlessly.

Thus, you might have learned how you can move the database files in an easy way.

Read SQL Server select from stored procedure (9 Examples)

SQL server move database files Management Studio

The SQL Server Management Studio makes it easy to move the database files. In this section, you will learn how you can move the database files using SQL Server Management Studio. We are using the DemoDB database for demonstration.

• The first step is to lcoate the current location of your database.
• To locate your database, open the SQL Server Management Studio, navigate to the database in the Object Explorer Window.
• Right click on the database whose files you want to move, then click on Properties.

• In the Database Properties Window, click on Files page to see the database files path.

• Close this Window and navigate to the DemoDB database.
• Right click on the DemoDB database, go to Tasks and then click on Detach.

• After clicking on the Detach option, the Detach Database Window will be opened.
• Check the options Drop Connections and Update Statistics and click on OK.

• Once you know the path, navigate to the directory containing the DemoDB.mdf and DemoDB_log.ldf files in the Windows Explorer.
• Then copy the files from the current location to the new location where you want to store the files. You can also move the files instead of copying but it is not a good practice. Copying the files can help in recovery if anything goes wrong during the process.
• We have copied the files into the following path: E:/Microsoft SQL Server.
• You have to give full permissions to both the files.
• To give permission to a file, right click on the file to open properties.
• In the Properties Window, navigate to the Security tab and click on Edit to give permissions.

• If you are using the Windows authentication in the SQL Server Management Studio to connect to the database, you have to give full permissions to the user you are using to authenticate.
• You can also give all the permissions to all the users at once.
• After giving the necessary permissions click OK.
• In the SQL Server Management Studio, naviagate to the Object Explorer Window and right click on Databases and then click Attach.

• In the Attach Databases Window, click on Add to add the database.

• Select the path where you copied the database files, then select the the database file and click OK.

• Click on OK in the Locate Database Files.
• You can verify in the Object Explorer Window in the SQL Server Management Studio the existence of the DemoDB database.
• You can verify that all the data inside the database is the same as it was in the previous location.

Thus, you might have learned how you can use the Attach-Detach method to move database files using the SQL Server Management Studio.

Read SQL Server stored procedure output parameter

SQL server move database files online

When you have to move the database files, you have to make sure that the database files are not being accessed during this process. For this thing, you have to take the database to offline mode.

Taking the database to offline mode closes all the connections and makes the files unavailable to any process. Thus, taking to put the database offline becomes the necessary condition to move database files.

However, if you can use some alternative methods to achieve the same task. You can use the Backup and Restore Method. In this method, you have to take the backup of data in a different location. Then you have to restore this data to the new location.

This method requires some time and a huge amount of storage space in case of a large database, but if you do not want to take your database offline, the method is quite useful.

Hence, it might be clear to you that you cannot move the database files while the database is online.

SQL server move database files detach attach

In this section, you will learn how to move the database files with the detach and attach method in the SQL server 2019. We use this method to move the database files when we want to change the location of the database.

First, we detach the database from its original location. Then we move or copy the database files to the new location and attach the database files to the new location. We will use the T-SQL queries for this procedure.

• We have a database named DemoDB.
• We will detach this database and attach again with a new location.
• First of all, execute the following query to know the current path of the database files.

USE DemoDB
GO
EXEC sp_helpfile

• The above stored procedure sp_helpfile will give the information about the database. You need to see the path of the database files i.e. DemoDB.mdf and DemoDB_log.ldf.

• Now go to this location in the Windows Explorer and copy these files into the new location where you want to store your database files.
• In our example, we copied the files to E:\SQL Server\Database Files.
• Once you have copied the two files, change the permissions of these files.
• To change the permissions, right click on the file and click on Properties.
• Navigate to the Security tab, and give all the permissions to the file.
• Give permissions for the user which you use to login into the database.
• In our case, we are using the Windows Authentication. Therefore we will give permission to the Windows Users.
• Now it is the time to detach the database from its original location.

USE [master]
GO
ALTER DATABASE [DemoDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'DemoDB', @skipchecks = 'false'
GO

• Executing the dbo.sp_detach_db stored procedure will detach the database.

• Write the below query to create the database again and attach it to the new location of the database files.

USE [master]
GO
CREATE DATABASE [DemoDB] ON 
( FILENAME = N'E:\SQL Server\Database Files\DemoDB.mdf'),
( FILENAME = N'E:\SQL Server\Database Files\DemoDB_log.ldf')
 FOR ATTACH
GO

• After refreshing the the databases in the Object Explorer Window, we can see that the DemoDB database is available. We can also see the contents of the databases to verify.

Thus, in this way you can use the detach and attach method to move the database files in SQL server 2019.

Read SQL Server INSERT INTO SELECT + Examples

SQL server move database files without downtime

There are different ways in which you can move the database files. Every method has its own pros and cons. However, one con is always there i.e database downtime.

In a production environment, if you need to move the database, you cannot afford any downtime. In such a situation, you can create a backup and restore it to the new location. When you restore the backed-up data, you need to maintain the records of the new transactions coming during the restoration process as these will not be recorded in the backed-up data.

This is the best method if you want to move the database without any downtime. But, there are few points to remember:

• It will require huge space i.e. for the backup location and the restore location.
• You need to manage the new transactions during the restoration process.

SQL server move database files in availability groups

In this section, we will discuss how we can move the files of a database in the availability group. We will see a stepwise approach to do this.

• In the Object Explorer Window, expand the Availability Databases, right click on the secondary database and click Suspend Data Movement. It will open the Suspend Data Movement Wizard.
• Click OK. It will open the wizard to remove the database from Availability Group.
• Use the ALTER DATABASE statement to move the database files.
• For example, if the name of the database is DemoDB and the new location is ‘E:\SQL Server\DemoDB.mdf‘, the query would look like:

ALTER DATABASE DemoDB
MODIFY FILE(
NAME=DemoDB,
FILENAME='E:\SQL Server\DemoDB.mdf');
GO

• Stop the SQL Service on the database using the SQL Server Configuration Manager.
• Open the Windows Explorer and move the file to the newly specified location.
• Then in the SQL Server Management Studio, right click on the database and click Join to Availability Group to add the database again to the availability group.
• Once this secondary database is added to the availability group, perform a manual failure from the primary database to the secondary database so that the secondary database becomes the primary database.
• Now perform all these steps for the new secondary database.

Thus, you might have learned how to move a database that is under an availability group.

Read String or binary data would be truncated in SQL Server

SQL server move database files to another server

In this section, you will learn how you can move the database files from one server to another.

There are scenarios when you need to move the database to another server. We will discuss how we can move or copy the database to another server with the help of SQL Server Management Studio.

• Open the SQL Server Management Studio. Right click on the database you want to move, click on Tasks and then click on Copy Database.
• You will see the Copy Database Wizard using which you can move the database. Click Next.
• Enter the source server name and the authentication details and click Next.
• Enter the desitination server details i.e. Server name, authentication mode and credentials. Click Next.
• Now the Wizard will ask you for the transfer method to use. You can use either the detach and attach method or the SQL management object method.
• The detach and attach method is faster but will need to take the database offline.
• The SQL management object method is slower but useful if you do not want to take the database offline.
• After selecting the transfer method, the wizard will ask you for selecting the database you want to copy. You can check the databases which you want to copy or move. You can check the either of the options i.e. copy or move. Then click Next.
• You can check the new location of the database files i.e. mdf and ldf files.
• Now the wizard will ask you if you want some other objects to move except the database like stored procedures or jobs. If you want to move any of them, select them, else click Next.
• You need to create a package. Choose the Windows event log for logging any errors encountered while transferring the data and click on Next.
• You can select the package to run immediatiely or schedule it later. For now, choose Run immediately and click Next.
• Veirfy the options that you selected during the wizard. Once verified, click Finish.
• You will see the progress and status of the task.
• Once all the processes are complete, you can verify that the new database is available in the newly specified server.

The method we discussed is a very simple and user-friendly method to move the database files to another server.

Read msg 3609 the transaction ended in the trigger

SQL server move database files to another filegroup

You cannot move database files to another filegroup directly. Instead you have to migrate data from one filegroup to another.

When you create a secondary data file in the database belonging to a particular filegroup(Primary by default), you cannot move it direclty to another filegroup. Also, a file cannot be part of more than one filegroup. So, there is no alternative way to do it directly.

However, if you want to do it, you can do it indireclty by migrating all the data from one file to another file belonging to another filegroup. The approach is explained below:

• Create a new filegroup into which you wanted to move the database file.
• Copy or migrate data from old filegroup to the new file group. You have to migrate every data in the data file i.e. tables, views, objects etc.
• Delete the data from the old filegroup.

In this way, you can move your data from one filegroup to another filegoup in SQL Server.

Read SQL Server DateTime vs Datetime2

SQL server move database files access denied

You might have faced this error while moving the database files. The error ‘Access Denied‘ comes when you are trying to attach the database files to the SQL Server instance. This is due to the lack of enough user permissions.

When you copy or move the database files, the permissions are reset and you need to change them. To solve this problem, follow the below steps:

• Open the path from where you copied the files.
• Open the file properties and navigate to the Security tab.
• You can find the users and permissions of the file.
• Note down the current user and its permissions and give the same permissions to the same files in the new location.
• For giving the new files the permissions, go to the new file location, open file properties and again navigate to the security tab.
• You can give permissions to all the users or the same user which is there in the current database files permissions by clicking on Edit.
• You can refer to the below image for more clarity.

• In our example, we are using the Windows Authentication and using the Windows user Blades to access the database.
• Therefore we are giving the user Blades, Full Control permissions to the files.
• Make sure that you have given the necessary permissions to both the MDF and LDF files.
• Once you have granted the enough permissions, you can continue with the process and you will not face the ‘Access Denied‘ error.

Thus, you might have solved the error.

Read Create Foreign Key in SQL Server

SQL server move database files to another drive

Sometimes, you run out of space or want your database to use another drive. In that case, you need to move the database files to another drive. You need to know the procedure for this. In this section, you will learn how you can move the database files to another drive. We will use the detach and attach method files for this task.

We have created a sample database DemoDB which is in the E:/ drive. We will move its files from their original location to another drive i.e. F:/.

• Firstly, you must know the excact location of the files. You can do it by executing the following query:

SELECT name, physical_name AS [Current Path]
FROM sys.master_files
 WHERE database_id = DB_ID('DemoDB');

• Once you know the original path of the database files, use the below stored procedure sp_detach_db to detach the database from the SQL Server instance:

USE [master]
GO
ALTER DATABASE [DemoDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'DemoDB', @skipchecks = 'false'
GO

• Now move or copy the database files from the original location to another drive using the Windows Explorer.
• When you copy then files to the new location, the file permissions are different in both the locations. Therefore, you need to manage the file permissions in the new location.
• In this example, we are moving the files to the F:\SQL Server\Files location.
• The two files are:
  • DemoDB.mdf: The database file.
  • DemoDB_log.ldf: The database log file.
• To change the file permissions, right click on the file and click Properties and navigate to the Security tab.
• Click on Edit to edit the file permissions.
• In our case, we use the Windows Authentication i.e. Windows User to log in to the database. Therefore, we will give the Full Control permission to the Windows User.

• Now it is the time to attach the database to the SQL Server instance again.

USE [master]
GO
CREATE DATABASE [DemoDB] ON 
( FILENAME = N'F:\SQL Server\Files\DemoDB.mdf'),
( FILENAME = N'F:\SQL Server\Files\DemoDB_log.ldf')
 FOR ATTACH
GO

• After executing the above query, you can refresh the database and verify the existence of the database.
• Als, you can use the below query again to verify the database files current location:

SELECT name, physical_name AS [Current Path]
FROM sys.master_files
 WHERE database_id = DB_ID('DemoDB');

• As you can observe in the output, we have successfully moved the database files.

Read Saving changes is not permitted in SQL Server

Move system database files in SQL server

Sometimes you also need to move some system database files in SQL Server. In this section, you will learn how you can move the master database from its original location to a new location in SQL Server 2019.

• Open the SQL Server Configuration Manager available in the Start menu. This application is installed with the SQL Server.
• Right click on the SQL Server service and click on Properties.

• Navigate to the Startup Parameters.
• In the Existing parameters box change the values for the path in the -d flag and the -I flag.

• You can click on a flag value in the Exisiting parameters box and change its value in the Specify a startup parameter.
• For example, if the new location is E:\SQL Server\master database, the new flag value should be -dE:\SQL Server\master database\master.mdf and -lE:\SQL Server\master database\mastlog.ldf for the data and the log file respectively.

• Move or copy the physically i.e. in the Windows Explorer, move or copy your files to the newly specified location.
• Change the file permissions for both the files i.e. master.mdf and mastlog.ldf, if you are copying the file instead of moving it.
• In our case, we use the Windows Authentication i.e. Windows User to login into the SQL Server instance. Therefore we will give the Windows User the Full Control permissions for the files.

• The changes will reflect only when you will restart the SQL Server Instance.
• In SQL Server Management Studio, stop the SQL Server Instance. Right click on the instance name and click on Restart to restart the instance.

• After restarting the SQL Server instance, you can verify if we have successfully moved the master database files or not with the following query:

SELECT name, physical_name AS [Current Path]
FROM sys.master_files
 WHERE database_id = DB_ID('master')

• As you can see in the output, we have moved the master database files.

The above procedure was specific to the master database only. The procedure for moving the other database files is different. If you want to learn how to do that, you can refer to the above section Move database files SQL server step by step where we discussed the steps to move database files.

Note: You cannot move the system database Resource Database. It is not possible to change the location of the Resource Database.

Read Delete Duplicate Rows in SQL Server

SQL server move database log files

In some situations, you might need to move the database log files in SQL server 2019. For example, in a huge application where transactions are very frequent, the size of the log files increases rapidly and you run out of space. In that case, you need to move the database log files.

You will see how you can move the database log file from its original location to another location. We will use our sample database DemoDB for demonstration purposes. We will move the DemoDB database log file.

• Firstly, get the current location of the database files.

SELECT name, physical_name AS [Current Path]
FROM sys.master_files
 WHERE database_id = DB_ID('DemoDB')

• Detach the database using the below query:

USE master
GO
-- Database in Single User Mode
ALTER DATABASE DemoDB
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
GO
-- Detach Database
sp_detach_db 'DemoDB'
GO

• Once detached move the log file i.e. DemoDB_log.ldf manually in the Windows Explorer to the new location.
• We are moving the log file into the location E:\Microsoft SQL Server\Files\Log Files.
• Remember to manage the file permissions if you are copying the file instead of moving it. Because when you copy the file, permissions are not the same for the new file.
• Now attach the database to the new location by executing the below query:

USE master
GO
sp_attach_DB 'DemoDB',
'E:\SQL Server\Database Files\DemoDB.mdf',
'D:\Microsoft SQL Server\Files\Log Files\DemoDB_log.ldf'
GO

• You can verify that you have moved the database file successfully.

You may like the following SQL Server tutorials:

• SQL Server Create Temp Table
• SQL Server Row_Number
• IDENTITY_INSERT in SQL Server
• SQL Server Add Column + Examples

Thus, you might have learned how you can move the database log files from one location to another in SQL Server.

• Move database files SQL server step by step
• SQL server move database files Management Studio
• SQL server move database files online
• SQL server move database files detach attach
• SQL server move database files without downtime
• SQL server move database files in availability groups
• SQL server move database files to another server
• SQL server move database files to another filegroup
• SQL server move database files access denied
• SQL server move database files to another drive
• Move system database files in SQL server
• SQL server move database log files