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.
“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 😉
Björn works in Hamburg as Senior Consultant – Microsoft Data Platform and Cloud at Kramer & Crew. He regularly participates in the PASS regional group meeting in Hamburg, the events of the PASS such as SQLSaturday and DataGrillen and organises the Azure Meetup in Hamburg. He is interested in topics such as SQL Server, Powershell and Azure for science fiction, snowboarding, baking and cycling.