Requirement: Move SharePoint 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 SharePoint side to specify where the data and log files for the database to be created.
In my scenario, our SQL Server uses SAN storage and have a different drives, Say: E:\ and F:\ and we wanted all data files to go to E:\ and LOGS into F:\
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!
Step 1: Stop All SharePoint Services
Prior 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"
Lets move SharePoint SQL Server Database Files to different drive location. 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
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 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.
use master go Exec sp_detach_db 'SP16_Content_Crescent_Intranet' go
Step 3: Move database files to new location
Once you detached the databases, You can move your database files to a new location (just a Cut-Paste from Windows Explorer) wherever required. Its a best practice to place DATA and LOG files to a different location.
Step 4: Attach the databases from new location:
Once the database files are moved to a new location, Lets attach it 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.
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 GOIts important to keep the same NAME for the database as its 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?Well by following above steps we can move existing SharePoint database to different drive. How about new databases we are going to create in 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.
You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.