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]
GO

DECLARE
@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 😉