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.

Uninstalling SQL Server Service Pack or Hotfix

Prior to SQL Server 2008, if you had to rollback a Service pack or hotfix installation, it was a long and hectic process as it involves rebuilding the SQL Server and restoring the user and system databases and setting up jobs, logins,etc... And in the meantime if you have missed backing up something then you are gone!
Starting from SQL Server 2008, Service Pack 1, microsoft has introduced an option to uninstall the service pack or an hotfix. It is as simple as uninstalling an application from your computer.
To uninstall an SQL Server service pack or hotfix,
Go to "Control panel" --> "Uninstall a Program" --> Click on "view installed updates" on left hand side
Choose the update you want to uninstall and click "Uninstall" option.

How to Restore model and msdb database

Unlike master database, restoring model and msdb is simple and follows the same procedure as restoring any other user database. However we have to be very cautious while restoring system databases as it will have sensitive data which is important for SQL Server to function without any issues.

How to Restore Master database

Before restoring master database, make sure

1. The build versions of the both instances (Current instance and the instance where backup is generated) should be the same.
• More Information at http://www.sanssql.com/2013/09/the-backup-of-system-database-on-device.html

The backup of the system database on the device <backupPath> cannot be restored because it was created by a different version of the server <version> than this server <version>

While I was trying to restore the backup of master database taken in SQL Server 2008 on to an SQL Server 2008 R2 instance, I was presented with the below error.

Msg 3168, Level 16, State 1, Line 1 The backup of the system database on the device D:\Backup\master.bak cannot be restored because it was created by a different version of the server (10.00.1600) than this server (10.50.1600). Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.