Full Database Backup in DB13 for MS SQL Server databases

[Former Member]

Hello,

We have some SAP systems using the MS SQL Server database. I want to know if it is possible to setup the Full Database Backup option in transaction DB13 to store the files in a hard disk space.

I already did this type of configuration in SAP systems in Oracle databases and for this I used the init<SID>.sap where we can put all the configuration, for instance the disk path where we want to save the backup files! But for MS SQL Server databases, in DB13, I don´t know how to do that, when I add the "Full Database Backup" option in DB13, this show me some options but only for tape, but I don´t want to store the backup files in tapes, I want to store/save the files on the server disk itself or else to an external disk but I don´t see where I can set this way of storage!

This is what you see in DB13 (when using a MS SQL Server database) in "Full Database Backup" and as you see I only have options for TAPE not for a disk or other type of storage! How can I do that? (if that is possible):

Can you help me please?

Kind regards,

samid raif

[former_member185239 · Accepted solution]

Hi Samid,

You need to create a new device with the help of MS SQL Server management and the destination will be of file type.

The newly created device type will be available in backup device type.

Follow the below links

http://scn.sap.com/thread/692767

With Regards

Ashutosh Chaturvedi

[Former Member]

Create a backup device from SQL Server management studio. See the below image:

Give a proper device name and in file option use the file (.bak) location for backup device.  Then it will appear into DB13 option.

Asad

[Former Member]

Samid,

This is discussed in the forum many times.

Just a search can do type change in Studio and configure it via DB13.

Regards,

Raja. G

[Former Member]

Hello,

Hum Ok! I read that post, I created a new device type in SQL Management Studio as it described in the link that you recommended, but when I set a new task "Full Database Backup" in DB13, it stopped with an error:

The error in DB13:

Type:       TSQL

DB-Name:    L01

For Run:    20140401 22:52:42

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

Stepname:   CCMS-step 1

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

"L01",@r3Db="L01",@bDev="backup L01",@expDays= 27,@jobName= "SAP CCMS

            Full DB Backup of L01 [20140401225242-2-225242]",@bkupChecksum="Y",@bk

upCompress="N",@bkupType="F",@nativeBkup="N",@exeDate = "2014040122524

            2",@bkupSim = "N",@format = 0,@init = 0,@bkupDb = "L01",@unload = 0,@e

            xeStmt = @exeStmt OUTPUT

Status:     (failure)

Message:    2528

Severity:                                                                                                                                                                                                                                                   15

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

Last msg:   Executed as user: NOESIS\SAPWT03$. Incorrect syntax near the keyword '

            backup'. [SQLSTATE 42000] (Error 156)  Incorrect syntax near the keywo

            rd 'with'. If this statement is a common table expression, an xmlnames

            paces clause or a change tracking context clause, the previous stateme

            nt must be terminated with a semicolon. [SQLSTATE 42000] (Error 319)

            DBCC execution completed. If DBCC printed error messages, contact your

             system administrator. [SQLSTATE 01000] (Error 2528).  The step failed

            .

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

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

Any tips? Can you help me please?

Kind regards,

samid raif

[Former Member]

Chk this out..try to search the error code..

Regards,

Raja. G

[Sriram2009]

Hi Samid

Kindly refer the SCN link

And refer the SAP Note for Backup /Restore strategy

1297986 - Backup and Restore strategy for MS SQL Server

Regards

Sriram

[Former Member]

HI,

We have a client who is taking backup through db13 on LTO 5 standalone tape. Backup of 1.4 TB complete in 1hr 30Min but as per tape write speed which is 140 MBPS  it should take around 3.5 hrs to complete the job.

So is it possible to achieve the same if yes then it will be grateful if someone can explain the same.

Regards

Mitt Gori

[Former Member]

Hello Raja

Sorry for the delay of my answer! Many thanks for that tip, it helps a lot and it solve the last error/problem that I reported here, in fact, the problem was we had named the device with two words containing a space. So we removed that space from the backup device (in SQL management studio) with one word only and the job runs successfully without any errors!

