SQL Server Interview Questions

7. How do you failover in log shipping/how do you bring the secondary server online

1. Disable backup on Primary 2. Manfully run copy job on secondary to copy any reaming backup 3. Manfully run restore job on secondary to restore reaming transaction log back up 4. Disable copy and restore job on secondary 5. Take tail log backup of database on primary With no truncate option backup log testdb to disk =' \\asdfasdfasd.trn' with no truncate 6. Restore tail log back up on secondary with recovery option. Restore log testdb from disk=' \\\asdfasdfasjdf.trn' with recovery 7. Set up log shipping from secondary ( now primary) to primary (now secondary) if this is first time 8. Transfer login using script 9. Transfer other jobs to secondary ( if necessary) 10. Find the problem why fail occur in primary database , solve the problem and back over to the previous primary

4. What are the requirements for log shipping?

1. SQL Server standard, workgroup, or enterprise edition 2. All servers must be on the same version of sql server 3. Databses must be in full or bulk logged recovery model 4. All database services must be up and running and using same domain account 5. Fire wall must be open and functional 6. Backup and copy folder must be shared with read and write permissions 7. File stream must be disabled at server and database levels 8. Secondary databases must be in no recovery option or standby option

5. How to configure log shipping?

1. Verify that all requirements are met 2. Choose what the primary, secondary, and optional monitor servers will be 3. Configure browse services, database engine services, and agent services with proper security 4. Create shared file with read and write permission for transaction log backups 5. Create a copy folder and share with read and write permission for each secondary server where trans log backups can be copied 6. Configure backup schedule for primary databases 7. Configure copy and restore schedules for secondary databases 8. Configure alert job schedule for monitor server if configured

10. What are the things should be considered when we are planning to rebuild system databases?

Before executing the rebuild operation, its important that we locate the backup of system databases as well as make a note as to where the MDF and LDF files are located, the server configuration, builds/hotfixes/sp applied. After the rebuild I review the summary.txt to make sure it completed without any issues, move system databases to the correct location, and restore system databases from the existing backups to get the environment back with changes

7. What are the operations you cannot perform while backup of a database in progress?

During a backup you cant create or delete database files, create indexes, or shrink databases

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

During the backup configuration, I make sure to check the box "verify backup integrity" to ensure that backups will be ready for restore procedures.

7. How can you automate the backup and restore process?

I can automate backup and restore operations utilizing the native SQL server agent or setup a maintenance plan to do so.

11. What is your experience with log shipping?

I've, properly configured the primary, secondary, and monitor servers. Configured required services and their accounts Configured share folder and set up the proper security Monitored all jobs and alerts Followed steps for planned and unplanned failovers Properly transferred logins and other jobs to secondary

10. You do not have a full or any other backup, except a backup of data file, how do you restore the database?

In this case, we need to attach the database using the data file. We do not need to have the transaction log file. It will create the transaction log based on the data file.

What is the purpose of log shipping?

Log shipping is a high availability disaster recovery option that backs up the transaction logs of a primary database and restores it onto one or more secondary databases on separate secondary instances.

8. Is it possible to take a backup for resourceDB?

No it's not possible, but a physical backup of the MDF and LDF files can be done.

1. What is your current back up strategy, disaster recovery plan, and retention policy for databases in your organization?

Our current backup strategy in a production environment consists of 2 full backup runs per week, differential backups every three hours, and transaction log backups every 30 minutes. We use full recovery model and our retention policy is 12 days. For our dev and test environment we take one full back up every week, differential backups every day at midnight, and we do not take transaction log backups as the recovery model us simple. Our retention policy is 22 days For every backup we make sure to verify the integrity of the backup to make sure a restore operation will work with those backup files For the system databases, we perform 1 full backup for the MASTER AND MSDB databases, and we only back up the TempDB and MODEL databases if we make changes to them and we cannot issue differential and transaction log backups to system databases

8. What is the difference between restore with no recovery and restore with standby?

Restore with no recovery is when the database is 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. Restore with standby is when the database is in read only mode. It can rollback uncommitted transactions, but saves the undo actions in a standby file so that recovery effects can be reverted.

10. What is the difference between the secondary being in Restoring with no recovery vs. Standby?

Restoring with no recovery means the database is not accessible. Standby means it is read-only. You make this decision when you set up the log shipping. If the database is in Standby mode, users can query it - except when a log backup is being restored. You need to decide if a restore job will disconnect users, or if the restore is delayed until after the users are disconnected.

8. What are some advantages to log shipping?

Some advantages to log shipping is it doesn't require expensive hardware to operate, easy to configure, provides high availability, and a secondary can be used for reporting purposes balancing the overall load.

9. What are some disadvantages to log shipping?

Some disadvantages for log shipping is users will experience some downtime, some data loss is a possibility, and it does not support automatic failover.

6. What are some common post restore processes?

Some post restore processes are to sync the logins and users, validate the data by running DBCC commands, and notifying the team that the restore occurred.

4. What are some reasons why the database restores fail?

Some reasons why a restore might fail are not enough space available on the drive, the user might not have the right permissions to perform the operation, or the service account might not have access to the backup folder/

6. As a DBA what is the best practice to take a backup?

