How to migrate SQL Server system databases?

on

Migrate temdb, model and msdb databases

  • Backup system databases.
  • Check the location of system databases with the script:
    SELECT name AS FileLogicalName, physical_name AS FileLocation
    FROM sys.master_files 
    WHERE database_id < 5
  • Use the script to change files locations:
    USE master
    GO
 
    --tempdb
    ALTER DATABASE tempdb
    MODIFY FILE( NAME = tempdev, FILENAME = 'C:\[new_location]\tempdb.mdf' )
 
    ALTER DATABASE tempdb
    MODIFY FILE( NAME = templog, FILENAME = 'C:\[new_location]\\templog.ldf')
 
    --model
    ALTER DATABASE model
    MODIFY FILE( NAME = modeldev, FILENAME = 'C:\[new_location]\\model.mdf' )
 
    ALTER DATABASE model
    MODIFY FILE( NAME = modellog, FILENAME = 'C:\[new_location]\\modellog.ldf')
 
    --msdb
    ALTER DATABASE msdb
    MODIFY FILE( NAME = MSDBData, FILENAME = 'C:\[new_location]\\MSDBData.mdf' )
 
    ALTER DATABASE msdb
    MODIFY FILE( NAME = MSDBLog, FILENAME = 'C:\[new_location]\\MSDBLog.ldf')
  • Stop SQL Server service (you can't offline system databases) and move database files to the new location.
  • Move msdb database files to the new location.
  • Start SQL Server service (start SQL Server Agent too) and if you did everything correctly, it should start successfully.
  • Check if SQL broker is enabled.
    USE master
    GO
    SELECT is_broker_enabled  
    FROM sys.databases 
    WHERE name = N'msdb'
  • Check if the Database Email is configured correctly.
  • Delete sys database files from the old location.

Migrate master database

  • Backup master database.
  • You can't use ALTER DATABASE for moving master database files to another location. Instead, you need to change the master database path on SQL Server Configuration Manager.
  • Right-click on the corresponding SQL Server instance name then choose "Properties" > "Startup Parameters":
  • Change master database path in -d (mdf file) and -l (ldf file) and click Update (two click for each change)
  • Click "Apply" then "OK".
  • Stop the SQL Server service.


  • Move master database files to the new location.
  • Start the SQL Server service (Start SQL Server Agent too) and if you did everything correctly, it should start successfully.
  • Check the new master database location with the script:
    SELECT name AS FileLogicalName, physical_name AS FileLocation
    FROM sys.master_files 
    WHERE database_id = 1
  • Locate SQLDataRoot under "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<Instance ID>\Setup"(where the <instance ID> is the corresponding instance ID. Change SQLDataRoot to the new location. It will avoid patching failures.

  • Change DefaultData and DefaultLog keys in the registry or under Database Default Locations, browse to the new location for both the data and log files.


  • Change the SQL Server Agent Log Path. From SQL Server Management Studio, in Object Explorer, connect to the desired SQL Server instance. Right-click the instance and select Properties.
  • In the Server Properties dialog box, select Database Settings.
  • Stop and start the SQL Server service to complete the change.


References:

 


















Comments

Post a Comment