Backups for SAP,SQLSERVER

[Former Member]

Hi Gurus,

I want to know the some information related to backups in SAP and SQLSERVER also.

I have installed solman,ecc,ep on windows 2003 and database is sqlserver 2005.

Till now I am taking full database(offline) backup weekly once through SQLSERVER MANAGEMENT STUDIO.

But I have not scheduled any online backups through DB13 moreover I want to take file system and transaction log backups also.

As I am new to this backups please let me know the following queries.

1)What strategy(backup) should I need to follow in order to take database and log and files also,please give me suggestion.

2)As per my knowledge we have to adjust some parameters related to backup in the file initsid.sap,but in ms sqlserver I am not able to find initsid.sap,please suggest me where can I find this file.

3)In sqlserver where can I find brtools information(please specify path),and also let me know from DB13 how can I schedule backups.

If possible please send me any specific link related to all my above queries.

Your help would be more appreciable

Many thanks in advance

rk.

[Former Member · Accepted solution]

Hi,

ad 1)

Your database needs to be in full recovery mode. ( You will find this in the database properties )

I run full database backup once per week ( to tape of course ) and incremental backup once per day.

Backup log between the database backups, every 2 hours. Backup the log to file. For security reasons place the file in other server in the network or in a dedicated drive in the server.

After the full backup is completed, copy the backup log file to a tape. The first log backup must initialize the file an restart the backup log process.

Please look at this link: http://help.sap.com/saphelp_nw04s/helpdata/en/d1/21714b7d3a11d288ec0000e8200722/frameset.htm

ad 2,3)

initsid.sap and BRTools will not work in a SQL Server environment as they were developed for the Oracle databases.

Please look at this thread to:

Regards,

Marcin Gajewski

[Former Member · Accepted solution]

Hi,

Many thanks for your quick response.

Here I am having some more queries.

1)In your answer you have specified that to take backup log.Does it means that you are taking Transaction log backup for every 2 hours or not?

and let me know that..... are you overwriting the backup logs or not and let me know from where you are taking backup logs.

2)Also let me know how are you taking usr/sap log?will it comes under full database or we need to take this one as seperate?

3)In order to take online full database or transaction log where should I need to give the parameters?Is there any arrangement like initsid.sap file in sqlserver?

Please let me know the above

Many thanks in advance

rk.

[Former Member · Accepted solution]

Hi,

I backup transaction logs every 2 hours using incremental backup.

I do it on separate disk. On saturday, my script compress these backup and write it to a tape. (uncompressed backup have about 30GB data - compressed ~7-10GB).

Regarding binary data in "usr" catalog: these data I backup every day using NTBACKUP and once a week (on sunday) using NTBackup + system state.

Regards,

Marcin

[Former Member · Accepted solution]

Hi,

Thanks for your quick response.

Here I am having some more queries.

1)Please give me some more elaborated information related to 2 nd query(usr/sap backup)

2)Also let me know how to schedule online backups in DB13(any links)

3)How to schedule incremental backups in DB13(In my DB13 screen there is no incremental backup link available)

4)Here I am not using any scripting for compress the file,so suggest me any alternative.

5)where you will store the usr NTBACKUP(in your local disk or some other server)

Please respond immediately

rk.

[Former Member · Accepted solution]

Hi,

ad 1) Run "ntbackup" in start->run. Go to schedule jobs tab and click on Add Job.

Choose "back up selected files, drives, or network data". On next screen choose SID catalog from \usr\sap folder and then next button. On type of backup select incremental backup.

ad 2) Go to DB13, on planning callendar click on hour field and on "Add" button.

Review Your Planned start date and time. On action choose "Full Database backup".

On Action Parameters tab choose your SID database, backup device and check "Verify Backup" checkbox. On Reccurence tab define reccurences (eg. every week in sunday at 2:00 AM). Click Add.

Add another action - Differential backup. On Action Parameters tab choose same parameters as above. On Reccurence tab define reccurences (eg. every day at 5:00 AM). Click Add.

Add another action - Transaction log backup. On Action Parameters tab choose same local disc (eg. Drive F). On Reccurence tab define reccurences (eg. every 2 Hour). Click Add.

Look at: http://help.sap.com/saphelp_nw04/helpdata/en/f2/31ad4d810c11d288ec0000e8200722/frameset.htm

ad 3) I'm sorry You're right. There is no incremental backup link in DB13. Incremental backup is only in Enterprise Manager.

ad 4) I use RAR to compress database backup files with simple batch file

eg.

rar a -y -ag_dd-mm-yy_hh-mm \serverBACKUPSID_MSDB.rar SID_MSDB.bak

this command produce following file