The best practice for backups is first, in a production environment set full recovery model and perform regular transaction log backups at least once an hour. Perform daily full backups of all system and user databases Periodically test backups to ensure that they are good and can be restored First backup to disk, then move to another backup media such as tape Store backups offsite If using sql server encryption, be sure to back up the service master key, database master keys, and certificates If backup time takes longer than your expected backup window or the backup file is taking too much space consider a third party backup program/ Document step by step the process of restoring system and user databases onto the same, or different server.

5. What are the permissions required to issue a backup and restore?

The permissions for backup are that a user must be assigned to sysadmin, db_owner, or db_backupoperator. To restore, the permissions for that user must be assigned to sysadmin, Dbcreator, or db_owner.

2. What is primary server, secondary server and monitor server?

The primary server is the production server which holds the original copy of the database. Log shipping configuration is done from the primary server. The secondary server is the standby copy of the primary. It serves as a redundant copy of the primary database and routinely receives the primary transaction log for restoring. The monitor server is an optional server that records the status of the backup, copy and restore operations carried out by log shipping. It raises alerts when any operation fails.

4. What is point in time recovery?

The process to perform a point in time recovery is to first take a transaction log backup and tail log backup with the no_truncate option, then restore the last full backup with no recovery, restore the last differential backup with no recovery, restore all transaction log backups and tail log backups with recovery.

5. Your log file is growing too fast. It looks like your log file size is larger or double of your database size. What are the reasons? What are the steps to fix this? How do you clean the transaction log when you only have a full backup?

The reason for this is because the recovery model for the database is set to full, so that the transaction log file keeps growing. Logs are cleared when we issue a transaction log backup. First we must take a transaction log backup to clear the log file, but the log file size does not change so to resize the log file we change the recovery model to simple. Then I issue a DBCC shrink file command to resize the log file. Once the command is complete I change the recovery model back to full and take a full backup.

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

The recovery model basically determines how the database will retain the transaction log file. Each setting will determine if transaction log backups are required or not. Regardless of the recovery model, transactions will be logged to the log file, but instead determines when the logged transactions will be released/removed from the log file.

9. I have a routine backup schedule which consists of a full backup once per day, then hourly scheduled differential backups. Often, I am tasked with taking a ad hoc full backup of the production database to update the test or dev server. This often times interrupts my planned backup and recovery sequence in case of a needed recovery. What is a solution for this so where the sequence won't get interrupted by ad hoc operations?

The solution for this is to take copy only backups

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

There are a number of ways you can check if a backup is occurring on a daily basis. I usually either, review the file system where the backups are kept so I can validate they exist, Query the msdb.dbo.backupset table for the backup related entries , or I review the sql server error log for backup related entries.

3. What jobs are running for log shipping?

There are three main jobs that carry out log shipping. First is the backup job which is created on the primary server instance and performs a backup operation of its transaction log Second is the copy job which is created on the secondary server instance and copies the backup files from the primary to the secondary server Third is the restore job which is also created on the secondary server instance and it restores the logs from the primary to the secondary databases Last is the alert job which is only active if a monitor server is configured but it raises alerts if any operations are not successful.

6. How do you monitor log shipping status?

To monitor log shipping you can view the monitor server history tables if set up Or run SELECT * FROM MSDB.DBO.Log_shipping_monitor_primary to view primary server status and SELECT * FROM MSDB.DBO.Log_shipping_monitor_secondary for secondary server status

11. How can we rebuild resource system database?

To rebuild the resource system database run the SQL server installation setup.exe file, then on the installation wizard click on maintenance and click on repair.

2. As a DBA what is the best practice for disaster recovery?

We must create a disaster recovery plan and include every detail for the rebuild of our servers and document the plan so that every member on the team will be able to read and follow it. Once a plan is made and set in place, we test the disaster recovery plan every six months.

3. The database goes into suspect mode? How do you recover it?

When the database goes into suspect mode, we have to check the error log or event viewer log, the issue is either a missing log or data file. If the log file is missing and we don't have any backups or we don't want to use the last backup ,we recover the database by ALTER DATABASE YourDbName SET EMERGENCY DBCC CHECKDB YourDbName ALTER DATABASE YouDbName SET SINGLE_USER WITH ROLLBACK IMMEDIATE DBCC CHECDB (YourDbName, REPAIR_ALLOW_DATA_LOSS ALTER DATABASE YourDbName SET MULTI_USER If the data file is missing, we recover the database based on our backup strategy and recovery model: If the database is full recovery model and we only have a full backup we recovery the database by backing up the current transaction log with a tail log backup set to no_truncate option then restore the last full backup with no recovery and then restore the tail log backup with recovery If the database is in full recovery model and we have a full backup, differential backup, transaction log backup, we can recover the database by first backing up the current transaction log and tail log backup with no_truncate option. Restore the last full backup with no recovery, restore the last differential back up with no recovery, restore all transaction log backups in order except the last one with no recovery, and lastly restore the last transaction log backup/tail log backup with recovery If the database is in simple recovery model we can recover the database up to the last full backup

9. How do you rebuild the system databases?

c:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release>setup.exe /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=<Default/Named> /SQLSYSADMINACCOUNTS= <Service Account> [/SAPWD=<Mandatory when using Mixedmode>]SQLCOLLATION