Backup and Restoration

Checkpoint
* It is a program which takes all committed transactions from  Transaction log file into data file.
* It takes all committed transactions from last checkpoint.
* It truncates T.Log file if the database recovery model is SIMPLE.
* In other recovery models (FULL and BULK LOGGED) checkpoint cannot  truncate T.Log file.

When Checkpoint Occurs?
    1. Periodically
        * It depends on recovery interval property of the server.
        * Right click on server name --> properties -->  Database Settings --> Recovery interval=n (m)
    2. When database backup is about to start.
    3. When SQL Server service is started
    4. When T.Log file is 70% full and it is in Log-truncate mode

FAQ:- What is difference between checkpoint and lazy writer?
Ans:
Checkpoint reads all committed transaction details from previous checkpoint and it submits the details like   LSN nos and effected pageids to lazy writer. Lazy writer copies all the pages to data file.

Recovery Models
* It is database level property.
* Transaction Log file behaviour depends on recovery models.
* The following features depends on recovery model.

    1. What is recorded in T.Log file
    2. When T.Log file is truncated?
    3. Date recovery upto point of failure is possible or not?
    4. Transaction Log shipping and mirroring are possible or not
    5. What types of backups are possible.

* SQL Server supports 3 recovery models
    1. FULL
    2. SIMPLE
    3. BULK LOGGED

* We can set recovery model by using ALTER DATABASE command
    use master
    go
    ALTER DATABASE <dbName> SET RECOVERY [FULL/BULK_LOGGED/SIMPLE]

Database Backups
* Backup is the process of taking data, meta data, objects etc from a database into o/s file.
* We can take backups into the following extension files

    .bak    (For any type of backups)
    .trn    (For T.Log backups)

* We can take multiple backups in a same file where SS maintains position value for these backups.
* Backups are useful
    * To provide security for data
    * To move/copy databases from one server to another.
    * To move the files of database.

Who can take backup?
* By default the following role members can take backups
    * SysAdmin    (Any db backup)
    * db_owner    (Backup of respective db)
    * db_backupoperator (,,  ,, )

What backup file consists of?
* Every backup file consists of 2 parts
    * Backup header (Meta data of backup(s))
    * Backup data
* Backup data consists of
    * Schema and file structure
    * data
    * Portions of T.Log file
    * It writes all pages into backup file by reading disk directly.

Types of Backups
* SS supports different types of backups
    * Full backup
    * Differential backup
    * Transaction Log backups
    * File or Filegroup backups
    * Stripped backups
    * Mirrored backups
    * Copy only backups
    * Tail Log backup

1. Full Backup
* It takes backup of complete database. SQL Server marks the extents which are taken into backup so that differential backup is generated later.
syn:
    use master
    go
    backup database <dbName> to disk='...................'
    with [options]
Ex:
    Take Full backup of AdventureWorks database in e:\backups folder.
Steps

    1. Create the folder e:\backups
    2. Grant read/write permissions to service account on this folder.
    3. Test it...
        start --> run --> \\systemName\backups
    4. Take new query
    use master
    go
    backup database AdventureWorks to disk='e:\backups\AdventureWorks_Full.bak'
We can mention network path as follows
     '\\Class2\backups\AdventureWorks_Full.bak'
* To verify backup
     restore verifyonly from disk='e:\backups\AdventureWorks_Full.bak'
* To view the header
    restore headeronly from disk='e:\backups\AdventureWorks_Full.bak'
* To view database files taken into backup
    restore filelistonly from disk='e:\backups\AdventureWorks_Full.bak'

FAQ:- How can you expect size of backup?
        Size of backup = sum of used extents in all data files * 64 KB + backup header

2. Differential backup
* Changes made after last full backup can be taken with differential backup.
* It takes less time as compared with full backup.
* These backups reduces recovery time as compared with T.Log backups.
syn:
    use master
    go
    backup database <dbName> to disk='..........'   with differential
Ex:
    use master
    go
    backup database AdventureWorks to disk='e:\backups\AdventureWorks_Full.bak' with differential

FAQ:- Where you can check backup details?
Sol:
    * We can view in MSDB database
        * backupset   (Consists of complete backup details)
            Backup types
                D    - Full
                I    - Differential
                L    - T.Log               
                F    - File/Filegroup
        * backupmediafamily - (Backup File path)
        * backupfile
        * backupfilegroup
        * backupmediaset

