How to Move SharePoint Content Databases to Another Drive?

Requirement: Move SharePoint content database from one drive to another in SQL Server!

By default, SharePoint creates its databases in the default SQL Server location: “C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA” folder (SQL Server 2016). We don’t have any option from the SharePoint side to specify where the data and log files for the database are to be created.

In my scenario, our SQL Server uses SAN storage and have different drives, Say: E:\ and F:\, and we wanted all data files to go to E:\ and LOGS into F:\

How to Move a Content Database in SharePoint?

In short, Stop SharePoint services, detach the database from SQL Server, Move database files to a different location, attach the database back and then start SharePoint services! Moving SharePoint content database files to a new location can be performed by these steps:

Step 1: Stop All SharePoint Services

Let’s move the SharePoint content database to another drive. Prior to moving databases, we have to stop all SharePoint services and applications which are accessing the databases. So, stop all SharePoint services first! Follow this article to stop or start all SharePoint services: How to Stop All SharePoint 2013 Services

If you want to move a specific content database, you can either set the database status to “Offline” from SharePoint 2013 Central Administration or:

• Dismount the database (Dismount-SPContentDatabase -Name “Content-database-name”)
• Detach the Database from SQL Server – Move the database to new location – Attach the database again
• Mount the database back to SharePoint: Mount-SPContentDatabase -Name “Content-database-name” -DatabaseServer “db-server-name” -WebApplication “Name-or-URL-of-the-webapp”

Step 2: Detach the database from SQL Server:

Let’s move SharePoint SQL Server Database Files to different drive locations. First, make a note of all database files associated with the database, such as MDF, NDF & LDF.

Make a note of the Database Files:

• Open SSMS >>  Right click on the target database >> Properties
• Go to Files tab >> Make note of the Path and File Names

  

You can get the files and locations using SQL Query:

USE "SP16_Content_Crescent_Intranet"
   
SELECT   
        DB_NAME(database_id) AS [Database Name],  
        physical_name AS [Path],  
        type_desc AS [Type]  
    FROM sys.master_files WHERE database_id = DB_ID()

Detach the databases using SQL Management studio
Make sure you stopped SharePoint from accessing the database. If you don’t stop SharePoint services or dismount the database from the SharePoint side, You’ll get “Cannot detach the database ‘Content-Database-Name’ because it is currently in use.” Error!

• Right click the database >> Tasks >> Detach

  

• Check “Drop Connections” >> Click OK to detach the database.

You can detach a database using SQL Script it as:

use master
go
Exec sp_detach_db 'SP16_Content_Crescent_Intranet'
go

Step 3: Move database files to new location

Once you have detached the databases, You can move your database files to a new location (just a Cut-Paste from Windows Explorer) wherever required. It’s a best practice to place DATA and LOG files in a different location.

Step 4: Attach the databases from new location

Once the database files are moved to a new location, Let’s attach them to SQL Server again.

• Open SQL Management studio >> Right click on Database node >> Select Attach
• Click on Add >> Browse to the new location for the databases and select the MDF file. All associated NDF and LOG files will be attached automatically. Click OK to attach the database to SQL Server.

  

You can script the above with SQL Query:

CREATE DATABASE [SP16_Content_Crescent_Intranet] ON
( FILENAME = N'D:\SQL Server\Data\SP16_Content_Crescent_Intranet.mdf' ),
( FILENAME = N'D:\SQL Server\Data\SP16_Content_Crescent_Intranet_log.ldf' )
FOR ATTACH
GO

It’s important to keep the same NAME for the database, as it’s referenced in SharePoint using the database name (regardless of the database file names!)

Step 5: Start all SharePoint Services

Use the article to start all SharePoint services again: How to Start all SharePoint 2013 Services? That’s all! We moved

SharePoint database from one drive to another!! Browse to your SharePoint site and verify all works correctly!

How to Change the default location for SQL Server Databases?

By following the above steps, we can move the existing SharePoint database to a different drive. How about the new databases we are going to create in the future?

• Login to your SQL Server >> Open SQL Server Management Studio >> Go to server properties
• Under “Database Settings” tab, Specify the locations for Data, LOG and Backup.
• Hit save to commit your changes. Restart your SQL Server Service for the changes to take effect.

  

Related Post: How to move SharePoint databases to new SQL server