Ads

28 July 2017

Move System Databases

Here is the steps to move the system databases:
==================================
Master 

- Change the physical location of the files from startup parameters once the SQL Server is offline, then copy and move the MDF and LDF to new location.
- Now rename existing files as old on previous location
-Now bring SQL Server online

MSDB

SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID('msdb');
GO

USE master;
GO
ALTER DATABASE msdb
MODIFY FILE (NAME = MSDBData, FILENAME = 'Z:\Data\MSDBData.mdf');
GO
ALTER DATABASE msdb
MODIFY FILE (NAME = MSDBLog, FILENAME = 'Z:\Log\MSDBLog.ldf');
GO

Model

SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID(N'model');
GO

USE master;
GO
ALTER DATABASE model
MODIFY FILE (NAME = modeldev, FILENAME = 'Z:\Data\model.mdf');
GO
ALTER DATABASE model
MODIFY FILE (NAME = modellog, FILENAME = 'Z:\SystemDBs\Log\modellog.ldf');
GO

TEMPDB

Use master
GO
SELECT name AS [LogicalName] ,physical_name AS [Location] ,state_desc AS [Status]
FROM sys.master_files WHERE database_id = DB_ID(N'tempdb');
GO

 USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'Z:\TempDB\Data\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdb2, FILENAME = 'Z:\TempDB\Data\TempDB2.ndf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdb3, FILENAME = 'Z:\TempDB\Data\TempDB3.ndf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdb4, FILENAME = 'Z:\TempDB\Data\TempDB4.ndf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'Z:\TempDB\Log\templog.ldf');
GO


Resource  DB 

Can I change the Resource database path? Should I?
No

Thanks to :
https://blogs.msdn.microsoft.com/vsanil/2012/11/02/resource-database-common-questions/
https://sqlandme.com/2013/07/08/sql-server-how-to-change-sql-server-errorlog-location/