Gold Award Winner                            

Introduction

DBAs are responsible for developing, implementing, and periodically testing a backup and recovery plan for the databases they manage. Even in large shops where a separate system administrator performs server backups, the DBA has final responsibility for making sure that the backups are being done as scheduled and that they include all the files needed to make database recovery possible after a failure. When failures do occur, the DBA needs to know how to use the backups to return the database to operational status as quickly as possible, without losing any transactions that were committed.

Back to top


Problem definition

When DBA start a back up there are many of issues concerning permission, tape, path or corruption. We will discuss them per case wise.

CASES

Case 1: operating system error 19

Problem 1:

media is write protected.

operating system error 19 - The media is write protected.

Solution 1:

Reference Forum post:

  1. You should check your server event log, or consult your server admin or storage admin, maybe they did some change.
  2. Please check also Windows EventLog (eventvwr.msc) on the server, where the SQL Server is running; maybe it's an OS/storage issue.
  3. Refer link http://support.microsoft.com/kb/971436

Back to top


Case 2: The file or filegroup "" cannot be selected for this operation.

Problem 2:

BACKUP DATABASE bankloans
FILE = 'bank1',
FILE = 'banklog1'
TO DISK = 'E:\demo\a1.bak'

Error 2:

Msg 3219, Level 16, State 1, Line 2

The file or filegroup "banklog1" cannot be selected for this operation.

Msg 3013, Level 16, State 1, Line 2

BACKUP DATABASE is terminating abnormally.

Solution 2:

If you only have two files (eg. bank1.mdf and banklog1.ldf) you just need to do the following to perform a full database backup:



Solution 3

As discussed here: DROP all Snapshots and then perform RESTORE over the database

USE [master]
CREATE DATABASE [dbStaging_ss_20160607]
ON ( NAME = N'dbStagingDB', FILENAME = N'E:\dbStaging\dbStaging_Primary.mdf'
) AS SNAPSHOT OF [dbStagingDB]
USE [master] DROP DATABASE [dbStaging_ss_20160607]

Back to top


More info:

refer more

Case 4: SQL Backup error with Management Studio

Problem 4: Get following errors when trying to create the maintenance plan:

in windows EventLog:

EventData

DBType 15

AppNameCount 19

AppName Visual Studio 2005

VendorNameCount 10

VendorName Microsoft

SummaryCount 81

Summary Visual Studio 2005 has a known compatibility issue with this version of Windows.

SessionID 2

Are SQL Server 64-bit as well or is it 32-bit?

Try to re-register the DLL and see what happens.

Solution 4:

Run C:\Program Files\Microsoft SQL Server\90\DTS\Binn>regsvr32 dts.dll (Could be Program Files(x86)) depending on you installation base.

Case 5: tape related error

Error 1:-
Server: Msg 3013, Level 16, State 1, Line 1
The backup data at the end of 'devicename' is incorrectly formatted. Backup sets on the media might be damaged and unusable. To determine the backup sets on the media, use RESTORE HEADERONLY. To determine the usability of the backup sets, run RESTORE VERIFYONLY. If all of the backup sets are incomplete, reformat the media using BACKUP WITH FORMAT, which destroys all the backup sets.
Server: Msg 3013, Level 16, State 1, Line 1
or
Error 2:-
Msg 3266, Level 16, State 1, Line 1
The Microsoft Tape Format (MTF) soft filemark database on backup device 'devicename' cannot be read, inhibiting random access.
Server: Msg 3013, Level 16, State 1, Line 1
Backup or restore operation terminating abnormally.


Back to top


Solution 5:

More info: solution:

You must manually delete or erase the device by using the following command:

BACKUP DATABASE mydatabase TO DISK='C:\MyDatabase.bak' with FORMAT
RESTORE HEADERONLY FROM DISK='C:\MyDatabase.bak' --return 1

Back to top


Case 6: Read on"" failed: 23(Data error (cyclic redundancy check)

Problem 6:

Backup failed for Server 'Active02'. (Microsoft.Sql|Server.SmoExtended)

Additional information 6:

System.Data.SqlClient.SqlError: Read on"E:\Class Database\Class.mdf" failed: 23(Data error (cyclic redundancy check).) (Microsoft.Sql|Server.Smo)

Solution 6:

It looks like database corrupted.

Can you run DBCC CHECKDB?

SELECT * FROM msdb.dbo.suspect_pages --check suspected pages
DBCC CheckDB ('CLasss') WITH NO_INFOMSGS, ALL_ERRORMSGS

Back to top


CASE 7: Auto close property of a database

Problem 7:

Event 9001

The log for database '' is not available. Check the event log for related error messages. Resolve any errors and restart the database.

A database was autoclosed, it was restarting the database, we made it false and took backup .sucessfully completed.

Solution 7:

The errors might occur due to SQL Server not able to open the database quickly enough for the backup to complete or the database was in the middle of shutting down due to previous user activity on the database. So setting the Auto Close property of the database to false will have the SQL VDI Backups to complete successfully.

See Also:

This Article Participated in Technet Guru Competition February-2018 Jump and won Gold Medal.