SQL Server Backup and Restore Interview Questions

Posted on June 26, 2013 by uday arumilli

SQL Server Backup and Restore Interview Questions

 

Here are the list of SQL Server Backup and Restore Interview Questions which are asked in SQL Server DBA interviews.

1. How does the database recovery model impact database backups?

Ans:

First the database recovery model is responsible for the retention of the transaction log entries.  So based on the setting determines if transaction log backups need to be issued on a regular basis i.e. every minute, five minutes, hourly, etc. in order to keep the transaction log small and/or maintain a log shipping solution.

  • Simple – Committed transactions are removed from the log when the check point process occurs.

  • Bulk Logged – Committed transactions are only removed when the transaction log backup process occurs.

  • Full – Committed transactions are only removed when the transaction log backup process occurs.

 2. Is the native SQL Server 2005 backups are in clear text or in encrypted?

Ans:

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.

 3. How can I verify that backups are occurring on a daily basis?

Ans:

  • Check all backup jobs history

  • 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.

 4. How do you know if your database backups are restorable?

Ans:

  • Issue the RESTORE VERIFYONLY command to validate the backup. For validating LiteSpeed backups use XP_restore_verifyonly

  • 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.

5. What are some common reasons why database restores fail?

Ans:

  • Sufficient space not available on drive

  • User may not have sufficient permissions to perform the restore

  • Unable to gain exclusive use of the database.

  • LSN’s are out of sequence so the backups cannot be restored.

  • Syntax error such as with the WITH MOVE command.

  • Version problem

  • Might be wrong backup location specified

  • Service account may not have permissions on backup folder

 6. What are the permissions required to perform backup and Restore?

Ans:

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

 7. How can you be notified if a native SQL Server database backup or restore fails via the native tools?

Ans:

  • Setup SQL Server Alerts to be sent to Operators on a failure condition.

  • Include RAISERROR or TRY\CATCH logic in your backup or restore code to alert on the failure.

 8. Does all successful SQL Server backup entries can be prevented from writing to the SQL Server Error Log by a single trace flag?

Ans:

Yes – Just enable the trace flag 3226.

 9. What are some common post restore processes?

Ans:

  • Sync the logins and users

  • Validate the data is accurate by running dbcc commands

  • Notify the team\user community

  • Cleanse the data to remove sensitive data i.e. SSN’s, credit card information, customer names, personal information, etc.

  • Change database properties i.e. recovery model, read-only, etc.

10. Explain how you could automate the backup and restore process?

Ans:

  • Backups can be automated by using a cursor to loop through each of the databases and backup each one

  • Restores can also be automated by looping over the files, reading from the system tables (backup or log shipping) or reading from a table as a portion of a custom solution

 11. What is the database that has the backup and restores system tables?  What are the backup and restore system tables?  What do each of the tables do?

Ans:

 The MSDB database is the database with the backup and restores system tables. Here are the backup and restore system tables and their purpose:

  • backupfile – contains one row for each data file or log file backed up

  • backupmediafamily – contains one row for each media family

  • backupmediaset – contains one row for each backup media set

  • backupset – contains one row for each backup set

  • restorefile – contains one row for each restored file

  • restorefilegroup – contains one row for each restored filegroup

  • restorehistory – contains one row for each restore operation

 12. How can full backups be issued without interrupting the LSN’s?

Ans:

Issue the BACKUP command with the COPY_ONLY option

 13. How is a point in time recovery performed?

Ans:

Continue reading →

Posted in Interview Q&A, SQL Development, SQL Server DBA | Tagged , , , , , , , , , , , , , , , , , , | 14 Comments

How to Use DBCC CHECKDB in SQL Server

Posted on June 26, 2013 by uday arumilli

How to Use DBCC CHECKDB in SQL Server

“Integrity” Road Sign with dramatic clouds and sky.

Use: By far the most widely used command to check the status of your database is DBCC CHECKDB. This statement checks the allocation and structural integrity of all data and index pages for each table and indexed view, as well as text and image objects. DBCC CHECKDB ensures that all data and index pages are correctly linked and pointers are consistent. Both user and system tables are checked within the specified database. DBCC CHECKDB executes both DBCC CHECKALLOC as well as DBCC CHECKTABLE for every object in the database

