1 / 58

Microsoft SQL Server Administration for SAP Database Backup and Restore

Microsoft SQL Server Administration for SAP Database Backup and Restore. SQL Server Architecture SQL Server with SAP Performance Monitoring and Tuning Administration and Troubleshooting Database Backup and Restore. Overview. Importance of Backup. User Errors Physical Errors

anila
Download Presentation

Microsoft SQL Server Administration for SAP Database Backup and Restore

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Microsoft SQL Server Administration for SAPDatabase Backup and Restore

  2. SQL Server Architecture SQL Server with SAP Performance Monitoring and Tuning Administration and Troubleshooting Database Backup and Restore Overview

  3. Importance of Backup • User Errors • Physical Errors • External Factors • Logical Errors

  4. Recovery Models • Full • No work loss • Supports recovery to any point-in-time • Simple • Simplest backup/restore strategy • Less log space required • Greatest work loss possible • Bulk_Logged • High performance bulk operations • Minimal log space for bulk operations • Some work loss exposure

  5. Backup • Full • Differential • Log • File • File Group • NT Backup

  6. Data Andre Vasser Hil Frenzen X0 X1 X2 X3 X4 X5 X6 X7 Kojak MagnumDerrick Marple X8 X9 X10 X11 X12 X13 X14 X15 Landis Wolf Wang Kerber Kania Thomas Merdes X16 X17 X18 X19 X20 X21 X22 X23 Mozart Bach Strauss Wagner Beeth. X24 X25 X26 X27 X28 X29 X30 X31 1 Full Backup Copy all used data pages to the backup media Transaction Log begin update begin insert commit chkp insert rollback LSN0 LSN1 LSN2 LSN3 LSN4 LSN5 LSN6 LSN7 delete LSN8 LSN9 LSN10 LSN11 LSN12 LSN13 LSN14 LSN15 LSN16 LSN17 LSN18 LSN19 LSN20 LSN21 LSN22 LSN23 LSN24 LSN25 LSN26 LSN27 LSN28 LSN29 LSN30 LSN31 Copy all used log pages to the backup media 2 Set the timestamp of the backup to the time when the backup has finished 3

  7. Log Backup Transaction Log begin1 update begin2 insert commit2 chkp insert begin3 LSN0 LSN1 LSN2 LSN3 LSN4 LSN5 LSN6 LSN7 delete dump commit1 chkp insert insert delete delete LSN8 LSN9 LSN10 LSN11 LSN12 LSN13 LSN14 LSN15 insert LSN16 LSN17 LSN18 LSN19 LSN20 LSN21 LSN22 LSN23 LSN24 LSN25 LSN26 LSN27 LSN28 LSN29 LSN30 LSN31 Copy all used log pages to the backup media 1 Transaction Log begin1 update begin2 insert commit2 chkp insert begin3 LSN0 LSN1 LSN2 LSN3 LSN4 LSN5 LSN6 LSN7 delete dump commit1 chkp insert insert delete delete LSN8 LSN9 LSN10 LSN11 LSN12 LSN13 LSN14 LSN15 insert LSN16 LSN17 LSN18 LSN19 LSN20 LSN21 LSN22 LSN23 LSN24 LSN25 LSN26 LSN27 LSN28 LSN29 LSN30 LSN31 Truncate the inactive portion of the transaction log 2

  8. Copy all modified records since the last full database backup to the backup media 1 Transaction Log begin update begin insert commit chkp insert rollback LSN0 LSN1 LSN2 LSN3 LSN4 LSN5 LSN6 LSN7 delete LSN8 LSN9 LSN10 LSN11 LSN12 LSN13 LSN14 LSN15 LSN16 LSN17 LSN18 LSN19 LSN20 LSN21 LSN22 LSN23 LSN24 LSN25 LSN26 LSN27 LSN28 LSN29 LSN30 LSN31 Data Copy all used log pages to the backup media 2 Andre Vasser Schumi Frenzen X0 X1 X2 X3 X4 X5 X6 X7 Kojak Rex Derrick Marple X8 X9 X10 X11 X12 X13 X14 X15 Dilg Wolf Wang Kerber Kania Thomas X16 X17 X18 X19 X20 X21 X22 X23 Mozart Bach Strauss X24 X25 X26 X27 X28 X29 X30 X31 Set the timestamp of the backup to the time when the backup has finished 3 Differential Backup

  9. <SID>DATA1 <SID>DATA3 <SID>DATA2 <SID>DATAn File Backup Copy one or more database files to the backup media 1 ...

  10. <SID>DATA1 <SID>DATA3 <SID>DATA2 <SID>DATAn Filegroup Backup Copy one filegroup to the backup media 1 ... PRIMARY

  11. Copy all R/3 and SQL Server files to the backup media 1 Create a document containing the file structure 2 File System Structure NT Backup Directory Files X:\<SID>DATA1 Primary data file X:\<SID>DATA2 Secondary data file X:\<SID>DATA3 Secondary data file Y:\<SID>LOG1 Transaction log file Z:\Tempdb data and log files of the tempdb C:\Mssql7\Backup Default Backup directory Binn MS SQL Server executables Books Online documentation Data System and sample database files Html Enterprise Manager Html Files Install Installation scripts and logs Job Temporary job output files Log Errorlogs and Joblogs Repldata Working directory for replication tasks Upgrade Files used for upgrade (6.5 to 7.0) D:\usr\sap\<SID> R/3 executables trans Transport directory E:\WINNT NT System directory

  12. ? . . . . How to Perform a Backup unplanned regular Tue Tue Mon Wed Thu Fri Sat Sun Tue Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thu Fri Sat Sun SQL Enterprise Manager Mon Tue Wed Thu Fri Sat Sun Query Analyzer CCMS DBA Calendar (DB13) Monthly Backup Cycle

  13. Backup Requirements and Costs Duration of a backup Time window for a recovery High availability Training Administrative workload Acquisition costs

  14. Backup strategies • Single DB and Transaction Log Backups • Parallel Tape Support • Two-Step Disk Backup • Supplementary Differential Backups • Hot-Standby Server

  15. Restore Procedure Availability ONLINE Target Replace hardware and set up system Actual Problem analysis Restore database Apply transaction logs Automatic recovery OFFLINE Time

  16. <SID> <SID> <SID>DATA3 <SID>DATA2 <SID>DATA1 PRIMARY <SID> Data Volume Crash Procedure andescalation plan Back up recent transaction log Replace RAID system Restore R/3 database Restore transaction logs Check restore operation

  17. <SID> <SID>LOG1 <SID>LOG1 Log Volume Crash Procedure andescalation plan Replace RAID system Restore R/3 database Restore transaction logs <SID> Check restore operation

  18. Directory msdb C:\Tempdb C:\Mssql7\ C:\usr\sap\ C:\WINNT master Executable Volume Crash Procedure andEscalation Plan Replace disks and install auxiliary NT Reload lost files from latest NT Backup Reboot Primary NT Restore msdb (and master) Database Check Restore Operation

  19. Back up log <SID> with no_truncate Shut down SQL Server Replace crashed disk(s), synchronize RAID NT Restore of EXEs; not log file(s)! SQL Restore of database and transaction logs SQL Restore of msdb (and master) Automatic recovery Summary One disk crash RAID 1 crash :Log files lost<SID> suspect RAID 5 crash:Data files lost <SID> suspect System crash: Data + EXEs lostSQL Server down Exe disk crash: EXEs lostSQL Server down OK Some data lost! OK OK OK

  20. Procedure andescalation plan Directory msdb <SID> C:\Tempdb C:\Mssql7\ C:\usr\sap\ C:\WINNT <SID> master System Crash Replace hardware and install auxiliary NT Reload executables from latest NT Backup Reboot primary NT Restore msdb (and master) database Restore R/3 database Restore transaction logs Check restore operation

  21. Log marks RESTORE options RECOVERY, NORECOVERY, STANDBY Backup password Snapshot Backup Database Copy Wizard New Backup modes Backup and restore

  22. Scheduled movement of T-logs Pulled to secondary machine(s) Copies all Logged Operations DML + schema, permissions, roles,... Setup & managed via Maintenance ‘Plan’ Interactive remote monitoring Used for creating ‘warm backup’ DBs Roll reversal (swap source <-> destination) Hot standby

  23. DB12

  24. Backup History

  25. SQL Agent Settings

  26. Backup Device

  27. Jobs

  28. Restore information

  29. DB13 Calendar

  30. DB13 Scheduling Backup

  31. DB13 Scheduling Backup

  32. DB13 Scheduling Backup

  33. DB13 Scheduling Backup

  34. DB13 Calendar

  35. Failed Job

  36. Failed Job

  37. Jobs in SQL Server

  38. Backup from SQL Enterprise Manager

  39. Backup from SQL Enterprise Manager

  40. Backup from SQL Enterprise Manager

  41. Backup from SQL Enterprise Manager

  42. Backup from SQL Enterprise Manager

  43. Restore from SQL Enterprise Manager

  44. Restore from SQL Enterprise Manager

  45. Failed Job in SQL Server

  46. Details of the failed job

  47. Details of the failed job

  48. Job Information

  49. Job Information

  50. Job Schedule

More Related