Restore Master Database

Recently I was tasked with migrating a SQL Server Instance to a new infrastructure.   Along with the migration, I also had to create a DEV, QA, UAT  environments as they were not part of the original infrastructure.  Instead of trying to move all the pieces separately, I migrated the databases, along with restoring the Master database.

To being we must make sure the new SQL Server Instance matches the existing instance.  Here is how we make sure the Instances match.

use master
GO
select SERVERPROPERTY ('Collation')
select SERVERPROPERTY ('Edition')
select SERVERPROPERTY ('InstanceName')
select SERVERPROPERTY ('ProductVersion')
select SERVERPROPERTY ('ProductLevel')
select SERVERPROPERTY ('ServerName')

* See reference to SQL Server Build Number if you need to match the version numbers.

The new server must also have the same global settings.  We need to make sure these  match between instances.

use master
go
sp_configure 'show advanced options' , 1
reconfigure
go
sp_configure
go

We need to find where the database files reside on the existing machine and migrate them to the same place on the new machine.  This is the most stressful step,  as we may have to duplicate some less than desirable practices.  The drive letters must match.  The file paths must match. (If everything was installed on the C drive, we will need to place the files there temporarily.)

use master
go
select name, physical_Name --,*
from sys.master_files where database_id in (1,2,3,4) order by database_id

At this point migrate the database files (MDFs, NDFs, LDFs) from master, model, msdb, tempdb to same location on this machine as source machine.  Ensure the SQL Account has FULL access to the folders (default is NT Service\MSSQL$InstanceName).

• Backup master, model, msdb, tempdb on new instance, incase we need to roll back for any reason.


• Stop New Instance


• Start new instance in single user mode

From Command Prompt with elevated privileges, start SQL SERVER in single user mode

cd [drive]:\folder_name  sqlservr.exe -c -m

From the command prompt, make sure you are connected to right server

select @@servername
GO

We are not ready to restore the Master database.

RESTORE DATABASE master FROM DISK = '[Drive]:\[backup location]Program Files\Microsoft SQL Server\MSSQL13.Instance\MSSQL\Backup\master.bak' WITH REPLACE;
GO

Make sure our new instance has access to the location of the original Temp database and System databases.

• Start SQL Server from service (multi-user)


• We are now running from the "Wrong" database files


• Correct the file paths

use master
go

select * from sys.master_files

We can see that our file paths need corrected

Alter database tempdb modify file
(name=tempdev, filename='[drive]:\new_location\tempdb.mdf')
Alter database tempdb modify file
(name=tempdev2, filename='[drive]:\new_location\tempdev2.ndf')
Alter database tempdb modify file
(name=templog, filename='[drive]:\new_location\templog.ldf')

Alter database model modify file
(name=modeldev, filename='[drive]:\new_location\model.mdf')
Alter database model modify file
(name=modellog, filename='[drive]:\new_location\modellog.ldf')

• Stop service


• Copy model files to correct location


• TempDB will be automatically recreated  (I deleted the old ones)


• Start Service


• Restore MSDB


• Check all files are in the correct place

select * from sys.master_files

• Remove "Wrong" Folders (All the migrated MDFs, NDFs, LDFs in the wrong place)


• Anything useing the Service Master Key will not work

on [Old Server]

BACKUP SERVICE MASTER KEY TO FILE ='[Drive]:\share\master_key' ENCRYPTION BY PASSWORD ='password'

• right click on file, properties, security, permissions: Give full permissions to your user.

on [New Server]

RESTORE SERVICE MASTER KEY FROM FILE = '[Drive]:\master_keymaster_key' DECRYPTION BY PASSWORD = 'password' force

 

• Change Server Name in System Tables. @@servername variable will point to old server. It must be changed.


• Check servername with ID = 0. The queries will return old server name [SERVER A]. [SERVER B] will not be found.

SELECT @@servername
SELECT srvname FROM sysservers where srvid = 0

GO

EXEC sp_dropserver '[Old Server]\[Old Instance]'
EXEC sp_addserver '[New Server]\[New Instance]' , 'local' ,'duplicate_OK'

Restart the instance and check to make sure the [New Server] name is in place.

SELECT @@servername
SELECT srvname FROM sysservers where srvid = 0

In my case the Instance name changed and I had been using machine level accounts.  I need to give the machine access to the new master database.

USE [master]
GO
CREATE LOGIN [NT SERVICE\MSSQL$NewInstance] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO

ALTER SERVER ROLE [sysadmin] ADD MEMBER [NT SERVICE\MSSQL$NewInstance]
GO

USE [master]
GO

CREATE LOGIN [NT SERVICE\SQLAgent$NewInstance] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO

ALTER SERVER ROLE [sysadmin] ADD MEMBER [NT SERVICE\SQLAgent$NewInstance]
GO

USE [master]
GO
CREATE LOGIN [NT SERVICE\SQLTELEMETRY$NewInstance] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO

We will need to remove the old server references.

USE [master]
GO
DROP LOGIN [NT SERVICE\SQLTELEMETRY$OldInstance]
GO

USE [master]
GO
DROP LOGIN [NT SERVICE\SQLAgent$OldInstance]
GO

USE [master]
GO
DROP LOGIN [NT SERVICE\MSSQL$OldInstance]
GO

We are good to go on our new SQL Server Instance!