Let’s take a look at a couple of the more common options to know how to use dbcc checkdb in sql server.

Steps to execute the DBCC CHECKDB:

  1. The first option on the command looks like this for the CanonECFG5 database:

USE MASTER;

GO

DBCC CHECKDB ('TestDB', NOINDEX) WITH NO_INFOMSGS;

 The command above checks the CanonECFG5 database but not its indexes. This won’t take long at all. The output returned will tell you if there are problems with the database. If so, check to make sure your backup is handy and then you can run the next level of this command.

  1. In this case we don’t have any errors. But if find any errors in execution, execute the next level of DBCC command as below.

USE MASTER;
GO
ALTER DATABASE TestDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DBCC CHECKDB ('TestDB', REPAIR_FAST) WITH NO_INFOMSGS;
GO
ALTER DATABASE TestDB SET MULTI_USER;

This command will attempt to fix many errors, but won’t allow any data to be lost. If that doesn’t work, the next level of the command is in step 3.

  1. Execute the below DBCC command

USE MASTER;
GO
ALTER DATABASE TestDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DBCC CHECKDB ('TestDB', REPAIR_REBUILD) WITH NO_INFOMSGS;
GO
ALTER DATABASE TestDB SET MULTI_USER;
GO

This command takes longer, but will also correct the indexes (if it can). It will also not allow data loss. Should this command not correct your errors, you’ll definitely want to have that backup handy, because you’re going to need it. The next level of this command will potentially lose data.

  1. Now execute the below DBCC command as a last and final repair trail.

