Unable to take Backup to tape MS-SQL

[Former Member]

Hi Experts,

We are trying to take our production server backup to tape but getting some errors. I checked with our Hardware team they were telling that the tape drive is ok and it compatible with the Hardware and Operating System. We are facing this issue after migration(HW change G8 Server) and upgrade of SAP System to EHP7. So they are suspecting that the issue is with the SQL Server or with the SAP.

OS : Windows Server 2012

DB : SQL Server 2008 R2

SAP : EHP 7 SPS 5(BASIS 741 07)

We have attached the error logs generated while taking the backup through DBACOCKPIT. Executed as user: prd. Write on "\\.\tape0" failed: 1117(The request could not be performed because of an I/O device error.)

Please help us to solve the issue. Do we need any other configuration for tape backups?

Thanks,

Krishna G.

Error.txt.zip

[former_member182307]

Hello,

Have you tried to create a new backup device in you SQLServer instance pointing to this tape device ( ie not using the default R3DUMPx devices provided afetr installation ) ?

You can do it using the SQLServer management studio.

And then try to take the backup from here.

Best regards,

Steve.

[Former Member]

Hi Steve,

We already tried this option by creating backup device for tape but faced the same problem with that one to.

Thanks,

Krishna G.

[Sriram2009]

Hi Krishna

Tape drive is connected in internal or external? I think you tape device is connected external, have you restart the tape device?

If it is internal, if possible just do the full system restart and then try the backup process ..

Regards

SS

[Former Member]

Hi Sriram,

First of all sorry for the delay in my response. We have planned to restart the server and tape tomorrow. But present we are getting error. It seems that the backup of the master and msdb databases are successful. please suggest.

***************************** SQL Job information   ******************************

Jobname:    SAP CCMS Full DB Backup of master,msdb,PRD [20151103155719-2-155719]

Type:       TSQL

DB-Name:    PRD

For Run:    20151103  15:57:19

**************************** Job history information  *****************************

Stepname:   CCMS-step 1

Command:    declare @exeStmt nvarchar(2000) exec prd.sap_backup_databases @dbList=

            "master,msdb,PRD",@r3Db="PRD",@bDev="R3DUMP0",@expDays= 27,@jobName= "

            SAP CCMS Full DB Backup of master,msdb,PRD [20151103155719-2-155719]",

            @bkupChecksum="Y",@bkupCompress="Y",@bkupType="F",@nativeBkup="N",@exe

            Date = "20151103155719",@bkupSim = "N",@format = 1,@init = 1,@bkupDb =

             "master",@unload = 0,@exeStmt = @exeStmt OUTPUT

Status:     (success)

Message:    3014

Severity:                                                                                                                                                                                                                                                    0

Duration:   0  hours(s)  0  min(s) 22 sec(s)

Last msg:   Executed as user: prd. Processed 376 pages for database 'master', file

             'master' on file 1. [SQLSTATE 01000] (Message 4035)  Processed 4 page

            s for database 'master', file 'mastlog' on file 1. [SQLSTATE 01000] (M

            essage 4035)  BACKUP DATABASE successfully processed 380 pages in 15.7

            84 seconds (0.187 MB/sec). [SQLSTATE 01000] (Message 3014).  The step

            succeeded.

                    <------------- End of Job Step History --------------->

Stepname:   CCMS-step 2

Command:    declare @exeStmt nvarchar(2000) exec prd.sap_verify_backups @nativeBku

            p = "N",@bkupSim = "N",@bDev = "R3DUMP0",@bkupChecksum = "Y",@exeDate

            = "20151103155719",@format_init = 1,@unload = 0,@dbCnt =1,@exeStmt = @

            exeStmt OUTPUT

Status:     (success)

Message:    3262

Severity:                                                                                                                                                                                                                                                    0

Duration:   0  hours(s)  0  min(s) 4  sec(s)

Last msg:   Executed as user: prd. The backup set on file 1 is valid. [SQLSTATE 01

            000] (Message 3262).  The step succeeded.

                    <------------- End of Job Step History --------------->

Stepname:   CCMS-step 3

