Troubleshoot SQL Server Backup Error Msg 3231, Level 16, State 1

This blog troubleshoots SQL Server Error Msg 3231, Level 16, State 1 while backing up SQL Database. To understand the error, let’s reproduce the error message.

Take a full backup of the database in a single file.

BACKUP DATABASE DBBlogger TO DISK = 'DBBlogger.bak'
GO

Take a differential backup and split the database backups.

BACKUP DATABASE DBBlogger 
TO DISK = 'DBBlogger.bak',
  DISK = 'DBBlogger_1.bak'
WITH DIFFERENTIAL

We get the error because the backup file [DBBlogger.bak] already exists, and if we try to take a differential backup with a similar filename using split backups, it gives an error message.

The solution is to use different file names or run the backup with FORMAT, INIT.

• FORMAT: The format keyword specifies the backup to write a new media header for the backup operation.
• INIT: It specifies that the backup set should be overwritten with preserving media header.

Let’s try again running the backup with FORMAT and INIT options.

BACKUP DATABASE DBBlogger TO DISK = 'DBBlogger.bak'
GO
BACKUP DATABASE DBBlogger 
TO DISK = 'DBBlogger.bak',
  DISK = 'DBBlogger_1.bak'
WITH DIFFERENTIAL, FORMAT, INIT