Ошибка backup failed for server


      Gold Award Winner
                           

Table of Contents

  • Introduction
  • Problem definition
  • CASES
    • Case 1: operating system error 19
      • Problem 1:
      • Solution 1:
    • Case 2: The file or filegroup «» cannot be selected for this operation.
      • Problem 2:
      • 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:
      • More info:
    • Case 4: SQL Backup error with Management Studio
      • Solution 4:
    • Case 5: tape related error
      • Solution 5:
    • Case 6: Read on»» failed: 23(Data error (cyclic redundancy check)
      • Problem 6:
      • Additional information 6:
      • Solution 6:
  • CASE 7: Auto close property of a database
    • Problem 7:
  • See Also:

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:demoa1.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:dbStagingdbStaging_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 FilesMicrosoft SQL Server90DTSBinn>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 DatabaseClass.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:

  • Error message for backup on disk and tape
  • Restore failed with Data error (cyclic redundancy check)
  • SQL Error? The operation cannot be performed on a database with database snapshots or active DBCC replicas
  • SQL Server Troubleshooting: Logon failure — the user has not
    been granted the requested logon type at this computer

  • Remove From My Forums
  • Question

  • I cannot backup my SQL database, following are the information I obtained from the Microsoft SQL (2005 Standard Edition) Server Management Studio after the backup was failed.

    >Backup failed for Server ‘SRV-A300’.  (Microsoft.SqlServer.Smo)

    Set property Devices to accomplish this action. (Microsoft.SqlServer.Smo)<

    -I set backup destination to local disk.

    -I would like to start a fresh full backup. Thanks

Answers

  • Hello,

    Please, make sure you specify a file name when backing up to disk. There’s no default file name for backups.

    Regards,

    Alberto Morillo
    SQLCoffee.com

    • Marked as answer by

      Thursday, August 20, 2009 2:57 PM

Can any body tel me how to overcome this error when taking a backup in SQL Server 2008 Express?

Backup failed for Server 'SQLEXPRESS'.  (Microsoft.SqlServer.SmoExtended)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1447.4+((KJ_RTM).100213-0103+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Backup+Server&LinkId=20476

System.Data.SqlClient.SqlError: The media loaded on "D:Project 2014PayrollBackup" is formatted to support 2 media families, but 1 media families are expected according to the backup device specification. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1447.4+((KJ_RTM).100213-0103+)&LinkId=20476

I just want to take a single backup of my database. But always I have to give two destinations and then when restoring I cant restore the database using a single backup file? Is there anything to configure in SSMS? If any, could you please clearly explain?

EDIT:

BACKUP DATABASE [Payroll] TO  DISK = N'D:Project 2014PayrollBackup' WITH NOFORMAT, NOINIT,  NAME = N'Payroll-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

asked Aug 1, 2014 at 17:58

CAD's user avatar
                           

Table of Contents

  • Introduction
  • Problem definition
  • CASES
    • Case 1: operating system error 19
      • Problem 1:
      • Solution 1:
    • Case 2: The file or filegroup «» cannot be selected for this operation.
      • Problem 2:
      • 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:
      • More info:
    • Case 4: SQL Backup error with Management Studio
      • Solution 4:
    • Case 5: tape related error
      • Solution 5:
    • Case 6: Read on»» failed: 23(Data error (cyclic redundancy check)
      • Problem 6:
      • Additional information 6:
      • Solution 6:
  • CASE 7: Auto close property of a database
    • Problem 7:
  • See Also:

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:demoa1.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:dbStagingdbStaging_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 FilesMicrosoft SQL Server90DTSBinn>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 DatabaseClass.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:

  • Error message for backup on disk and tape
  • Restore failed with Data error (cyclic redundancy check)
  • SQL Error? The operation cannot be performed on a database with database snapshots or active DBCC replicas
  • SQL Server Troubleshooting: Logon failure — the user has not
    been granted the requested logon type at this computer

  • Remove From My Forums
  • Question

  • I cannot backup my SQL database, following are the information I obtained from the Microsoft SQL (2005 Standard Edition) Server Management Studio after the backup was failed.

    >Backup failed for Server ‘SRV-A300’.  (Microsoft.SqlServer.Smo)

    Set property Devices to accomplish this action. (Microsoft.SqlServer.Smo)<

    -I set backup destination to local disk.

    -I would like to start a fresh full backup. Thanks

Answers

  • Hello,

    Please, make sure you specify a file name when backing up to disk. There’s no default file name for backups.

    Regards,

    Alberto Morillo
    SQLCoffee.com

    • Marked as answer by

      Thursday, August 20, 2009 2:57 PM

Can any body tel me how to overcome this error when taking a backup in SQL Server 2008 Express?

Backup failed for Server 'SQLEXPRESS'.  (Microsoft.SqlServer.SmoExtended)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1447.4+((KJ_RTM).100213-0103+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Backup+Server&LinkId=20476

System.Data.SqlClient.SqlError: The media loaded on "D:Project 2014PayrollBackup" is formatted to support 2 media families, but 1 media families are expected according to the backup device specification. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1447.4+((KJ_RTM).100213-0103+)&LinkId=20476

I just want to take a single backup of my database. But always I have to give two destinations and then when restoring I cant restore the database using a single backup file? Is there anything to configure in SSMS? If any, could you please clearly explain?

EDIT:

BACKUP DATABASE [Payroll] TO  DISK = N'D:Project 2014PayrollBackup' WITH NOFORMAT, NOINIT,  NAME = N'Payroll-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

asked Aug 1, 2014 at 17:58

CAD's user avatar

CADCAD

4,0526 gold badges28 silver badges47 bronze badges

1

The backup statement is using the NOINIT clause. This causes each successive backup to append to the existing backup file. You are attempting to backup to one media set (one file), but it appears the existing backup you are attempting to append to consisted of two media sets (two files).

For a quick turnaround, specify a new filename:
BACKUP DATABASE [Payroll] TO DISK = N'D:Project 2014<insert new filename>' WITH ...

answered Aug 1, 2014 at 18:27

Dave Mason's user avatar

Dave MasonDave Mason

4,7262 gold badges23 silver badges24 bronze badges

The issue for me was there were two sources in the Destination.

enter image description here

After you backup with only one destination, removing the other. You don’t get the error anymore.

Let me know if this helps!

answered Apr 27, 2020 at 6:31

vba's user avatar

vbavba

4988 silver badges19 bronze badges

1

I also received the error. In my case, I wanted to specify a different location to which the file should be backed up to. But what it was trying to do, was to now split the backup to both files in both locations — thus the part of the error:

is formatted to support 1 media families, but 2 media families are expected according to the backup device specification. (Microsoft.SqlServer.Smo)

So, remove one of the destinations specified in the ‘Destination’ section of the Back Up Database wizard. It should only have one destination if it only backed up to one file previously.

meJustAndrew's user avatar

meJustAndrew

5,8718 gold badges50 silver badges76 bronze badges

answered Apr 14, 2016 at 9:27

Kemono's user avatar

KemonoKemono

611 silver badge3 bronze badges

I got the same error with my project, so I did a couple of simple things:

  1. Copied my old MyDB.bak file from the default backup path, say C:Program FilesMicrosoft SQL ServerMSSQL14.SQLEXPRESSMSSQLBackup
  2. Removed it from there
  3. Backed up new version of DB to the same path. (It now succeeded)
  4. Copied the newly created backup to desired place.

DuDa's user avatar

DuDa

3,7184 gold badges16 silver badges36 bronze badges

answered Jan 11, 2021 at 22:47

Arthur's user avatar

When I was attempting to restore a backup onto another server, I got the same error that you got.

I was able to get past this error by deleting the «Backup Device» object and recreating a new one. As you know, the Backup Device is an object that saves a path to your backup folder and to the backup file. Apparently, it also saves other information like the media family.

In the SSMS interface, expand the Server Objects section, and then the Backup Devices section. Find the named backup device for the database and path that you are getting the error on. Delete that object and recreate it. That should take care of the issue.

Now, just so we are clear, I’m NOT talking about deleting the backup itself. I’m NOT talking about deleting or removing any drives. I’m NOT talking about deleting all of the backup devices listed on your server. I’m simply talking about deleting the backup device (which is an object) for one database on your server.

answered Feb 16, 2015 at 15:47

Jeff Pfau's user avatar

1

I received this message when I was adding a database to Always On Group and log backup failed with this message:

‘The Backup cannot be performed because the existing media set is formatted with an incompatible version’

Problem: I was using the same backup shared folder as I was using two servers with multiple instances. Database was promoted from SQL Server 2008 R2 to SQL Server 2016. I did not delete the old 2008 R2 backups from that shared folder.

Solution: Try and clear out the old version backups and try again. It worked for me.

answered Jan 12, 2018 at 10:26

user9208516's user avatar

Summary

This Error Message Article explains the SQL Management Studio error message “Backup failed for Server ‘[server name]’.  (Microsoft.SqlServer.SmoExztended) Additional information: System.Data.SqlCLient.SqlError: Cannot use the ‘[backup file and file location]’ because it was originally formatted with sector size [sector size] and is now on a device with sector size [sector size] (Microsoft.SqlServer.Smo)” and details how to fix the underlying problem that caused it to appear.

Explanation

A backup file is attempting to be saved over another existing backup file. When the message appears, the original file needs to be removed before the new file can be saved. This probably is due to some change in the original file where SQL does not recognize it as the old file.

In the case where the message appeared, the database structure had been changed and it is possible a different version of SQL was used to generate the file. Either of those may have been the reason that SQL would not save the new backup over the old one.

Solution

A good workaround is to select ‘backup to a new media set’.  It seemed to solve the problem. In this situation, it was saving over a backup of a newer version of SQL.

Error First Appeared: Windows 8 running Microsoft SQL Management Studio 2008

Other Helpful Links:

http://stackoverflow.com/questions/24285195/sql-server-2012-express-backup-database-failure

http://stackoverflow.com/questions/22448818/backup-a-database-on-a-hdd-with-a-different-sector-size

Dave Klement

Principal at Efficient Business Systems

I’m trying to do a simple backup to an external disk and getting this error:

Backup failed for Server ‘Server-name’

Additional information:

System.Data.SqlClient.SqlError: The media loaded on «C:Program FilesMicrosoft SQL..» is formatted to tsupport 1 media families, but 2 media families are expected according to the backup device specification.
(Microsoft.SqlServer.Smo)`

Backup type: Full

Why am I getting this error and why is the message pointing to C:Program Files.. if the destination file is an external device?

Aaron Bertrand's user avatar

Dave Klement

Principal at Efficient Business Systems

I’m trying to do a simple backup to an external disk and getting this error:

Backup failed for Server ‘Server-name’

Additional information:

System.Data.SqlClient.SqlError: The media loaded on «C:Program FilesMicrosoft SQL..» is formatted to tsupport 1 media families, but 2 media families are expected according to the backup device specification.
(Microsoft.SqlServer.Smo)`

Backup type: Full

Why am I getting this error and why is the message pointing to C:Program Files.. if the destination file is an external device?

Aaron Bertrand's user avatar

asked Aug 31, 2012 at 16:33

loki's user avatar

2

This error means that you «striped» your backup across multiple files, and you’re only providing one of the files when you’re trying to restore.

If you’re backing up using SQL Server Management Studio, make sure that you click «remove» to remove any existing backup destinations before you «add» the file you want to back up to.

answered Sep 9, 2012 at 18:54

To avoid above error, just replace your new backup location with listed backup locations. Means remove all old entry and add your new backup location. It will work.

answered Mar 25, 2017 at 5:41

Shakul's user avatar

1

Возможно, вам также будет интересно:

  • Ошибка b210d митсубиси лансер 10
  • Ошибка b1c2d митсубиси lancer 10
  • Ошибка b2aaa форд мондео 4
  • Ошибка backend error network error
  • Ошибка b2109 ниссан теана j32

  • Понравилась статья? Поделить с друзьями:
    0 0 голоса
    Рейтинг статьи
    Подписаться
    Уведомить о
    guest

    0 комментариев
    Старые
    Новые Популярные
    Межтекстовые Отзывы
    Посмотреть все комментарии