USE MASTER;
GO
ALTER DATABASE TestDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DBCC CHECKDB ('TestDB', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
GO
ALTER DATABASE TestDB SET MULTI_USER;

As you can probably guess, this command could potentially lose data or make your applications unusable.

Info:

Use the REPAIR options only as a last resort. To repair errors, we recommend restoring from a backup. If you must use REPAIR, run DBCC CHECKDB without a repair option to find the repair level to use. If you use the REPAIR_ALLOW_DATA_LOSS level, we recommend that you back up the database before you run DBCC CHECKDB with this option.

 **Note: If we want to repair the database the db should be in single user mode.

 Q. How to know the estimated TempDB space required to run CHECKDB command?

Ans:

Below command calculates the amount of space in “tempdb” required to run DBCC CHECKDB statement. However, the actual statement isn’t executed.

DBCC CHECKDB ('TestDB') WITH NO_INFOMSGS, ESTIMATEONLY

 Sample Output:

Estimated TEMPDB space (in KB) needed for CHECKDB on database TestDB = 14254.

 Q. How to fast up the DBCC check db execution process?

Ans:

The below command forces to obtain an exclusive lock on database which makes bit faster the process.

 DBCC CHECKDB ('TestDB') WITH NO_INFOMSGS, TABLOCK

 Q. What is Phycial_Only option in DBCC CHECKDB?

Ans:

This command limits the checking to the integrity of the physical structure of the page and record headers, but it can also detect torn pages, checksum failures, and common hardware failures. Using the PHYSICAL_ONLY option may cause a much shorter run-time for DBCC CHECKDB on large databases and is recommended for frequent use on production systems. Specifying PHYSICAL_ONLY causes DBCC CHECKDB to skip all checks of FILESTREAM data.

 DBCC CHECKDB ('TestDB') WITH NO_INFOMSGS, PHYSICAL_ONLY

Q. How to check data purity using DBCC CHECKDB?

Ans:

Below command causes DBCC CHECKDB to check the database for column values that are not valid or out-of-range. For example, DBCC CHECKDB detects columns with date and time values that are larger than or less than the acceptable range for the datetime data type; limits the checking to the integrity of the physical structure of the page and record

 DBCC CHECKDB ('TestDB') WITH NO_INFOMSGS, DATA_PURITY

Please check here for more DBCC commands.

Posted in SQL Server DBA | Tagged , , , , , , , , , , , | 2 Comments

SQL Server Architecture Questions and Answers

Posted on June 25, 2013 by uday arumilli

SQL Server Architecture Questions and Answers

 

Sql Server Architecture Questions and Answers

Here are the list of Sql Server Architecture Questions and Answers which are asked in SQL Server developer / DBA  interviews

Q. Can you draw SQL Server architectural diagram with all the components?

Ans:

 

062513_0610_SQLServerAr3.png

Network Flow – SQL Server Architecture

Q. Can you explain the network flow in SQL Server Architecture?

Ans:

062513_0610_SQLServerAr4.jpg

These steps are defined as follows:

1. The user selects an option in a client application. This option calls a function in the client application that generates a query that is sent to SQL Server. The application uses a database access library to send the query in a way SQL Server can understand.

2. The database library transforms the original request into a sequence of one or more Transact-SQL statements to be sent to SQL Server. These statements are encapsulated in one or more Tabular Data Stream (TDS) packets and passed to the database network library to be transferred to the server computer.

3. The database network library uses the network library available in the client computer to repackage the TDS packets as network protocol packets.

4. The network protocol packets are sent to the server computer network library across the network, where they are unwrapped from their network protocol.

5. The extracted TDS packets are sent to Open Data Services (ODS), where the original query is extracted.

6. ODS sends the query to the relational engine, where the query is optimized and executed in collaboration with the storage engine.

7. The relational engine creates a result set with the final data and sends it to ODS.

8. ODS builds one or more TDS packets to be sent to the client application, and sends them to the server database network library.

9. The server database network library repackages the TDS packets as network protocol packets and sends them across the network to the client computer.

10. The client computer receives the network protocol packets and forwards them to the network libraries where the TDS packets are extracted.

11. The network library sends the TDS packets to the database access library, where these packets are reassembled and exposed as a client result set to the client application.

12. The client application displays information contained in the result sets to the user.

Pages and Extents

Q. What is a page in database systems and tell me something about pages?

Ans:

The fundamental unit of data storage in SQL Server is the page.

Page size is 8kb means 128 pages = 1 MB.

Page starts with the header of 96 bytes that is used to store page number, page type, the amount of free space on the page, and the object id that owns the page. The maximum size of a single row on a page is 8060 bytes. But this restriction is relaxed for tables which are having varchar, nvarchar, Varbinary, Taxt or Image columns.

Q. Any idea what is “ROW_OVERFLOW_DATA allocation unit”?

Ans:

When the total row size of all fixed and variable columns in a table exceeds the 8,060 byte limitation, SQL Server dynamically moves one or more variable length columns to pages in the ROW_OVERFLOW_DATA allocation unit.

This is done whenever an insert or update operation increases the total size of the row beyond the 8060 byte limit. When a column is moved to a page in the ROW_OVERFLOW_DATA allocation unit, a 24-byte pointer on the original page in the IN_ROW_DATA allocation unit is maintained. If a subsequent operation reduces the row size, SQL Server dynamically moves the columns back to the original data page.

Q. Can you briefly describe about EXTENT?

Ans:

An extent is eight physically contiguous pages, or one extent = 64 KB. That means 16 Extents= 1 MB.

There are two types of Extents. Uniform Extents and Mixed Extents.

Uniform extents are owned by a single object;

Mixed extents are shared by up to eight objects

Q. What are the different Types of Pages available?

Ans:

GAM and SGAM (Global Allocation Map & Shared GAM):

GAM: Extents have been allocated: 1 – Free space 0 – No space

SGAM: Mixed Extents have been allocated: 1 – Free Space + Mixed Extent and 0 – No space

Each GAM / SGAM covers 64000 extents – 4 GB

PFS (Page Free Space): Percentage of free space available in each page in an extent.

DCM (Differential Changed Map):  This tracks the extents that have changed since the last BACKUP DATABASE statement. 1 – Modified, 0 – Not modified

BCM (Bulk Changed Map): This tracks the extents that have been modified by bulk logged operations since the last BACKUP LOG statement. 1 – Modified, 0 – Not modified (Used only in bulk logged Recovery model)

In each data file pages are arranged like below

Along with that we have three different data pages

Data

Index

Text/ Image (LOB, ROW_OVERFLOE, XML)

 

Files and File Groups

Q. Have you ever heard the word “Files” or “File Groups” in SQL Server?

Ans:

Continue reading →

Posted in Database Design, Interview Q&A, SQL Development, SQL Server DBA | Tagged , , , , , , , , , , , , , , , | 60 Comments