SQL Server DBA Interview Questions and Answers – Database Backups and Restore-3

Lets have some more questions on Backup and Restore. You may visit my other backup and restore related blogs here Database Backup and Restore – 1 and Database Backup and Restore – 2
1) What is the Backup strategy in your environment?

It’s very open ended Question:
As per my understanding, normally Organizations follow below standards
Prod/QA Environment:

For Small size databases (<200 GB)

• Full Backup – Daily
• Differential Backups – NA
• Transaction Log backups – Every 10-15 minutes depending upon the RPO

For Large size databases (>=200 GB)

• Full Backup – Weekly
• Differential Backups – Daily
• Transaction Log backups – Every 10-15 minutes depending upon the RPO

Again it all depends upon the criticality of the database e.g. for Data warehousing databases it may be possible that there is no requirement of Transaction log backups.

 2) From a best practices perspective, what is your backup retention policy?

Again it all varies on organization’s policies. As a general practice it’s always better to keep the database backups on the shared location (In the Data Center) at least for 2-3 days. Daily backups should be written to tape backups and should be retained for a month or so. Also as a preventive measure there should be monthly or bi monthly backups with a retention policy of minimum one year.

3) What is the below error related to Permission issue for database backup?

Msg 3201, Level 16, State 1, line 1
Cannot open backup device ‘\\*****\*******\master.bak’.
Operating system error 53(The network path was not found.).

This error is due the insufficient permissions of Service Account on the Shared location.

4) What are Database Maintenance Plans?

Database Maintenance Plans allow us to automate many database administration tasks in Microsoft SQL Server. Maintenance plans create a workflow of the tasks required to make sure that our database is optimized, regularly backed up, and free of inconsistencies.

 5) What are the Benefits of Database Maintenance Plans?

SQL Server 2012 maintenance plans provide the following features:

• Workflow creation using a variety of typical maintenance tasks. You can also create your own custom Transact-SQL scripts.
• Conceptual hierarchies. Each plan lets you create or edit task workflows. Tasks in each plan can be grouped into subplans, which can be scheduled to run at different times.
• Support for multiserver plans that can be used in master server/target server environments.
• Support for logging plan history to remote servers.
• Support for Windows Authentication and SQL Server Authentication. When possible, use Windows Authentication.

6) What all options we have in a Database maintenance plans which helps a DBA to keep the database healthy?

We have below options in the Maintenance Wizard.

1. Check Database Integrity – checks the logical and physical integrity of all objects in the database
2. Shrink Database – shrinks the files and the logs in the specified database on a file-by-file basis. It pools log files. Physically shrinking makes the file smaller and truncates a file in the backup.
3. Reorganize Index – uses minimal system resources and defragments indexes of tables and views by reordering the pages to match the logical order
4. Rebuild Index – drops and recreates the index reclaiming space and reorders the index rows in contiguous pages
5. Update Statistics – updating statistics ensures queries run with up-to-date statistics, but they also require queries to be recompile, can result in a performance trade-off
6. Clean Up History – deletes entries in the history tables
7. Execute SQL Server agent job – Windows service that runs scheduled jobs
8. Backup Database (Full) – Backs up the entire database, but as databases grow, the backup takes longer and requires more storage. The best solution is to use in conjunction with differential backups.
9. Backup Database (Differential) – used to back up only the data that has changed since the last full backup
10. Backup Database (Transaction Log) – used to back up individual transaction logs, which can be individually restored since the last full backup
11. Maintenance clean up task – performs housekeeping functions

7) In which Database all the Maintenance Plans are stored?

msdb database contains information about all the Database Maintenance plans.

8) In which table all the Maintenance Plans meta data is stored?

•  sysdbmaintplan_databases: Contains one row for each database that has an associated upgraded database maintenance plan.
• sysdbmaintplan_history: Contains one row for each upgraded database maintenance plan action performed.
• sysdbmaintplan_jobs: Contains one row for each upgraded database maintenance plan job.
• Sysdbmaintplans: Contains one row for each upgraded database maintenance plan.

9) Is it possible to change the Database Maintenance plan once it’s configured?

Yes we can change the Database Maintenance plan anytime.

10) Can we execute TSQL Statement using Database Maintenance Tasks?

Yes, we can run TSQL Statement in a Database Maintenance Plan.

11) Can we run a SQL Server Agent job using Database Maintenance Tasks?

Yes, we run a SQL Server Agent job in a Database Maintenance Plan.

12) What is History CleanUp Task in Database Maintenance Plan?

The History Cleanup task deletes entries in the following history tables in the SQL Server msdb database.

•           backupfile
•           backupfilegroup
•           backupmediafamily
•           backupmediaset
•           backupset
•           restorefile
•           restorefilegroup
•           restorehistory

By using the History Cleanup task, a package can delete historical data related to backup and restore activities, SQL Server Agent jobs, and database maintenance plans.

13) Which system stored procedures are used by History Cleanup Task?

It uses sp_purge_jobhistory and sp_delete_backuphistory statements.

14) What is Notify Operator task in Database Maintenance Plan?

The Notify Operator task sends notification messages to SQL Server Agent operators. A SQL Server Agent operator is an alias for a person or group that can receive electronic notifications.

15) How can we be notified if a native SQL Server database backup or restore fails via the native tools?

• Setup SQL Server Alerts to notify SQL Server Agent Operators on a failure condition.
• Include RAISERROR or TRY\CATCH logic in your backup or restore code to send an email on a failure condition.

16) What are some common errors related to backup failures?

17) What are the phases of Database restore process?

A restore is a multiphase process. The possible phases of a restore include the data copy, redo (roll forward), and undo (roll back) phases:

