Backup,Restore and Recovery
Training Division
New Delhi
Backup Restore and Recovery
Backup Restore and Recovery
No Data loss and server stay up and running Fault Tolerance protection
Disk mirroring RAID
Implement Hardware RAID solution
Use window NT server’s in-built capabilities
Backup and Recovery of Data:Allows for the complete restoration of data over a wide range of potential system problem such as:
Media Failure
Database/Table Corruption Permanent loss of Server User Errors
Planning to Backup & Restore
Backup and restore operation must be planed together.
Think of the type of problem that might possibly affect the system e.g.
Server failure, Database failure, Table corrupt
Impotence of Data
Recover to the point of failure, Can rely on last backup
What downtimes is acceptable
Requires more frequent backup Warm standby server
Analyzing Backup Requirment
How large is each database ?
Sp_spaceused
Dbcc sqlperf (logspace)
How often does the data change ?
Are some table modified more then
others ?
How easy would it be to re-create lost
data ?
Do you need to keep a rotating sets of
backup ?
Backup Consideration
Who is responsible for backup ?
Capability of executing the backup and restore command
Member of db_owner or db_backupoperator roles.
Verify that the backups are good
Run consistency check before backup start.
Periodically test backups by restoring on a testing server.
Which database should you backup ?
System database User database
Types of backups
Complete Database backup
Copy the data as well as active transaction log.
Differential Database Backup
It is copy of only the changes to the database since the last full database backup.
Only the last differential backup restore. Required full backup to be restored first.
Transaction log
It is a copy of all committed transaction since the last log backup
Type of Backup cont….
Is a serial record of all database tranaction that can be replayed.
It can restore database to known state.
File and File group Backup
Backing up System Database
Master
Backup after any significant changes are made e.g. server configuration
change,creating, changing and droping database or devices.
Can’t perform transaction log backup.
MSDB
Keep Information used by SQL Server Agent e.g. Job scheduling, alters.
Backing up System database
Model
Is a template database
Backup when changing settings
Tempdb
Backing up User Database
At a minimum:
Weekly backup of full database Daily transaction log backup
Backup full database after:
Non-logged operation
Importing/exporting
Choosing backup Strategy
Backup the database only
You might lose all committed transaction that have accord after recent database backup.
Backup all the data regardless of whether it changes after the last database backup.
Is self-contained and does not rely on any other backup to be restored.
It will use more space and time to complete backup operation on per backup.
Choosing backup Strategy
Backup data +Transaction log
If is not acceptable to loss any transaction. You will be able to recover all committed transaction up to the point of failure.
Provide the information necessary to redo changes made after a database backup was performed.
If data changes are frequent.
Creating and applying is more complex than simple database backup.
Restoration for transaction log takes longer times.
Choosing backup Strategy
Differential Database Backup
Increase the speed of backup and restore.
Record only those changes made to the database after the last full backup.
It is a smaller and takes less time to complete. Don’t allow to be restored to the exact point of failure.
Implementing backups
Creating a backup devices
Backup device enable to associate a logical name with the physical backup media.
Tape device : Must be connected physically to the computer runs SQL server.
Disk device :
Are files on hard disk and are same as o/s files. \\server name\share name\path\file.
Appropriate Permission.
Backup data on network can be subject to error, verify it.
Named pipe device : Third party vendors.
Implementing backups
Adding Backup Device
Transact-SQL
Sp_addumpdevice “tape/disk”,logical name, physical name.
Backup database database_ name to
device_name
Backup database database_name to disk
=“path”
Enterprise manager
Implementing backups
Verifying Database Consistency
Database DBCC CHECKDB ( 'database_name' [, NOINDEX | { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD }]
) [WITH {ALL_ERRORMSGS | NO_INFOMSGS}]
Tables
DBCC CHECKTABLE table name
Logs
Backing up the Database
Through t-SQL
BACKUP DATABASE {database_name | @database_name_var} TO <backup_device> [,...n]
[WITH
[BLOCKSIZE = {blocksize | @blocksize_variable}] [[,] DESCRIPTION = {text | @text_variable}]
[[,] DIFFERENTIAL]
[[,] EXPIREDATE = {date | @date_var} | RETAINDAYS = {days | @days_var}] [[,] FORMAT | NOFORMAT]
[[,] {INIT | NOINIT}]
[[,] MEDIADESCRIPTION = {text | @text_variable}]
[[,] MEDIANAME = {media_name | @media_name_variable}] [[,] [NAME = {backup_set_name | @backup_set_name_var}] [[,] {NOSKIP | SKIP}]
[[,] {NOUNLOAD | UNLOAD}] [[,] [RESTART]
[[,] STATS [= percentage]] ]
Backing up Restriction
Some operation are not allowed such
as:
Creating or deleting files. Creating indexes.
Performing Non-logged operation. Shrinking Database.
Backup transaction LOG
Through T-SQL
BACKUP LOG {database_name | @database_name_var} {
[WITH { NO_LOG | TRUNCATE_ONLY }] TO <backup_device> [,...n]
[WITH
[BLOCKSIZE = {blocksize | @blocksize_variable}] [[,] DESCRIPTION = {text | @text_variable}]
[[,] EXPIREDATE = {date | @date_var} | RETAINDAYS = {days | @days_var}] [[,] FORMAT | NOFORMAT]
[[,] {INIT | NOINIT}]
[[,] MEDIADESCRIPTION = {text | @text_variable}]
[[,] MEDIANAME = {media_name | @media_name_variable}] [[,] [NAME = {backup_set_name | @backup_set_name_var}] [[,] NO_TRUNCATE] [[,] {NOSKIP | SKIP}] [[,] {NOUNLOAD | UNLOAD}] [[,] [RESTART] [[,] STATS [= percentage]] ] }
Transaction Log
Sql Server guarantee transaction atomicity and
durability.
Transaction function as a single unit of work. Guarantee that the changes are permanent.
SQL Server writes every database changes to
the transaction log.
The log contain a record of every activity that
changed the database.
Keep tracks of new objects, user,permission
changes.
Transaction Log Structure
Records of transaction serves three function
Recovery of individual transaction.
Recovery of all incomplete transaction when SQL Server is started.
Rolling a restored database forward to the point of failure.
Transaction Log backup
The starting point of transaction backup is
where:
The previous transaction log backup ended.
The transaction log backup as part of the most recent database or differential database backup ended.
Truncating the transaction log:
The inactive portion of the transaction log is that part of log which is no longer used during recovery process.
Transaction Log backup
The ending point of inactive portion of
the transaction log is the earlist of these
events:
The most recent checkpoint.
A transaction that has not been committed or rolled back.
Condition for backing up
Transaction log
If the truncate log on check point is set to true.
If any Non-logged operation have accured in
the database .
Until a database backup has been created.
If the transaction log has been truncated,
unless a database backup is crested.
If any files are added or deleted from the
database.
Checkpoint
Execute Checkpoint startement
Database option changed
Server stopped, shutdown
Reduce Recover interval
Restoring the database
Through T-SQL
Restore database database-name from device-name
E DATABASE {database_name | @database_name_var} [FROM <backup_device> [,...n]]
[WITH
[DBO_ONLY]
[[,] FILE = file_number]
[[,] MEDIANAME = {media_name | @media_name_variable}] [[,] MOVE 'logical_file_name' TO 'operating_system_file_name']
[,...n]
[[,] {NORECOVERY | RECOVERY | STANDBY = undo_file_name}] [[,] {NOUNLOAD | UNLOAD}]
[[,] REPLACE] [[,] RESTART]
[[,] STATS [= percentage]] ]
Restoring the database
Through Enterprise Manager
Some restore command
Restore headeronly from backup-device Restore filelistonly from backup-device Restore verify only from backup-device
Restoring Transaction Log
Through T-SQL
RESTORE LOG {database_name | @database_name_var} [FROM <backup_device> [,...n]]
[WITH
[DBO_ONLY]
[[,] FILE = file_number]
[[,] MEDIANAME = {media_name | @media_name_variable}] [[,] {NORECOVERY | RECOVERY | STANDBY = undo_file_name}] [[,] {NOUNLOAD | UNLOAD}]
[[,] RESTART]
[[,] STATS [= percentage]]
[[,] STOPAT = {date_time | @date_time_var}] ]
Recovery
Automatic
It process each database in the following order at startup: master,model,tempdb,msdb, user database
Manual
When Disk failure
Install windowsNT and load appropriate service pack, verify that appropriate domain functionality exist.
Install SQL server and load appropriate service pack. Restore master, msdb backup.
Shutdown the server
Reconfigure the server for network libraries. Check SQL server running properly.
Recovery
Recovery to prior state.
Apply database backup.
Apply sequence of transaction log.
Restore to point of failure.
Backup the current active transaction log.
backup log mydb to disk= "c:\backup\mydblog1.bak" with no_truncate
Restore database backup without recovery.
restore database mydb from disk = "c:\backup\data.bak" with replace,norecovery
Apply each transaction log backup in sequence. Apply the current transaction log file.
restore log mydb from disk ="c:\backup\mydblogrp.bak" with recovery