* To check history of backups we can use the following script regularly.

SELECT s.database_name, m.physical_device_name,
cast(s.backup_size/1000000 as varchar(14))+' '+'MB' as bkSize,
CAST (DATEDIFF(second,s.backup_start_date , s.backup_finish_date)AS
VARCHAR(4))+' '+'Seconds' TimeTaken,
s.backup_start_date,
CASE s.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END as BackupType,
s.server_name,
s.recovery_model
FROM msdb.dbo.backupset s
inner join msdb.dbo.backupmediafamily m
ON s.media_set_id = m.media_set_id
WHERE s.database_name = 'BackupReport'
ORDER BY database_name, backup_start_date, backup_finish_date

FAQ:- What is your backup strategy?
Sol:
    Backup strategy refers to
        * What types of backups we have configured
        * Interval of backups

    * It depends on the following factors
        * Size of db
        * Recovery model of db
        * DB availability (24/7)
        * Transaction Rate

Scenario
    * My Database size is 6GB and daily 1000 transactions
Suggested solution
    * We can suggest the backup strategy like
        * Daily Full backups
        * For every 4 hrs differentail/T.Log backups

3. Transaction Log backup
* It takes backup of only T.Log file.
* It is not possible if the recovery model is SIMPLE.
* Backup is generated very fastly.

Advantages

    * To truncate the T.Log file periodically.
    * To recovery data upto point of failure.
    * To implement T.Log shipping.

syn:
    use master
    go
    backup log <dbName> to disk='............'
ex:
    use master
    go
    backup log AdventureWorks to disk='e:\Backups\Adv_TLog.trn'

FAQ:- I have taken full backup when the database was in SIMPLE recovery model. It was not allowing and  to take T.Log backup so I have changed recovery model to FULL. Can I take now Transaction Log backup?
Ans:
    * Not allowed. Full backup of SIMPLE recovery model cannot work as base for T.Log backup.
     * Again we have to take Full backup then T.Log backup.

4. File/Filegroup backup

syn:
    use master
    go
    backup database <dbName> FILEGROUP='filegroupName' TO DISK='.............'
    use master
    go
    backup database AdventureWorks FILEGROUP='PRIMARY'
    TO DISK='e:\backups\Adv_Primary_FG.bak'

Backup device
* It is disk drive or tape drive where we can take backups.
* It maintains the path of the backup file.
syn:
    use master
    go
    backup database <dbName> to <backupdeviceName>

Ex:
--Creating backup device
USE [master]
GO
EXEC master.dbo.sp_addumpdevice 
@devtype = N'disk',
@logicalname = N'AdventureWorks_BD',
@physicalname = N'd:\backups\AdventureWorks_BD.bak'
GO

--Taking backup
backup database AdventureWorks to AdventureWorks_BD

5. Mirrored Backups
* Mirrored backups simply write the backup to more than one destination.
* You can write up to four mirrors per media set.
* This increases the possibility of a successful restore if a backup media gets corrupted.
* We can view mirrors information in backupmediaset table of msdb.

Ex:
-- make one backup on d: disk and a mirror on e: disk
BACKUP DATABASE AdventureWorks
TO DISK = 'd:\AdventureWorksMirror_1.bak'
MIRROR TO DISK = 'e:\AdventureWorksMirror_2.bak‘
WITH FORMAT;

6. Striped backups

* To divide the backup into multiple files.
* We can take backup into different files in different disks.
* SQL Server provides better performance.
* First file consists of backup header.

syn:
    use master
    go
    backup database <dbName> to disk='................',disk='................' with noformat
Ex:
    BACKUP DATABASE [AdventureWorksLT]
    TO
    DISK = N'd:\backups\AdvLT1.bak', 
    DISK = N'd:\backups\AdvLT2.bak'
    WITH NOFORMAT, NOINIT
    GO

7. Tail Log backup
1. A Tail log backup is the last Transaction log backup that you make prior to restoring a database.
2. If your db crashes for whatever reason, you have to backup your transaction log so that you can do point    in time recovery.
3. It is possible only
    a. If the recovery model of database is other than SIMPLE.
    b. If the transaction log file was not damaged physically.
    c. If there is FULL backup exists.

