Ch 11' Backup your data

1
Ch 11. Backup your data
2
Backup Device

• An object contain location of backup device
• Location can be a file or a tape device

3
Create a backup device

• Expand Server-gtserver objects
• Right click Backup Device, select New Backup
  Device
• Enter Device name as Device1
• Set Destination file to C\Device1.bak

4
Types of Backups

• Full backup backup all data and log files
• Differential backup backup all data and logs
  since last full backup
• Transaction Log Backup back up transaction log
  and clear old logs

5
Create a full backup

• Right click on test database, select
  Tasks-gtBackup
• Set backup type full
• Under Destination, click Add, select backup
  device, choose Device1 as backup destination,
  click ok to create backup
• Check for c\Device1.bak on your harddrive

6
Create a differential backup

• Add some data to test.test table
• Right click on test database, select
  Tasks-gtBackup
• Set backup type Differential
• Under Destination, click Add, select backup
  device, choose device1 as backup destination,
  click ok to create backup
• Right click on device1 backup device, select
  properties, select media content, you should
  see a list of backups performed.

7
Create a differential backup

• Add some data to test.test table
• Right click on test database, select
  Tasks-gtBackup
• Set backup type Transaction Log
• Under Destination, click Add, select backup
  device, choose device1 as backup destination,
  click ok to create backup
• Right click on device1 backup device, select
  properties, select media content, you should
  see a list of backups performed.

8
Backup to multiple devices

• Create another backup device device2
• Right click on test database, select
  Tasks-gtBackup
• Set backup type Full
• Under Destination, click Add, select backup
  device, choose device2 as backup destination,
  click ok to create backup.
• Right click on both backup devices, select
  properties, select media content, you should
  see both devices contain backups of the database

9
Restore

• During restore, database is in a inconsistant
  state and cannot be used
• You can set Recovery to true when you are done
  with restore.

10
Restore

• Delete all data from test.test table
• Right click on database-gttasks-gtrestore database
• Select backup you would like to restore from
• Click Option page
• Make sure recovery state is set to RESTORE WITH
  RECOVERY
• Check overwrite existing database option
• Click okay to start restore
• Go back to test.test , data has been restored

11
Point in time restore

• Only available with transaction log backup
• Allows you to restore back to a specific time

12
Point of time restore

• Create a transaction log backup
• Modify some data in test.test table
• Right click on device1, note the last transaction
  backup time
• Right click on test database-gttasks-gtrestore-gtrest
  ore database
• Click on ellipse on point-of-time text box, enter
  last transaction log backup time
• Notice test.test has been reverted