Skip to Content
0

Offline Backup Recommendation

Oct 19, 2016 at 12:38 AM

103

avatar image

Hi there,

We have just taken over the Basis support for a company running ECC

on Windows MS SQL Database.

At the moment they do not have any offline backups. Only online backups each day.

Am I correct in thinking that SAP do in fact recommend a weekly offline backup???

Thanks

Andy

10 |10000 characters needed characters left characters exceeded

Many thanks David for the reply. That has helped a lot, thanks

0
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
David Liu
Oct 19, 2016 at 12:48 AM
3

Hello,

Please read carefully following two documents which will explain you that there is no need to stop your SQL Server database to perform
a db backup :

SAP Note 1878886 - Backup Strategies for SQL Server

A concepts of an "offline backup" that is applicable to other DBMS does not apply to SQL Server. There is no need to shutdown SQL Server to perform a backup under any circumstance.

SAP Note 1297986 - Backup and Restore strategy for MS SQL Server

Misconception 5:
"We make an "offline backup", this is the most reliable kind of backups and is easy to perform".
Fact:
There is no such concept as "offline backup" for Microsoft SQL Server at all. This misconception comes from other DBMS where a standard backup procedure is nothing more than copying of data files onto backup media, often directly with OS tools. While it is easy to perform when the DB is offline, a consistent file copy of a running database requires a specific procedure to be strictly followed. As a consequence, "online"- backups were error-prone and sometimes appear not to be usable.
In SQL Server DB backups are always made using the native atomic T-SQL command "BACKUP DATABASE". This command does all actions that guarantee logical consistency of a backup set (as a rule it is a single file or single tape). It does not copy whole data files, rather used data pages only as well as log pages that include redo and rollback information.
So, in Microsoft SQL Server a single full DB backup set guarantees a consistent DB restore even if all subsequent transaction log backups are missing. The BACKUP DATABASE command is executed when the SQL Server is running and the DB is up, a SAP system may or may not be running at this time, this does not matter at all. From there any backup in SQL Server is always "online". What people erroneously call "offline backup" on SQL Server can be one of two cases:
- The SAP system is stopped but DB server is running and the DB backup is made with command "BACKUP DATABASE" or with a third-party backup software which calls this T-SQL command. This is an allowed option, we just need to point out that a shutdown of the SAP system is not needed and in fact it is not an "offline" backup.

- SQL Server is stopped and the backup is made by copying the database files (data and transaction log files) using OS tools like
NTBACKUP or even with a manual/scripted file copy. This is absolutely unacceptable because such a "backup" can be "restored" though (in fact: attached), but it will not accept the subsequent transaction log backups. So the data is restored only to the point in time when the file copy was done and the data loss will comprise many hours or even days. If this method is being practiced as an addition to valid backups, there is no risk, those files are just useless and waste space. However if this is the only way to back up the DB, this should be addressed with very high priority. The file copy takes usually much longer because data files and especially transaction log files have a lot of unused space in them. While the usual backup excludes empty pages from a backup set, the file copy cannot do so. For the same reason much more space is necessary for storing of such "backups".

Beside these two Notes, you can also see the SAP Library 'Backup with MS SQL Server' document at :

http://help.sap.com/saphelp_nw73ehp1/helpdata/EN/44/fee1419c9224dee10000000a114a6b/frameset.htm

Regards,

David

Share
10 |10000 characters needed characters left characters exceeded
Joshua Lacroix Oct 21, 2016 at 06:09 PM
0

Online backups? Define that.

You have the DB recovery model to full right? You are creating transaction logs? You are backing these up off the server at a good frequency during the day right?

Our production server, the Trans logs are backed up ever 30 mins,

Share
10 |10000 characters needed characters left characters exceeded