Restore master database on a cluster instance

Below is the sequence for restoring master database on a cluster instance

1. Take SQL Server Service Offline from Cluster Admin
2. Login to the active node of the cluster
3. Start the SQL Server service in single user mode
   

   net start "SQL Server (InstanceName)" /c /m /T3608

4. Login to the instance using sqlcmd
5. RESTORE master database
   

   RESTORE DATABASE master FROM DISK = '<BackupLocation>' WITH REPLACE
   

6. Once the master database is restored, the SQL Service will be shutdown automatically
7. Start SQL Server Service from Cluster Admin

Restore fails with error "The media set has 2 media families but only 1 are provided. All members must be provided."

When you try to restore a backup, it might fail with the below error.

Msg 3132, Level 16, State 1, Line 1  The media set has 2 media families but only 1 are provided. All members must be provided.  Msg 3013, Level 16, State 1, Line 1  RESTORE DATABASE is terminating abnormally.

This means that the backup file provided for restore is not a complete one.

Okay, now what does "is not a complete one" mean?

This error pops up when the database is backed up into different files using the split backup technique.
In this case, the database backup was split into 2 files and while restoring the database only one file was mentions.
To fix this issue, you have to specify the complete list of backup files which were part of the backup procedure.

Split backups in SQL Server

Split backup is a method of performing the backups on a SQL Server database to multiple files.
When we perform the split backups on a database, the SQL server engine creates multiple backup files with the size split into the number of files mentioned in the backup command.

BACKUP DATABASE [SansSQL] TO  
 DISK = N'D:\Backup\SansSQL_Part1_Backup.bak' 
   ,DISK = N'D:\Backup\SansSQL_Part2_Backup.bak' 
WITH INIT, STATS = 10
GO

When you execute backup command like above then the backup of that particular database will be split into 2 different files of almost equal size.
This can be used with Full, Differential and Log backups as well.

Kill all connections to a database in SQL Server

The easy and quick way to kill all connection to a SQL Server database is by setting the database to single user mode with ROLLBACK IMMEDIATE option.
This option will kill all the users immediately before setting the database to single user mode.
This will come handy while restoring a database and users are connected to it.

USE master
GO
ALTER DATABASE <DatabaseName>
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE <DatabaseName>
SET MULTI_USER
GO

How to invoke a SQL job from another Job present on a different instance

For invoking an SQL Server Job from another Job which is present on a different instance, we have different ways like

1. Create a Linked server and use msdb..sp_start_job to start the job
2. Using xp_cmdshell
3. Using SQLCMD Operating system command

In most of the SQL Server instances the xp_cmdshell will be disable due to security reasons and creating linked server is time consuming.