SID_logs_17-12-06_21-00.rar

and store it to remote backup server.

ad 5)

All backup plan look like this:

MS SQL Server with databases

- full backup - on tape

- differential - on separate local disk. From this local disk to *.rar archive and backup server

- transaction log - on separate local disk. From this local disk to *.rar archive and backup server

When I have all backup (all week), I copy it on tape (full is already on tape, all differential and all transaction log)

Marcin

[Former Member · Accepted solution]

Hi Marcin,

Many thanks for your detailed reply.

My apologies for late response.If you can give me answers for some more queries it would be help full for me.

1)When I am going through NTBACKUP in run mode it is showing wizard:

please tell me what should I do from this wizard inorder to achieve the steps recommended by you(if u don't mind step by step)

Also let me know where you are storing these "back up selected files, drives, or network data"............are you storing this data in disk or some other place

Also you have not mentioned the storage location usr/sap

What is the time period that you are giving for this incremental netbackup in your scenario

2)You are using RAR to compress the files.

Where should I need to use this command(tell me path)

Also let me know any configuration or something needed for RAR files

3)Can we store database backup and transaction backup in the same tape??

any pros and cons

4)How many tapes that you are using in your scenario and how you are reusing these tapes(step by step) tape size and database size required.

5)Are you taking MASTER and MSDB database backup,if so where you are storing and also let me know the recurrence period

Please give the answers step by step so that I will create a backup strategy with referrence to your strategy

Many thanks in advance

rk.

[Former Member · Accepted solution]

Hi,

I have separate server with backup data storage and tapes. This server has different net shares for backups (logs, binaries etc.)

ad 1)

Run NTBACKUP from Run... command. Click Backup Wizard -> Next -> back up selected files, drives, or network data -> choose usr\sap\<SID> catalog and Next button -> backup media: File, backup media or file name:
<backup_server>\<SID_bin_share>\<SID_datetime>.bkf, click Next -> click Advanced Button -> select type of backup: Incremental and Next button -> check Verify data after backup and Next -> Append this backup to the media and Next -> Next -> Click on Later and schedule Job name.

This backup goes to net share of backup server with ~4h period. System binaries do not change often. Incremental backup increments file about ~2-14MB per 4h (most file in backup are *.log files)

ad 2)

I use RAR because ZIP don't zip files grater than 2GB (*.mdf, *.ndf and *.ldf data files have more than 2GB in one file). I copy rar.exe to winnt directory and run it from a windows scheduler. (example code in previous message)

ad 3)

Yes, You can store database and transaction log in one tape.

ad 4)

I use 40GB DLT Tapes. One tape for one day (from monday to saturday) and one tape for Weekend. On monday to friday I copy to tape compressed differential backups and transaction logs. On Weekend tape I store compressed full backups. This tape goes to shelf.

ad 5)

Yes, I'm taking master and msdb backup with full backup every day and copy it to "day tape". This databases don't change often.

Regards,

Marcin Gajewski

[Former Member · Accepted solution]

Hi,

Thanks for your reply I am assuming the following strategy please let me know any suggestion

full database:weekly once(stored on seperate server and tape)

differential:every day(stored on seperate server and tape)

transaction log:every 2 hours(stored on seperate server and tape)

usr/sap:not yet decided

Please go through above and let me know following

1)How you are scheduling these ntbackups and what is the jobname that you are giving?what is the recurrence time that you are giving for these incremental backups.(daily or weekly)mention it

2)In my scenario I am having following

drived:usr/sap

drivef:tempdb

driveg:data files

driveh:transaction log

So please suggest me how can I take the backups for datafiles and tempdb

will these files covered while taking full database backup??or I need to follow individual incremental backups for these files??suggest me

3)You are using 7 tapes in your scenario,please let me know how you are reusing these tapes,I mean after completion of week how you are reusing these tapes(sun.........sat mention procedure)

4)I have also downloaded RAR to winnt but don't know how to work with windows schedular(can u plz suggest me in this regard and also give 1 more detailed example mentioning that RAR cmd file with which you are decompressing) any configuration needed for RAR scheduling??

5)msdb,master database backup:Are you taking full backup of these 2 or not?if so you are taking on daily basis or weekly basis

In your early reply you mentioned that you are taking full backups for weekly once so by this point I am bit confused about these 3 databases,plz give me clear idea.

Once again many thanks for going through such a big mail.

rk.

[Former Member · Accepted solution]

Hi,

Please rspond to my query and also let me know is there any specific tools for taking full backups(which includes all files like sr/sap,tempdb,datafiles,transaction log etc)at once

Your help would be more appreciable

Thanks and regards,

rk.

[Former Member · Accepted solution]