It works in Development and Quality systems but in our Production system (AM1) the Full backup option in DB13 stopped with the following error, in fact the error is on verification of backup, it means that the Full backup finished with success, but when it does the backup verification, the job in DB13 stops with an error, the following one:

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

Jobname:    SAP CCMS Full DB Backup of AM1 [20140417103726-4-103726]

Type:       TSQL

DB-Name:    AM1

For Run:    20140417  10:37:26

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

Stepname:   CCMS-step 1

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

            "AM1",@r3Db="AM1",@bDev="fullprd",@expDays= 27,@jobName= "SAP CCMS Ful

            l DB Backup of AM1 [20140417103726-4-103726]",@bkupChecksum="Y",@bkupT

            ype="F",@nativeBkup="N",@exeDate = "20140417103726",@bkupSim = "N",@fo

            rmat = 0,@init = 0,@bkupDb = "AM1",@unload = 0,@exeStmt = @exeStmt OUT

            PUT

Status:     (success)

Message:    3014

Severity:                                                                                                                                                                                                                                                    0

Duration:   4  hours(s)  41 min(s) 22 sec(s)

Last msg:   Executed as user: am1. Processed 7434328 pages for database 'AM1', fil

            e 'A01DATA1' on file 1. [SQLSTATE 01000] (Message 4035)  Processed 315

            1504 pages for database 'AM1', file 'A01DATA2' on file 1. [SQLSTATE 01

            000] (Message 4035)  Processed 4574152 pages for database 'AM1', file

            'A01DATA3' on file 1. [SQLSTATE 01000] (Message 4035)  Processed 44363

            92 pages for database 'AM1', file 'A01DATA4' on file 1. [SQLSTATE 0100

            0] (Message 4035)  Processed 25598 pages for database 'AM1', file 'A01

            LOG1' on file 1. [SQLSTATE 01000] (Message 4035)  BACKUP DATABASE succ

            essfully processed 19621974 pages in 16881.638 seconds (9.521 MB/sec).

             [SQLSTATE 01000] (Message 3014).  The step succeeded.

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

Stepname:   CCMS-step 2

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

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

            = "20140417103726",@unload = 1,@dbCnt =1,@exeStmt = @exeStmt OUTPUT

Status:     (failure)

Message:    3201

Severity:                                                                                                                                                                                                                                                   16

Duration:   0  hours(s)  8  min(s) 30 sec(s)

Last msg:   Executed as user: am1. Cannot open backup device 'fullprd(\\10.0.0.45\

            backupsap\prd\prdfullqua.bak)'. Operating system error 1265(error not

            found). [SQLSTATE 42000] (Error 3201).  The step failed.

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

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

Can you help me please with this one!? Can you tell me why in verification it stops with that error?

Kind regards,

samid raif

[Sriram2009]

Hi Samid

Last msg:   Executed as user: am1. Cannot open backup device 'fullprd(\\10.0.0.45\
            backupsap\prd\prdfullqua.bak)'. Operating system error 1265(error not
            found). [SQLSTATE 42000] (Error 3201).  The step failed.
                    <------------- End of Job Step History --------------->

Could you check the user AM1 having access of the share folder (\\10.0.0.45\backupsap)?

Regards

Sriram

[Former Member]

Hi Sriram,

What is very strange is that...! Because AM1 is not an operation system user (local/domain) is a specific database user (MS SQL Server) and because of that I can´t give any access to AM1 user to the shared folder (\\10.0.0.45\backupsap)!

In SQL management studio in Login option I see the following information about AM1 user:

In Database properties of AM1 database I see the following information about am1 user:

Any tips? Can you help me please?

Thank you,

samid raif

[Sriram2009]

Hi

Could you refer the Microsoft link You cannot back up databases to a network drive if your account have not sufficient permissions to ...

Regards

Sriram