BACK UP INTERVIEW QUESTIONS.

Backups

1)    What is Backups? Types of backups? (i)

“A copy of data that is used to restore and recover the data after a system failure”. There are four types of Backups available (1) Full Backup (2) Differential Backup (3) Transaction Log Backup (4) File /File Group Backup

2)    What is Use of Backups?

Backups is safe guard to Databases because Data may can loss due to many failures such as Media Failures, User errors, Hardware Failures and Natural Disasters etc.  With good backups, we can recover database from failures.

3)    What is Full, Diff, T.Log Bakups? Explain Each? (i)

a)    Full Backup:- Backs up the entire Database including Transaction Log. With full Backup Point-in-time recovery is possible because it contains .mdf and .ldf. It offers complete protection against media failures.

b)    Differential Backup:- Backs up only modified extents since the previous complete backup.

c)     T.Log Backup:- Backs up the active portion and truncates the inactive portion of the transaction log. It can be used for recover the data up to the point of failure (or) can restore the database to a specific point-in-time.

d)    File/File Group backup:- Backs up individual files and filegroups within a database.

4)    Can we take Diff/T.Log backups without Full backups?

No, it is not possible. Full backup is a base backup for Diff/T.Log backups.

5)    What are the Syntaxes for Backups? (i)

a)    Full Backup: - Backup database dbname to disk = “path”

        (e.g. BACKUP DATABASE AdventureWorks TO DISK = 'g:\backups\AdventureWorks.bak')

b)    Diff. Backup: - Backup database dbname to disk = “path” with differential

        (e.g. BACKUP DATABASE AdventureWorks TO DISK = 'g:\backups\AdventureWorks.bak' WITH DIFFERENTIAL)

c)     T.Log Backup:- Backup log dbname to disk = “path”

        (e.g. BACKUP LOG AdventureWorks TO DISK = 'g:\backups\AdventureWorks.bak')

6)    Explain about Tail of Log? Give the syntax? ®

The tail-log backup is the last backup that is used to recover the database to the point of failure (supports only Full or Bulk-logged recovery models).

(e.g. Assume that 4.00 P.M. log backup is performed and 5.00 P.M log backup has to be performed, in this case if the database crashed at 4.30 P.M, here we will loss 30 Min data, to recover this 30 Min of data we can use Tail log backup)

Syntax :-   

1) If the database is online :-

   Before starting a restore sequence, back up the tail of the log using WITH NORECOVERY whenever the next action you plan to perform on the database is a restore operation:

BACKUP LOG database_name TO <backup_device> WITH NORECOVERY

2) If the database is offline and does not start:-

Try to take a tail-log backup. Because no transactions can occur at this time, using WITH NORECOVERY is optional. If the database is damaged, use either WITH CONTINUE_AFTER_ERROR or WITH NO_TRUNCATE.

BACKUP LOG database_name TO <backup_device> [WITH { CONTINUE_AFTER_ ERROR | NO_TRUNCATE }

7)    Backup strategy? ®

Generally Backups are done by client requirement. But Most of the companies are following this strategy.

1)    Full Backup:- Every sunday nightly 10.00 PM

2)    Differential Backup:- Every day nightly 10.00 PM

3)    Transactional Log Backup:- Every 15/30 min

8)    What are the backup devices are used and Backup retention period? (i)

There are two main backup devices are used. First backups are taken into Disk and then it will moves to Tape.

1) Disk (onsite backup):- Backup retention period is 2 weeks of backups

2) Tape (offsite backup):- Backup retention period is 4 weeks of backups

Note:- Backup retention period means how many days of backups are maintained in disk/tape (after retention period the existing old backups will be deleted)

9)    What the main differences are between Disk and Tape?

Sl	Disk	Tape
1	It is fast	It is slow
2	Lives less life	Lives more life
3	Cost is more	Cost is less
4	Mainly used for online	Mainly used for restore

10) How SQL Server find the modified extents for taking Differential Backup?

All modifications are recorded in Differential Change Map (DCM) after full backup, SQL Server finds the modified extents and backups those extents.

11) What is the use of Copy-only Backup?

To take a backup without interrupting the current backup sequence number.

12) How to take a backup into multiple drives? (E.g. I want to take a backup which having Database size in 100 GB, but i have D: and E: drives having each 70 GB, how to take a backup for multiple drives) (s)

Backup database dbname to disk = “path1, path2”

(e.g. BACKUP DATABASE AdventureWorks TO DISK = 'D:\AdventureWorks.bak, E:\AdventureWorks.bak ')

14) True or False - The native SQL Server 2005 backups are in clear text.

• True - With SQL Server 2008 is the introduction of natively encrypted database backups.
• Prior to SQL Server 2008 a third party product was necessary to encrypt the database backups.

15)How can I verify that backups are occurring on a daily basis?

• Review the SQL Server error log for backup related entries.
• Query the msdb.dbo.backupset table for the backup related entries.
• Review the file system where the backups are issued to validate they exist.
• Additional information:

16)  How do you know if your database backups are restorable?

• Issue the RESTORE VERIFYONLY command to validate the backup.
• Restore the backups as a portion of a log shipping solution.
• Randomly retrieve tapes from off site and work through the restore process with your team to validate the database is restored in a successful manner.

17) For differential backups, how is the data determined for those backups?

    As data is changed in the extent, the extent is marked as changed and the entire extent is backed up.

18) How is a point in time recovery performed independent of a server down situation?

      It depends on which backup types are issued.  In this example let's assume that full, differential and transaction log backups are issued.

• • Restore the most recent full backup with the NORECOVERY clause
  • Restore the most recent differential backup with the NORECOVERY clause
  • Restore all of the subsequent transaction log backups with the NORECOVERY clause except the last transaction log backup
  • Restore the last transaction log backup with the RECOVERY clause and a STOPAT statement if the entire transaction log does not need to be applied