Hi,

full database:weekly once(stored on seperate server, compressed and then stored to tape)

differential:every day(stored on seperate server compressed and then stored to tape)

transaction log:every 2 hours(stored on seperate server compressed and then stored to tape)

usr\sap\<sid>: You may backup this every day in incremental mode.

ntbackup is used only for backup usr\sap<sid> catalog. Job name eg. "<SID> binary backup", recurences: every day at 4:00 AM

ad 2)

drive d - usr\sap: ntbackup in incremental mode every day at 4:00AM

drive f: tempdb: same as transaction log files

drive g - data files: full on saturday at 1:00 AM and scheduled RAR at 4:00 AM (we give 3h at complete backup), differential on monday to friday at 5:00 AM and scheduled RAR at 7:00 AM.

drive h - transaction log: every 2 hours exept night (our company don't have night shift) RAR 1h after backup

This scenario give You restore point with max 2h of failure (transaction log takes time every 2 hours). If You want to restore database from thursday at 6:00 PM You must use Full backup taken on Saturday, differential backup from mo, tue, wed, thu and transaction log on monday to thursday at 18:00.

About RAR: You can write small script in *.bat or *.cmd files and attach it to windows scheduler (timing above). This script take backup file eg. sid_datettime.bak and compress it to following directory. Backup program eg. BrightStor copy this folder (with compressed files) to tape.

Example code for *.cmd files

h: 
cd dataMSSQLBACKUP
rar a -y -ag_dd-mm-yy_hh-mm \<backup_server>BACKUP<SID>.rar <SID>.bak

This code backups <SID>.bak file in location h:\data\mssql\backup and copy it to <backup_server>\backup with filename <sid>_19-12-06_10-00.rar

Databases master and msdb You can backup every day with full backup (backup takes about ~5 minutes and file size is about 20MB - very small). You can copy it to "day tape".

Regards,

Marcin

[Former Member · Accepted solution]

Hi,

Thanks for your suggestion

Let me know any manual commands that we can use for backups.If so where can I find the commands in sqlserver let me know

Can you share the ideas of scripting means how to create scripts and on what server should I need to create scripts etc........As I am not aware of scripting can you suggest me any alternative for scripts in backups.

Let me know how you are reusing tapes and files,I mean are you replacing the files or tapes weekly or you keep on copying the files into backup server.

If you are keep on copying the files(or tapes) on backup server will it causes to any memory related problems.

Please respond immediately

Many thanks in advance

rk.

[Former Member · Accepted solution]

Hi All,

Please respond to my query ASAP.

Many thanks in advance

rk.

[Former Member · Accepted solution]

Hi,

Sorry for my delay, I was quite busy before Christmas.

Backup is fully automated. One thing You must remember to replacing the tapes.

If You shedule SQL backup via DB13, You must define all scripts on database servers in windows scheduler.

About reusing tapes: when free space on day tapes are finish, I put these tapes into shelf and get new one. After 6 months these old tapes are formated and go to reuse.

Regards,

Marcin Gajewski

[Former Member · Accepted solution]

Hi Thanks for your reply.

Suppose If I am scheduling backups through DB13 is there any need to do on sqlserver management studio also,if so please let me know procedure.

Also let me know how can I define scripts in windows schedular

Are you using any wizard like maintenance plan in the management studio in order to takebackups let me know any procedure for this,Is there any things need to be done on DB13 related to this.

In DB13 when I am trying to schedule it is showing the tape name by default like R3DUMP0,R3DUMP1,R3DUMP2 etc.Now let me know how can I change this name and include my own name to this.Or else let me know how can I give a name to the tape name as R3DUMP0........etc

Many thanks In advance

rk.

[Former Member · Accepted solution]

Hi,

If You schedule backup in DB13 there is no need to do anything in sqlserver mngmt. studio. For test, You can schedule backups for few days and You will see how it works.

To shedule already defined scripts do the following.

in Windows Control Panel click on Scheduled Task and add new Task. Click Next. In Wizard click Browse and select your backup_script.cmd, define timing of compression (daily) and start time.

R3DUMP0, R3DUMP1 etc. are logical names in SAP system. These are the operating system devices \tape0 to \tape2. If only one tape unit is connected to your machine, this is referred to as backup device R3DUMP0.

Look into SQL Server Management Studio \ Database instance \ Server Objects \ Backup Devices.

You can attach Your own device by right clickin' on Backup Devices -> New Backup Device. Name it and configure destination point. In DB13 You will see new device.

Regards,

Marcin

[Former Member · Accepted solution]

Hi all,

Please respond to my query ASAP

Your help would be more appreciable

Thanks and regards,

rk.