Home
MS SQL
Interview FAQs

FAQs of level

Table hints allow you to override the default behavior of the query optimizer for statements. They are specified in the FROM clause of the statement. While overriding the query optimizer is not always suggested, it can be useful when many users or processes are touching data. The NOLOCK query hint is a good example because it allows you to read data regardless of who else is working with the data; that is, it allows a dirty read of data — you read data no matter if other users are manipulating it. A hint like NOLOCK increases concurrency with large data stores.

SELECT * FROM table_name (NOLOCK)

Microsoft advises against using NOLOCK, as it is being replaced by the READUNCOMMITTED query hint. There are lots more query hints with plenty of information online.

Basically, the Database Consistency Checker (DBCC) provides a set of commands (many of which are undocumented) to maintain databases — maintenance, validation, and status checks. The syntax is DBCC followed by the command name. Here are three examples:

DBCC CHECKALLOC — Check disk allocation consistency.

DBCC OPENTRAN — Display information about recent transactions.

DBCC HELP — Display Help for DBCC commands.

Truncate is a quick way to empty a table. It removes everything without logging each row. Truncate will fail if there are foreign key relationships on the table. Conversely, the delete command removes rows from a table, while logging each deletion and triggering any delete triggers that may be present.

Transactions allow you to group SQL commands into a single unit. The transaction begins with a certain task and ends when all tasks within it are complete. The transaction completes successfully only if all commands within it complete successfully. The whole thing fails if one command fails. The BEGIN TRANSACTION, ROLLBACK TRANSACTION, and COMMIT TRANSACTION statements are used to work with transactions. A group of tasks starts with the begin statement. If any problems occur, the rollback command is executed to abort. If everything goes well, all commands are permanently executed via the commit statement.

Log shipping is the process of automating the backup of database and transaction log files on a production SQL server, and then restoring them onto a standby server. Enterprise Editions only supports log shipping. In log shipping the transactional log file from one server is automatically updated into the backup database on the other server. If one server fails, the other server will have the same db and can be used this as the Disaster Recovery plan. The key feature of log shipping is that it will automatically backup transaction logs throughout the day and automatically restore them on the standby server at defined interval.

These are some common reasons why database restores fail:

  • Unable to gain exclusive use of the database.
  • LSNs are out of sequence so the backups cannot be restored.
  • Syntax error such as with the WITH MOVE command.

These are the recommendations to design a backup and recovery solution?

  • Determine What is Needed
  • Recovery Model
  • Select Backup Types
  • Backup Schedule
  • Backup Process
  • Document
  • Backup to Disk
  • Archive to Tape
  • Backup to Different Drives
  • Secure Backup Files
  • Encrypt or Password Protect Backup Files
  • Compress Backup Files
  • How Much to Keep on Disk
  • Online Backups
  • Run Restore Verifyonly
  • Offsite Storage

  1. The MSDB database is the database with the backup and restore system tables.
  2. 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

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 either 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

These are some common post restore processes in SQL SERVER-

  • Sync the logins and users
  • Validate the data is accurate
  • Notify the team/user community
  • Cleanse the data to remove sensitive data i.e. SSN, credit card information, customer names, personal information, etc.
  • Chang database properties i.e. recovery model, read-only, etc.

PreviousDisplaying 12 of 15Next
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
Need Help? Contact Us.

Log in

*
*

Forgot password?

*

New User

*
*
*
*