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.