Transcript SQL Server “Sphinx”

Microsoft SQL Server
Administration for SAP
Database Backup and Restore
Overview





SQL Server Architecture
SQL Server with SAP
Performance Monitoring and Tuning
Administration and Troubleshooting
Database Backup and Restore
2
Importance of Backup

User Errors

Physical Errors

External Factors

Logical Errors
3
Recovery Models

Full



Simple




No work loss
Supports recovery to any point-in-time
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
4
Backup






Full
Differential
Log
File
File Group
NT Backup
5
Full Backup
Data
Vasser Hil
Frenzen
X5
X6
X7
X12
X13
X14
X15
Kojak
MagnumDerrick Marple
X8
X9
X10
X11
Landis
Wolf
Wang
Kerber Kania
Thomas
X16
X17
X18
X19
X21
Bach
Strauss Wagner Beeth.
X26
X27
X24
X25
X3
X4
X1
Mozart
X2
Andre
X0
X20
1
Merdes
X22
X23
Copy all used data pages to the backup media
Transaction Log
X28
X29
X30
X31
begin
update begin
insert
commit
chkp
insert
rollback
LSN0
LSN1
LSN2
LSN3
LSN4
LSN5
LSN6
LSN7
LSN9
LSN10
LSN11 LSN12
LSN13 LSN14 LSN15
LSN16 LSN17 LSN18
LSN19 LSN20
LSN21 LSN22 LSN23
LSN24 LSN25 LSN26
LSN27 LSN28
LSN29 LSN30 LSN31
delete
LSN8
2
Copy all used log pages to the backup media
3
Set the timestamp of the backup to the time
when the backup has finished
6
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
LSN16 LSN17 LSN18
LSN19 LSN20
LSN21 LSN22 LSN23
LSN24 LSN25 LSN26
LSN27 LSN28
LSN29 LSN30 LSN31
insert
1
Copy all used log pages to the backup media
2
Truncate the inactive portion of the transaction log
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
LSN16 LSN17 LSN18
LSN19 LSN20
LSN21 LSN22 LSN23
LSN24 LSN25 LSN26
LSN27 LSN28
LSN29 LSN30 LSN31
insert
7
Differential Backup
Data
X3
Andre
Vasser Schumi Frenzen
X4
X5
X6
X7
X12
X13
X14
X15
X0
X1
X2
Kojak
Rex
Derrick Marple
X8
X9
X10
X11
Dilg
Wolf
Wang
Kerber Kania
Thomas
X16
X17
X18
X19
X21
Mozart
Bach
Strauss
X24
X25
X26
X20
1
X22
X23
Copy all modified records since the last full
database backup to the backup media
Transaction Log
X27
X28
X29
X30
X31
begin
update begin
insert
commit
chkp
insert
rollback
LSN0
LSN1
LSN2
LSN3
LSN4
LSN5
LSN6
LSN7
LSN9
LSN10
LSN11 LSN12
delete
LSN8
LSN13 LSN14 LSN15
LSN16 LSN17 LSN18
LSN19 LSN20
LSN21 LSN22 LSN23
LSN24 LSN25 LSN26
LSN27 LSN28
LSN29 LSN30 LSN31
2
Copy all used log pages to the backup media
3
Set the timestamp of the backup to the time
when the backup has finished
8
File Backup
<SID>DATA1
<SID>DATA2
<SID>DATA3
<SID>DATAn
1
Copy one or more database files to the backup
media
...
9
Filegroup Backup
<SID>DATA1
<SID>DATA2
<SID>DATA3
<SID>DATAn
1
Copy one filegroup to the backup media
...
PRIMARY
10
NT Backup
Directory
Files
X:\<SID>DATA1
X:\<SID>DATA2
X:\<SID>DATA3
Primary data file
Secondary data file
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>
trans
R/3 executables
Transport directory
E:\WINNT
NT System directory
1
Copy all R/3 and SQL Server
files to the backup media
2
Create a document containing
the file structure
File System
Structure
11
How to Perform a Backup
unplanned
regular
Tue
Tue Wed Thu Fri
Sat
Sun
Mon Tue Wed Thu Fri
Sat
Sun
Mon Tue Wed Thu Fri
Sat
Sun
Mon Tue Wed Thu Fri
Sat
Sun
Mon
....
?
SQL
Enterprise Manager Query Analyzer
Tue
CCMS DBA Calendar (DB13)
Monthly
Backup
Cycle
12
Backup Requirements and Costs
Duration of a backup
Time window for a recovery
High availability
Training
Administrative workload
Acquisition costs
13
Backup strategies





