SQL Server Backup and Restore Ques & Ans

SQL Server DB Backup and Restore Questions & Answers

Q. How many types of Database Recovery Model in SQL Server? How they impact on database backup?
A: There are three types of Recovery Model in SQL Server:
1. Simple:Committed transactions are removed from the log when the check point process occurs.
2. Full:Committed transactions are only removed when the transaction log backup completed.
3. Bulk-Logged:Committed transactions are only removed when the transaction log backup process occurs.

Q. How to check that when the database backup was done and at which location?
A: There are multiple ways to check when the database backup was done:
1. Check Database Properties, 

2. Get the backup & location details from MSDB database under tables: msdb.dbo.backupset & msdb.dbo.backupmediafamily.
---------------------------------------------------------------------------------
--Database Backups for all databases For Previous Week
---------------------------------------------------------------------------------
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupset.expiration_date,
CASE msdb..backupset.type
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
END AS backup_type,
msdb.dbo.backupset.backup_size,
msdb.dbo.backupmediafamily.logical_device_name,
msdb.dbo.backupmediafamily.physical_device_name,
msdb.dbo.backupset.name AS backupset_name, msdb.dbo.backupset.description
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)
ORDER BY
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date
3. Check for the Successful backup entry in SQL Error log

Q. How can you verify database backup media\file is valid?
A: Using RESTORE VERIFYONLY Command, we can validate the backup file.
Syntax: RESTORE VERIFYONLY FROM DISK = 'Path'

Q. What are the post restore steps do you perform?
A: Here are the following steps:
1. Create required the logins and users
2. Sync the orphaned users.
3. Validate the data is accurate by running dbcc commands
4. Notify the App team\user community
5. If required, delete the sensitive data i.e. SSN’s, credit card information, customer names, personal information, etc.
6. Change database properties i.e. DB compatibility, recovery model, read-only, etc.

Q. Have you faced any issue while restoring a database?
A: Here are the few common reasons:
1.Insufficient permission to restore
2.Insufficient Space on the data\log drive.
3.Database is in Use. Unable to get Exclusive lock on the database.
4.Syntax error like run the command WITH MOVE option.
5.Version Issue, Restoring from higher version database backup to lower version. example: Restore from SQL 2008 R2 to SQL2005 not possible.

Q. What are the permissions required to perform backup and Restore?
A: The user must be a member of either of the below roles:
Backup:
Sysadmin – fixed server role
Db_owner – fixed database role
Db_backupoperator – fixed database role
Restore:
Sysadmin – fixed server role
Dbcreator – fixed server role
Db_owner – fixed database role

Q. How can we prevent all successful SQL Server backup entries writing to the SQL Server Error Log by a single trace flag?
A: Yes – Can be achieved by enabling the trace flag 3226.

Q. How many types of database backups in SQL Server?
A: There are three main backup in SQL Server:
1. Full Backup
2. Diffrential Backup
3. T-log Backup
Few other types:
Copy Only Backup: will not interrupting the LSN’s Sequence.
File Backup: Backup only particular File Backup
FileGroup Backup Backup only particular FileGroup Backup
For more details Refer Backup Restore Post

Q. What is Point in time recovery in SQL Server? Have you ever performed?
A: Point in time restore is possible only in FULL recovery model.
Here are steps to restore backups:
1. Take a tail log backup of the database which needs to be restored.
2. Restore the most recent full backup with the NORECOVERY clause.
3. Restore the most recent differential backup with the NORECOVERY clause
4. Restore all of the subsequent transaction log backups with the NORECOVERY clause except the last transaction log backup.
5. 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.
For more details Refer Backup Restore Post

Q. How to take tail log backup of the database?
A: Tail log backup is same as normal log backup. We have two options to consider:
1. WITH NORECOVERY: When database online and you are planning to perform a restore after the tail log backup. It takes the database in restoring mode to make sure that no transactions performed after the tail log.
2. WITH CONTINUE_AFTER_ERROR: When database offline and does not starts. Remember we can only perform the log backup on damaged database when the log files are not damaged

Q.You have an environment which is having scenario like Backups are scheduled as below:
FULL Backup- Weekly, Every Sunday Night at 1:00AM
DIFF Backup- Daily once in a Day
LOG Backup- In every 30 Mins
Now you found that your one of the user database has been corrupted and while restoring you got to know that the last Sunday night full backup also has been corrupted. What’s your recovery plan?
A: Here are the steps to Recovery Database:

1. Restore Full backup with NORECOVERY (Taken 13 Days ago)
2. Restore Diff backup with NORECOVERY (Taken 8 Days ago)
3. Restore Subsequent 8 Days of Log backup with NORECOVERY
4. Finally recover the database with command:
Restore Database with RECOVERY

Q. Full backup size is 100 GB, usually Diff backup size varies between 100 MB and 5 GB, one day unfortunately Diff backup size was increased to 50 GB? What is the reason behind this? any idea?
A: This is basically happens because of some activity is scheduled just after or nearby the full backup.
There are might be following reasons:
1. Maintenance jobs are scheduled- like Rebuild\Reorg, UpdateStats.
2. Heavy Insert\Update\Delete Operation just after the full backup completed.

To avoid these issues with Diff backups , schedule the index or such maintenance jobs to happen right before the full backup.

Q. What are the phases of sql server database restore process?
A: There are following phases of database restore process:
1.Analysis: Analyse the disk space availability, check for MDF\NDF & LDF directory and create the files and all other basic checks.
2.REDO: Rollfoward all committed transactions to database and if it finds any uncommitted transactions it goes to the final phase UNDO.
3. UNDO: Rollback any uncommitted transactions and make database available to users.

Q. What is piecemeal Restore?
A: Consider we have a database of 4 TB where as on primary file group is a read write filegroup of size 1 TB and we have other files groups which are read-only of size 3 TB. We actually need not perform backup for read-only file groups, here we can perform Partial backups.
Piecemeal restore process allows us to restore the primary filegroup first and the database can be online and the remaining filegroups can be restored while the recovery the transactions are running on primary File group. Mostly suitable for data warehousing databases.

Q. Consider a situation where I have to take a backup of one database of 60 GB. My hard drive lacked sufficient space at that moment. I don’t find 64GB free on any drive. Fortunately, I have 3 different drives where I can hold 20 GB on each drive. How can you perform the backup to three different drives? How can you restore those files? Is this really possible?
A: Yes we can achieve this using Split Backup.
. We can split the backup files into different places and the same can be restored.

Q. What is the recovery model of master database and can we change it? and can we take log backup of master database?
A: By default the recovery model of master database is SIMPLE.
Yes we can change the recovery model of master can be set to FULL or BULK_LOGGED. However, BACKUP LOG is not supported for master. Therefore, even if the recovery model of master is changed to full or bulk-logged, the database continues to operate as if it were using the simple recovery model.

Q. Is it possible to restore master database? If yes what are the steps?
A: Master Database keeps is having all server level information stored in it that includes logins information etc.., so ensure you schedule a regular backup for master database.
Yes we can restore master database, Here are the steps to restore a master DB:
1. Start the SQL Server Services in single user mode (-m)
Syntax:
C:\Program Files\Microsoft SQL Server\MSSQL10_50.1\MSSQL\Bin\sqlservr.exe -m -s
2. Issue the restore command with replace from SQLCMD prompt
Syntax:
RESTORE DATABASE master FROM WITH REPLACE
3. Restart the sql server in normal mode.

All databases as available at the time of master db backup must be attached as everything is tracked in master database.
If any databases are missing we can manually attach the mdf-ldfs.