다음을 통해 공유

SQL Server DBA: Backup error and solutions

  • 아티클

      https://msdnshared.blob.core.windows.net/media/2016/05/0640_NinjaAwardTinyGold.png                            

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.

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.