• The data copy phase involves copying all the data, log, and index pages from the backup media of a database to the database files.
• The redo phase applies the logged transactions to the data copied from the backup to roll forward that data to the recovery point. At this point, a database typically has uncommitted transactions and is in an unusable state. In that case, an undo phase is required as part of recovering the database.
• The undo phase, which is the first part of recovery, rolls back any uncommitted transactions and makes the database available to users. After the roll back phase, subsequent backups cannot be restored.

18) What is a simple Database restore command from Full Backup?

RESTORE DATABASE [Restore_test] 
FROM  DISK = N'H:\MSSQL\Backup\Restore_test.bak'
WITH  FILE = 1, 
MOVE N'Restore_test_data' TO N'H:\MSSQL\Data\Restore_dat1.mdf', 
MOVE N'Restore_test_log' TO N'H:\MSSQL\Log\Restore_log1.ldf', 
REPLACE,  STATS = 10
GO

19) What is a simple Database restore command from Full & Differential Backup?

RESTORE DATABASE [Restore_test] 
FROM  DISK = N'H:\MSSQL\Backup\Restore_test.bak'
WITH  FILE = 1, 
MOVE N'Restore_test_data' TO N'H:\MSSQL\Data\Restore_dat1.mdf', 
MOVE N'Restore_test_log' TO N'H:\MSSQL\Log\Restore_log1.ldf', 
REPLACE,  STATS = 10, NORECOVERY
GO

RESTORE DATABASE [Restore_test]
FROM  DISK = N'H:\MSSQL\Backup\Restore_test_Differential.bak'
WITH  FILE = 1, 
MOVE N'Restore_test_data' TO N'H:\MSSQL\Data\Restore_dat1.mdf', 
MOVE N'Restore_test_log' TO N'H:\MSSQL\Log\Restore_log1.ldf', 
REPLACE,  STATS = 10, RECOVERY
GO

 20) What is the Difference between WITH RECOVERY and WITH NORECOVERY parameter?

• RESTORE WITH RECOVERY is the default behavior which leaves the database ready for use by rolling back the uncommitted transactions. Additional transaction logs cannot be restored. Select this option if you are restoring all of the necessary backups now.
•  RESTORE WITH NORECOVERY which leaves the database non-operational, and does not roll back the uncommitted transactions. Additional transaction logs can be restored. The database cannot be used until it is recovered.

21) What is WITH STANDBY Mode?

This option leaves the database in read-only mode. It undoes uncommitted transactions, but saves the undo actions in a standby file so that recovery effects can be reverted.

22) What is the below Error related the Database Restore?

Msg 3159, Level 16, State 1, Line 1
The tail of the log for the database "Retsore_test" has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.

This error indicates that the Database already exists in the target SQL Server Instance. We have to use WITH REPLACE option to perform the database restore.

23) Why there is a requirement to restore the database?

Requirement of Database restore:

•  In case of Database corruption, data issue or Disaster recovery
• In case of Database refresh from production to QA and Dev environment
• In case of upgrade from lower version to upper version in side by side upgrade

24) What all steps are involved in the Database restore process?

We normally get a request in the below format.

Source Server & Source Database

Target Server & Target Database

1.  First of all we have o extract the permissions of the Target database so that we can retain the permissions of the database after the DB restore from production.
2. Perform the database restore from the source database backup.
3. Once the restore is done then we have to drop the users on the target database which is restored
4. Apply the permissions using the script which was extracted in the first step.
5. Check and resolve any kind of Orphaned users

25) What are Marked Transactions?

SQL Server supports inserting named marks into the transaction log to allow recovery to that specific mark. Log marks are transaction specific and are inserted only if their associated transaction commits. As a result, marks can be tied to specific work, and you can recover to a point that includes or excludes this work.

26) What are the permissions required to perform Database Restore?

The user must be a member of either of the below roles

• Sysadmin – fixed server role
• Dbcreator – fixed server role
• db_owner – fixed database role

27) Is it possible to restore a Database backup of SQL Server 2012 to SQL Server 2008 /2008 R2?

No it’s not possible to restore the upper version database backup to lower version.

28) What is RESTORE FILELISTONLY option?

This SQL returns a result set containing a list of the database and log files contained in the backup set in SQL Server.

RESTORE FILELISTONLY FROM AdventureWorksBackups
WITH FILE=1;
GO

29) What is RESTORE LABELONLY option?

It returns a result set containing information about the backup media identified by the given backup device.

RESTORE LABELONLY FROM DISK='C:\Backup\Adv_Full.bak'

30) What is Point in Time recovery?

Point in Time Recovery option gives us the ability to restore a database prior to an event that occurred that was detrimental to your database.  In order for this option to work, the database needs to be either in the FULL or Bulk-Logged recovery model and you need to be doing transaction log backups.

 High level steps to perform a point in time recovery.

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 tail log backup
5. Restore the last tail log backup with the RECOVERY clause and a STOPAT statement if the entire transaction log does not need to be applied

31) What is piecemeal Restore?

A piecemeal restore sequence restores and recovers a database in stages at the filegroup level, beginning with the primary and all read-write, secondary filegroups. 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. Mostly suitable for data warehousing databases.

 References: Thanks to the all the SQL Server techies who wrote and shared the valuable information in the below blogs which helped me a lot to prepare this series of Questions. Also big thanks to Microsoft Documentation which contains each and everything about their product.

https://blog.udemy.com/sql-server-maintenance-plan/

http://www.mssqltips.com/sqlservertip/939/sql-server-database-backup-and-restore-failure-notifications/

http://www.mssqltips.com/sqlservertip/1150/what-is-in-your-sql-server-backup-files/