Steps: Taking tail log backup of SQLDB database.
1. Take Full backup of database.
use master
go
backup database SQLDB to disk='d:\backups\SQLDB.bak'

2. Insert some data into any one table of SQLDB database.
3. Stop server
4. Go to database files folder. Here
  C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA
5. Move the Data File to another location.
6. Restart the instance.
7. Now database goes into suspect mode.
8. Stop the server.
9. Copy Transaction Log file into different location. For example d:\Log folder.
10. Start server.
11. Create a new database with same name i.e. SQLDB. By deleting existing one.
12. Copy the old log file and over write the newly created log file of the database by stopping server.
13. Start server again.
14. Now take the tail log backup
    backup log SQLDB to disk='d:\backups\sqldb_tail.bak'
    with no_truncate
15. Now restore the FULL backup by using WITH NORECOVERY.
    restore database SQLDB from disk='d:\backups\SQLDB.bak'
    with norecovery
16. Restore the Tail Log backup WITH Recovery
    restore log SQLDB from disk='d:\backups\sqldb_tail.bak'
17. Observe the changes made after FULL backup.

Restoration
* It is the process of creating database from backup file.
* Restoration details are present in [restorehistory] msdb table.
* Only the following role members can restore backups
              * sysadmin
              * dbcreater
syn:
    use master
    go
    RESTORE DATABASE/LOG <dbName> FROM DISK='..............'
    WITH NORECOVERY/RECOVERY/STANDBY/REPLACE
ex:
    Backup Hospital db and restore it in another instance.

1. Go to first instance take full backup
    backup database Hospital to disk='d:\backups\hospital.bak'

2. Go to second instance and restore it. Create folders d:\data and e:\log, grant read write permissions to service account.

    Restore database Hospital from disk='d:\backups\Hospital.bak'
    with
    move 'Hospital'
    to 'd:\DATA\Hospital.mdf',
    move 'Hospital_log'
    to 'e:\Log\Hospital_log.ldf'

Recovery States

* SQL Server supports 3 recovery states.

1. With Recovery
    * Default state
    * Database comes online i.e. becomes operational.
    * We cannot apply further backups.

2. With NoRecovery
    * Database becomes non-operational.
    * We can apply further backups.
    * If we want to allow read only access on this database then we can create database snapshot.

3. With standby
    * Database is restored in read only state.
    * We can apply further backups.

With STOPAT option
* To recover the database upto required date and time.
* It is possible only with T.Log backups.
syn:
    RESTORE LOG <dbName> FROM DISK='............' with stopat ='date and time'

Backup Compressions
* To maintain the backups in compressed manner where we can use less disk space for the backups.
* SQL Server can compress upto 75 to 80%.
* We can enable by the following methods
    1. Using server level property
        sp_configure 'backup compression default',1
        reconfigure
    2. While taking backup
        backup database ................ with compression/no_compression

Ex:    Restoring AdventureWorks database from AdventureWorks_Full.bak
        file which consists of 3 types of backups i.e. FULL, DIFFERENTIAL AND LOG.

RESTORE DATABASE [AdventureWorks]
FROM DISK = N'D:\backups\Adventureworks_Full.bak'
WITH  FILE = 1,
MOVE N'AdventureWorks_Data'
TO N'd:\data\AdventureWorks.mdf', 
MOVE N'AdventureWorks_Log'
TO N'e:\log\AdventureWorks_1.ldf', 
NORECOVERY,STATS = 10
GO
RESTORE DATABASE [AdventureWorks]
FROM  DISK = N'D:\backups\Adventureworks_Full.bak'
WITH  FILE = 2, 
MOVE N'AdventureWorks_Data'
TO N'd:\data\AdventureWorks.mdf', 
MOVE N'AdventureWorks_Log'
TO N'e:\log\AdventureWorks_1.ldf', 
NORECOVERY,  STATS = 10
GO
RESTORE LOG [AdventureWorks]
FROM  DISK = N'D:\backups\Adventureworks_Full.bak'
WITH  FILE = 3
GO

ex:  Restoring from striped backups Adv1.bak and Adv2.bak

RESTORE DATABASE [AdventureWorks]
FROM  DISK = N'D:\backups\Adv2.bak', 
DISK = N'D:\backups\Adv1.bak'
WITH  FILE = 1
GO