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


Backup Error message Msg 3231

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

SQL Database backup backup with FORMAT and INIT options

29 views0 comments