Troubleshoot SQL Server Backup Error Msg 3132, Level 16

This blog troubleshoots SQL Server Error Msg 3132, Level 16, State 1 while backing up SQL Database.

To understand the error, let’s reproduce the error message.

Start a full backup of the database in multiple files.

BACKUP DATABASE DBBlogger TO DISK = 'DBBlogger_1.bak',
                            DISK = 'DBBlogger_2.bak',
                            DISK = 'DBBlogger_3.bak'
GO

If we need to restore a SQL Database backup with split files, we require all files to restore successfully. If we try to restore the database without all files, we get the error message -

USE [master]
RESTORE DATABASE [DBBlogger_1] FROM 
DISK = N'C:\SQL\DBBlogger_1.bak'
WITH  FILE = 1, 
MOVE N'DBBogger' TO N'C:\SQL\DBBogger.mdf', 
MOVE N'DBBogger_log' TO N'C:\SQL\DBBogger_log.ldf', NOUNLOAD,  STATS = 5

Error:Msg 3132, Level 16, State 1, Line 5

The media set has 3 media families, but only 1 is provided. All members must be provided.

Msg 3013, Level 16, State 1, RESTORE DATABASE is terminating abnormally.

You need to specify all backup files and SQL Database restore works fine.

USE [master]
RESTORE DATABASE [DBBlogger_1] FROM 
DISK = N'C:\SQL\DBBlogger_1.bak', 
DISK = N'C:\SQL\DBBlogger_2.bak', 
DISK = N'C:\SQL\DBBlogger_3.bak' 
WITH  FILE = 1, 
MOVE N'DBBogger' TO N'C:\SQL\DBBogger.mdf', 
MOVE N'DBBogger_log' TO N'C:\SQL\DBBogger_log.ldf', NOUNLOAD,  STATS = 5

You can use T-SQL to query the MSDB database and list database backups. The family_sequnce_number determines the backups required for a backup set.

DECLARE @DatabaseName NVARCHAR(max)
SET @DatabaseName = N'DBBlogger'
USE msdb;
SELECT DISTINCT d.NAME
   ,bmd.family_sequence_number
   ,bmd.physical_device_name
   ,b.type AS [type]
   FROM sys.databases d
INNER JOIN backupset b ON (b.database_name =d.NAME)
LEFT JOIN backupmediaset t5 ON (b.media_set_id = t5.media_set_id)
LEFT JOIN backupmediafamily bmd ON (bmd.media_set_id = t5.media_set_id)
WHERE (d.NAME = @DatabaseName)
order by bmd.physical_device_name;