Loading . . .
SQL Server Backup throws Error Message – Msg 3201 Level 16 State 1

SQL Server Backup throws Error Message – Msg 3201 Level 16 State 1

Here I want to share my experiences with you regarding SQL Server Backup and a backup error message “Msg 3201 Level 16 State 1”, sometimes the simplest things becomes the hardest;-)
But even for the simple things in life there will be always solutions which I would like to show you.

Customer order:

“Please copy the XYZ database of production in the test environment”

No problem, so pulled out my SQL Server Backup script:


SQL Server Backup SingleDB to NetworkShare

23.02.2015 BP Initial V1.0
17.04.2015 BP added Servername to Backup_Filename
21.05.2015 BP REPLACE(@@SERVERNAME, '\', '_') - named instances 


USE [master]

@database_name varchar(100),
@Network_Share varchar(512),
@backupfile_name VARCHAR(512),
@backupdate varchar(8),
@SQLCMD nvarchar(512)

-- SET Database Name
SELECT @database_name = 'Test';

-- Set Network-Path
SELECT @Network_Share = '\\Ziel_Server\Backup_Pfad'; -- without Backslash

-- generate a backup device and file name
SELECT @backupdate = CONVERT(VARCHAR(8), GETDATE(), 112);
SET @backupfile_name = @Network_Share + '\' + REPLACE(@@SERVERNAME, '\', '_') + '_' + @database_name + '_' + CONVERT(VARCHAR(8), GETDATE(), 112) + '.bak';

-- drop the backup device from last time
IF EXISTS(SELECT * FROM sys.backup_devices where name = 'Network_Share_Device')
EXEC master.dbo.sp_dropdevice @logicalname = N'Network_Share_Device';

-- create the backup device / filename
EXEC master.dbo.sp_addumpdevice @devtype = N'disk', @logicalname = N'Network_Share_Device', @physicalname = @backupfile_name;

-- backup the database to the newly created backup device / filename
SET @SQLCMD = N'BACKUP DATABASE [' + @database_name + '] TO [Network_Share_Device] WITH DESCRIPTION = N''Backing up DBNAME to a network share drive'', NOFORMAT, INIT, NAME = 
  N''Backup_DB_BkUp_to_Network_Share'', SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM, COPY_ONLY;'

print 'Just backing up ' + @database_name + ' to ' + @Network_Share;
exec sp_executesql @SQLCMD;

-- drop the backup device from last time
IF EXISTS(SELECT * FROM sys.backup_devices where name = 'Network_Share_Device')
EXEC master.dbo.sp_dropdevice @logicalname = N'Network_Share_Device';[/sql]
Aber was ist das... ich erhielt eine Fehlermeldung mit der ich so nicht gerechnet hatte
Just backing up Test to \\Ziel_Server\Backup_Pfad
Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'Network_Share_Device(\\Ziel_Server\Backup_Pfad\Ziel_Server_Instanzname_Test_20150521.bak)'. Operating system error 3(failed to retrieve text for this error. Reason: 15105).
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
Device dropped.

What? Why? The disc was empty (I had checked that)! The path was also accessible!

Ok, Google tell me… there was the following KnowlegdeBase Post… the error message says something is wrong with the disc and it should be formatted… ok, disc formatted but it didn’t help me to fix this…

I had already found another shared folder which I use to work around the backup and copy it to the destination later.

Sometimes things are so simple that you don’t think about it on the first attempt 😉

The database had now reached a size which would no longer fit the available disc space. 66 GB database on a 60GB drive – hard to imagine, but why such a meaningless error message.
My brain seems to be really lazy today, this 66 GB database should fit the disc space (normally we have enabled backup compression), here unfortunately not. Ok, quickly fixed and tried again, now the backup file is only 12 GB.
Now I was able to complete the customer order successfully.

Just backing up Test to \\Ziel_Server\Backup_Pfad
10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
Processed 8289352 pages for database 'Test', file 'Test' on file 1.
100 percent processed.
Processed 1113 pages for database 'Test', file 'Test_log' on file 1.
BACKUP DATABASE successfully processed 8290465 pages in 562.126 seconds (115.221 MB/sec).
Device dropped.

What do we learn?

  • always enable backup compression
  • Sometimes think of database size in case of a not running backup 😉

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Backup Button Previous post TSQL #66 – Monitoring Backup Solution – Backup History
SQL Saturday #409 Rheinland Next post SQL Saturday #409 – PASS Rheinland – Speaker List announced

SQL from Hamburg

Das bin ich ;-)

Björn Peters - MVP - Data Platform
I had to do with MS SQL databases for the first time in 2000 and looked after these database systems for around seven years. From 2007 to 2019, I was employed as a database administrator and looked after many different SQL servers from medium-sized companies and large corporations from different industries.
Although I have some certificates, I get my insights and knowledge about the SQL Server purely from day-to-day business, reading / following numerous forums/blogs.
I'm not specialized in any topic, but I focus mostly on performance analysis.
Since the end of 2016, I have been the Azure Meetup Hamburg organizer and from April 2017 to June 2018, Cloud and Data Center Management MVP, and since July 2018, Data Platform MVP.