SQLServerWiki

“The Only Thing That Is Constant Is Change”

Moving system Databases for clustered SQL Server 2014 instance ‘SERVER\INSTANCE’.

Posted by database-wiki on September 22, 2016

  1. Login to the active node.
  1. Make sure new disk volume is added as dependency for SQL Server (INSTANCE)
  1. Open Failover Cluster Manager à select the running SQL Server (INSTANCE) role à select SQL Server (INSTANCE) resource from the drop down panel à right click [Take Offline].
  1. Take a file level backup of the below listed files.
name CurrentLocation state_desc
master K:\INSTANCE\MSSQL12.INSTANCE\MSSQL\DATA\master.mdf ONLINE
mastlog K:\INSTANCE\MSSQL12.INSTANCE\MSSQL\DATA\mastlog.ldf ONLINE
modeldev K:\INSTANCE\MSSQL12.INSTANCE\MSSQL\DATA\model.mdf ONLINE
modellog K:\INSTANCE\MSSQL12.INSTANCE\MSSQL\DATA\modellog.ldf ONLINE
MSDBData K:\INSTANCE\MSSQL12.INSTANCE\MSSQL\DATA\MSDBData.mdf ONLINE
MSDBLog K:\INSTANCE\MSSQL12.INSTANCE\MSSQL\DATA\MSDBLog.ldf ONLINE

 Moving the master Database:

To move the master database, follow these steps.

Since SERVER\INSTANCE is down, move the master.mdf and mastlog.ldf files to the new location.

From the Start menu, point to All Programs, point to Microsoft SQL Server, point to Configuration Tools, and then click SQL Server Configuration Manager.

In the SQL Server Services node, right-click the instance of SQL Server (for example, SQL Server (MSSQLSERVER)) and choose Properties.

In the SQL Server (INSTANCE) Properties dialog box, click the Startup Parameters tab.

In the Existing parameters box, select the –d parameter to move the master data file. Click Update to save the change.

In the Specify a startup parameter box, change the parameter to the new path of the master database.

In the Existing parameters box, select the –l parameter to move the master log file. Click Update to save the change.

In the Specify a startup parameter box, change the parameter to the new path of the master database.

The parameter value for the data file must follow the -d parameter and the value for the log file must follow the -l parameter.

In the Existing parameters box, select the –e parameter to move the ERRORLOG file. Click Update to save the change.

In the Specify a startup parameter box, change the parameter to the new path of the master database.

The parameter value for the data file must follow the -d parameter and the value for the log file must follow the -l parameter.

Start the SQL Server (INSTANCE) resource (Open Failover Cluster Manager à select the running SQL Server (INSTANCE) role à select SQL Server (INSTANCE) resource from the drop down panel à right click [Take Online].)

Verify the file change for the master database by running the following query.

SELECT name, physical_name AS CurrentLocation, state_desc

FROM sys.master_files

WHERE database_id = DB_ID(‘master’);

GO

  1. Steps for Moving MSDB, Model and Tempdb system Database to new location.

Verify the current location of MSDB and Model databases by running the below query.

SELECT name, physical_name AS CurrentLocation, state_desc

FROM sys.master_files

WHERE database_id in (DB_ID(‘MODEL’),DB_ID(‘MSDB’), DB_ID(‘TEMPDB’));

Modify the below command with updated drive path and run the below command:

USE MASTER;

GO

ALTER DATABASE MSDB MODIFY FILE (NAME = ‘MSDBData’, FILENAME=’I:\INSTANCE\MSSQL12.INSTANCE\MSSQL\DATA\MSDBData.mdf’);

GO

ALTER DATABASE MSDB MODIFY FILE (NAME = ‘MSDBLog’, FILENAME=’I:\INSTANCE\MSSQL12.INSTANCE\MSSQL\DATA\MSDBLog.ldf’);

GO

Verify the file change for the master database by running the following query.

SELECT name, physical_name AS CurrentLocation, state_desc

FROM sys.master_files

WHERE database_id in (DB_ID(‘MODEL’),DB_ID(‘MSDB’), DB_ID(‘TEMPDB’));

  1. Stop SQL Server (INSTANCE): Open Failover Cluster Manager à select the running SQL Server (INSTANCE) role à select SQL Server (INSTANCE) resource from the drop down panel à right click [Take Offline].
  2. Move the model, msdb, tempdb files to new location.
  3. Start SQL Server (INSTANCE): Open Failover Cluster Manager à select the running SQL Server (INSTANCE) role à select SQL Server (INSTANCE) resource from the drop down panel à right click [Take Online].

Follow-up: After Moving All System Databases

If you have moved all of the system databases to a new drive or volume or to another server with a different drive letter, make the following updates.

Change the SQL Server Agent log path. If you do not update this path, SQL Server Agent will fail to start.

Change the database default location. Creating a new database may fail if the drive letter and path specified as the default location do not exist.

Change the SQL Server Agent Log Path

From SQL Server Management Studio, in Object Explorer, expand SQL Server Agent.

Right-click Error Logs and click Configure.

In the Configure SQL Server Agent Error Logs dialog box, specify the new location of the SQLAGENT.OUT file. The default location is C:\Program Files\Microsoft SQL Server\MSSQL12.<instance_name>\MSSQL\Log\.

Change the database default location

From SQL Server Management Studio, in Object Explorer, right-click the SQL Server and click Properties.

In the Server Properties dialog box, select Database Settings.

Under Database Default Locations, browse to the new location for both the data and log files.

Stop and start the SQL Server service to complete the change.

Change the default location for extended events

  1. Changing the path for default traces.

–ALTER SERVER CONFIGURATION SET DIAGNOSTICS LOG OFF;

–ALTER SERVER CONFIGURATION

–SET DIAGNOSTICS LOG PATH = ‘F:\MSSQL12.SJC3_NT2_SQL01\MSSQL\LOG’;

–ALTER SERVER CONFIGURATION SET DIAGNOSTICS LOG ON;

  1. Change the location of the event_file in properties to correct location for custom traces.

Change the default location for maintenance plan logging.

–change agent service log location.

–edit each maintenance plan “reporting and logging” location.

Change the default location for Full-Text Search logging.

Go to the location in “HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL10.<INSTANCEID>\Setup\SQLDataRoot” and change the value of SQLDataRoot to New location

Restart SQL full-text filter daemon launcher service from configuration manager.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: