Backup's and Restore's
Introduction:
In this module I will try to discuss about backup and restoration Interview questions, before going that I will cover over view of Backups and Restoration features of SQL Server. In this module Recovery models are very important.
Recovery Models:
It is a Data Base level property. SQL Server supports three types of recovery models they are
1. Full recovery model (Default)
2. Simple recovery model
3. Bulk logged
Data base Backups:
It is the process of taking data, Meta data and services present in the db into O/S file. Multiple backups also we can write in a single file which maintains backups with position no.
Backup can be generated in two types of files
1. .BAK (can consists of any type of backup)
2. .TRN (transaction log backup)
Types of Backups:
1. Full backup
2. Differential backups
3. Transaction log backups
4. File or File group Backups
5. Mirrored, Stripped Backups
6. Copy-only Backups
7. Tail log Backups
Restores:
It is the process of creating database from existing backup file. To restore database the user must have either
· Sysadmin (or) dbcreator role
SQL Server supports three recovery states
1. With Recovery
2. With NoRecovery
3. With Standby
Frequently asked Questions:
1: What is checkpoint and how to verify when the checkpoint has raised?
It is a program executed by SQL Server to take all committed transactions from T.Log file into data file. It takes all committed transaction details from T.Log file and writes respective pages from buffer pool to data file. To verify when the checkpoint raised we can use
SP_WHO2 or DBCC LOG (‘dbname’, 3)
2: what are the recovery models and define it?
· Recovery models are Full, Simple, Bulk Logged recover model.
In full recovery model every transaction is recorded in T.log file. Log file grow very fastly.
In simple recovery model also every transaction is recorded in T.log file but log file is truncated once checkpoint occurs.
In Bulk logged every transaction is recorded except bulk operations.
Note: In full and Bulk logged log file is truncated at the time of log backup
.
3: What is Full, Differential, Transaction log and Copy-only Backups?
In full, complete database is backed up. It is a base for differential and transaction log backups.
In differential, Changes made after full backup can be taken into file with differential backup. It is generated fastly as compared with full backup.
In transaction log, it takes backup T.log file only. It takes all transactions from the last full or differential or log backup
In Copy-only backups are used to take backup without exciting plan but normal backups affect the backup plan and recovery steps.
4: What is tail log backup and WITH NO_TRUNCATE clause? How it take it?
It is last transaction log backup, which should be taken before restoring database in the event of database failure.
Syntax: BACKUP LOG TO DISK=’-------‘ WITH NO_TRUNCATE
With no_truncate clause allows the active part of the transaction log to be backed up even if the database is inaccessible.
5: In which scenarios T.Log is truncated?
· When checkpoint runs in SIMPLE recovery model
· When T.log Backup was generated in Full recovery model. It truncates all committed transactions from the log file.
6: When we cannot take backup of database in which state?
· Suspect (only tail log backup is allowed)
· Restoring
· Standby
· Offline
· If T.Log file was full(Only Log backups are allowed)
7: How can we imagine or calculate size of backup?
Use
Go
Sp_spaceused à Check reserved size
8: My database size is 8gb and daily 600 transactions give me your suggestible strategy?
1. Daily Full Backup
2. Every 4hrs differential backups
3. Every 2hrs T.Log backups
9: My database size is 600gb and daily 4000 transactions give me your suggestible strategy?
1. Weekly Full backup
2. Daily Differential backups
3. Every 1hr T.Log backup
Note: Whenever the database size is lessthan 200gb then follow FAQ: 8 steps. if morethan 200gb follow FAQ: 9 steps.
10: How to know the backup path where backups are stored?
Use MSDB
Go
Select * from backupmediafamilly
Note: Transaction log backups chain will never break.
11: How to recovery crashed database (data file & log file)?
Data File:
1. Check error log. If error number is 17204(data file was damaged)
2. Take tail log backup using
BACKUP LOG TO DISK=’----‘ WITH NO_TRUNCATE
3. Restore FULL BACKUP with NORECOVERY
4. Restore latest differential backup if any with NORECOVERY
5. Restore Tail log backup WITH RECOVERY
LOG File:
1. If log was damaged then make the database into single_user_mode
Alter database set single_user
2. Set the database in emergency mode
Alter database set emergency
3. Run the checkdb with required repair level
DBCC Checkdb (‘dbname’, ‘Repair_allow_data_loss’)
4. Set the database into multi user mode
Alter database set multi_user
12: Difference between Transaction log and tail log backups?
Transaction log Backup
|
Tail Log Backup
|
It is scheduled log backup which truncates T.log file.
|
It is not scheduled it cannot truncate T.log file.
|
It forces checkpoint
|
Skip checkpoint
|
Required to truncate T.log file
|
To recovery data when data file was damaged
|
13: My database recovery model was SIMPLE and I have taken full backup. I was unable to take T.log backup hence I have changed recovery model to full. Can I take T.log backup now?
Not allowed, Full backup of SIMPLE recovery model cannot work as base for T.Log backups we have to take first full backup then T.Log backup.
14: we have configured every Sunday 11pm FULL backup. Every 11pm differential backups and every 1h.r T.log backups. Database was failed at 11.30 pm on Friday. Then what are the db recovery steps?
1. Take Tail log backup to get 11-11.30pm transactions on Friday.
2. Restore last Sunday full backup with NO_RECOVERY
3. Restore Friday 11pm differential backup with NO_RECOVERY
4. Restore tail log backup with RECOVERY
15: My backup was failed what may be the possible scenarios?
- Disk was Full
- Server was busy
- Problem with network
- If domain is not running and SQL Server service is running with domain account
- Problem with MSDB and SQL Agent
- If there is disk I/O error while reading from data or T.log files
- CHECKSUM errors
- Database has entered into Suspect or restoring
- T.log file was full (only log backups are allowed other backups fail)
16: can I take tempdb backup and do the restore?
We cannot take the tempdb bakup
17: can I take log backup in simple recovery model?
No we cannot take log backup in simple recovery model.
18: What are the recovery states define it?
1. With Recovery
This database becomes operational. We cannot apply further backups.
2. With NORecovery
This database becomes non-operational. We can apply further backups.
3. With Standby
Database is restored in read only mode. Users can work only with SELECT command. It allows further transaction log backups to be restored.
19: Summary of Recovery Models:
FULL
|
BULK LOGGED
|
SIMPLE
| |
What’s logged
|
All transactions
|
All transactions except Bulk operations
|
Minimal for recovery
|
Log truncated
|
At backup
|
At backup
|
When Checkpoint
|
Available for restore
|
Yes
|
Yes
|
No
|
Recommended for production
|
Yes
|
Not Always
|
NO
|
Point in time recovery
|
Possible
|
No
|
No
|
Supports log backups
|
Yes
|
Yes
|
No
|
Use minimal space
|
No
|
No
|
Yes
|
Log shipping
|
Yes
|
Yes
|
No
|
Mirroring
|
Yes
|
No
|
No
|
Replication
|
Yes
|
Yes
|
Yes
|