Command:    declare @exeStmt nvarchar(2000) exec prd.sap_backup_databases @dbList=

            "master,msdb,PRD",@r3Db="PRD",@bDev="R3DUMP0",@expDays= 27,@jobName= "

            SAP CCMS Full DB Backup of master,msdb,PRD [20151103155719-2-155719]",

            @bkupChecksum="Y",@bkupCompress="Y",@bkupType="F",@nativeBkup="N",@exe

            Date = "20151103155719",@bkupSim = "N",@bkupDb = "msdb",@unload = 0,@e

            xeStmt = @exeStmt OUTPUT

Status:     (success)

Message:    3014

Severity:                                                                                                                                                                                                                                                    0

Duration:   0  hours(s)  0  min(s) 18 sec(s)

Last msg:   Executed as user: prd. Processed 2248 pages for database 'msdb', file

            'MSDBData' on file 2. [SQLSTATE 01000] (Message 4035)  Processed 1 pag

            es for database 'msdb', file 'MSDBLog' on file 2. [SQLSTATE 01000] (Me

            ssage 4035)  BACKUP DATABASE successfully processed 2249 pages in 14.8

            22 seconds (1.185 MB/sec). [SQLSTATE 01000] (Message 3014).  The step

            succeeded.

                    <------------- End of Job Step History --------------->

Stepname:   CCMS-step 4

Command:    declare @exeStmt nvarchar(2000) exec prd.sap_verify_backups @nativeBku

            p = "N",@bkupSim = "N",@bDev = "R3DUMP0",@bkupChecksum = "Y",@exeDate

            = "20151103155719",@format_init = 1,@unload = 0,@dbCnt =2,@exeStmt = @

            exeStmt OUTPUT

Status:     (success)

Message:    3262

Severity:                                                                                                                                                                                                                                                    0

Duration:   0  hours(s)  0  min(s) 3  sec(s)

Last msg:   Executed as user: prd. The backup set on file 2 is valid. [SQLSTATE 01

            000] (Message 3262).  The step succeeded.

                    <------------- End of Job Step History --------------->

Stepname:   CCMS-step 5

Command:    declare @exeStmt nvarchar(2000) exec prd.sap_backup_databases @dbList=

            "master,msdb,PRD",@r3Db="PRD",@bDev="R3DUMP0",@expDays= 27,@jobName= "

            SAP CCMS Full DB Backup of master,msdb,PRD [20151103155719-2-155719]",

            @bkupChecksum="Y",@bkupCompress="Y",@bkupType="F",@nativeBkup="N",@exe

            Date = "20151103155719",@bkupSim = "N",@bkupDb = "PRD",@unload = 0,@ex

            eStmt = @exeStmt OUTPUT

Status:     (failure)

Message:    3013

Severity:                                                                                                                                                                                                                                                   16

Duration:   1  hours(s)  25 min(s) 55 sec(s)

Last msg:   Executed as user: prd. Write on "\\.\tape0" failed: 1117(The request c

            ould not be performed because of an I/O device error.) [SQLSTATE 42000

            ] (Error 3202)  Operation on device 'R3DUMP0(\\.\tape0)' exceeded retr

            y count. [SQLSTATE 42000] (Error 3230)  Unable to unload one or more t

            apes. See the error log for details. [SQLSTATE 42000] (Error 3213)  BA

            CKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013)

            .  The step failed.

                    <------------- End of Job Step History --------------->

**************************** Job history information  *****************************

Thanks.

[former_member182307]

The strange thing for your PRD backup is :

Duration:   1  hours(s)  25 min(s) 55 sec(s)

Are you sure the backup tape type can handle the dump.

Is there enouch space on it. Because It' s like it is writing something on it and then  after these 1h25mins it stops.

What kind of tapes are you using?

What are the characteristics ?

How much space is the PRD DB using ?

Thanks & BR,

Steve.

[Former Member]

Hi Steve,

Thanks for the quick reply.

1. We are using HP LTO Ultrium 3 tape

2. this is the reply from our network team on the tape size - 400 GB Normal and 800 GB on compression mode(1:2)

3. The size of the DB is 320GB with around 75% fill size.

Thanks,

Krishna G.

[former_member182307]

Hi,

Can you check the hardware compression of your drive is activated ?

Also can you look into the servers event viewer if teher is any error related to this backup at the time the backup is interrupted.

One last thing : can you take a test backup of the PRD db only, with Init / Format tape and compression flags selected to this drive ( on a blank and new tape ) ?

Thanks & BR,

Steve.

[Sriram2009]

Hi Krishna

1.  Have you restart the system?

2. From Windows server OS have you able to access or run the backup?

Regards

SS