Friday, January 29, 2016

Move SharePoint Database from One Drive to Another

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:\

Important: As always, Backup your databases before performing any of this activity!

Solution:
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"
Step 2: Detach the database from SQL Server:
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
    moving a sharepoint database to another drive
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 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
    move sharepoint database to different drive
  • 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 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.
    move sharepoint database to different drive
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
Its 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.
    move sharepoint database from one drive to another
Related Post: How to move SharePoint databases to new SQL server



You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Check out these SharePoint products:

No comments :

Post a Comment

Please Login and comment to get your questions answered!


You might also like:

Related Posts Plugin for WordPress, Blogger...