Single DB and Transaction Log
Backups
Parallel Tape Support
Two-Step Disk Backup
Supplementary Differential Backups
Hot-Standby Server
14
Restore Procedure
Availability
Target
Actual
ONLINE
Replace hardware
and set up system
Problem
analysis
Restore
database
Apply
transaction
logs
Automatic
recovery
OFFLINE
Time
15
Data Volume Crash
Procedure and
escalation plan
Back up recent
transaction log
<SID>
<SID>DATA3
Replace RAID system
<SID>DATA2
<SID>DATA1
PRIMARY
Restore R/3 database
<SID>
Restore transaction logs
<SID>
Check restore operation
16
Log Volume Crash
Procedure and
escalation plan
Replace RAID system
Restore R/3 database
Restore transaction logs
<SID>LOG1
<SID>LOG1
<SID>
<SID>
Check restore operation
17
Executable Volume Crash
Procedure and
Escalation Plan
Replace disks and install
auxiliary NT
Directory
C:\Tempdb
Reload lost files from
latest NT Backup
C:\Mssql7\
C:\usr\sap\
C:\WINNT
Reboot Primary NT
Restore msdb (and
master) Database
msdb
master
Check Restore Operation
18
Summary
One disk
crash
RAID 1 crash :
Log files lost
<SID> suspect
RAID 5 crash:
Data files lost
<SID> suspect
System crash:
Data + EXEs lost
SQL Server down
Exe disk crash:
EXEs lost
SQL Server down
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
OK
Some data lost!
OK
OK
OK
19
System Crash
Replace hardware and
install auxiliary NT
Procedure and
escalation plan
Directory
Reload executables from
latest NT Backup
C:\Tempdb
C:\Mssql7\
C:\usr\sap\
C:\WINNT
Reboot primary NT
Restore msdb (and
master) database
msdb
Restore R/3 database
<SID>
Restore transaction logs
<SID>
master
Check restore operation
20
Backup and restore


Log marks
RESTORE options





RECOVERY, NORECOVERY, STANDBY
Backup password
Snapshot Backup
Database Copy Wizard
New Backup modes
21
Hot standby

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)
22
DB12
23
Backup History
24
SQL Agent Settings
25
Backup Device
26
Jobs
27
Restore information
28
DB13 Calendar
29
DB13 Scheduling Backup
30
DB13 Scheduling Backup
31
DB13 Scheduling Backup
32
DB13 Scheduling Backup
33
DB13 Calendar
34
Failed Job
35
Failed Job
36
Jobs in SQL Server
37
Backup from SQL Enterprise
Manager
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
Restore from SQL Enterprise
Manager
43
Restore from SQL Enterprise
Manager
44
Failed Job in SQL Server
45
Details of the failed job
46
Details of the failed job
47
Job Information
48
Job Information
49
Job Schedule
50
Notification
51
Backup Device
52
Backup – media set name
53
Restore from Device
54
Point in time restore
55
Lab






Create a Backup Device
Backup using CCMS
Backup using SQL Enterprise
Manager
Backup from Query Analyzer
Check the media naming convention
by SAP
Setup a stand by database and
switch to the standby database
56
Lab




Restore using SQL Enterprise
Manager
Restore from Query Analyzer(1 full
backup followed by 2 trans log
restore)
Setup log shipping and switch to the
backup server
Perform point-in-time recovery
57
Summary

Write module summary
58