Real time MS SQL Server DBA Experienced Interview Questions and Answers - Interview 2

Join this channel to get access to perks:
/ @mssqldbatechsupport
Join the whats app Group for the scripts and assistance:
chat.whatsapp....
You Can Also Download the scripts from below folder
drive.google.c...
We strongly believe there is always chance of betterment, so suggestions are most welcome.
Happy learning, and best of luck in your professional journey!
The journey of improvement is ongoing and never be an end.
Thank you!
MS SQL DBA Tech Support

[@k.nagapavankumar6997]

Thanks Harsha, when you get a chance please make a video upgrade sql server which includes AlwaysOn one primary and one secondary

[@MSSQLDBATechSupport]

sure

[@sqltechzpestechz7053]

thank u so much

[@MSSQLDBATechSupport]

Welcome

[@sanjeevdhiman6018]

Amazing work sir❤

[@MSSQLDBATechSupport]

Thanks a lot 😊

[@roseflower8544]

Thank you so much for the video. Please fix the sound. It is very hard to understand the answers.

[@MSSQLDBATechSupport]

Sorry for that.. will work on that.

[@Greeshma-dl5lm]

Can you please answer the question, how we can see the index rebuild status in terms of number of rows completed

[@MSSQLDBATechSupport]

Hello Greeshma, you need to add set statistics profile on at the starting index rebuild query ==================== set statistics profile on Index Rebuild Query ==================== In the below link there is query to view the result.just replace the spid. sqlserverentire.blogspot.com/2020/07/are-you-scripting-tables-from-ssms.html

[@saikusuma8153]

Hi, What is the answer for why sql server is not suggesting any missing indexes?

[@MSSQLDBATechSupport]

Hello sai Answering your query: =================================== SQL Server will not give any missing index suggestions in execution plans. At that time, we can use BitzCache to get suggestions. Since it is a third-party tool that many clients may not agree to use, At that time we have to analyze the execution plan. After analyzing the execution plan, we can see *output list columns* and prepare non-clustered indexes to deploy in the test environment and observe the output ======================================== Thanks & Regards MS SQL DBA TECH SUPPORT

[@vivekjadhav9433]

Hello sir Can u provide the answer for sql 2005 upgrade to sql 19 but client need to rollbackup again to sql 2005 pls provide answer

[@MSSQLDBATechSupport]

Hello Vivek Its bit hard to answer to your question without knowing about environment however I am consolidating and gave one final answer. *NOTE* To upgrade from 2005 to 2019 directly is not possible First, we need to upgrade to 2012 and later we can upgrade to 2019. 1.Perform OS level Snapshot on sql server OS. 2.Perform Full Database backups for system & user databases of 2005 3.Document all custom configurations, maintenance plans, and SQL Server Agent jobs. 4.Check hardware and software requirements for SQL Server 2012 and 2019. 5.Restore the databases from the SQL Server 2005 backups to the SQL Server 2012 instance. 6.Install a new instance of SQL Server 2019 on a separate server or virtual machine. 7.Use the Database Migration Assistant (DMA) or SQL Server Data Tools (SSDT) to assess the compatibility of your databases with SQL Server 2019. 8.Monitor and test the performance of SQL Server 2019 and optimize configurations as needed for your environment. *Rollback Plan*: 1.If possible, create a virtual machine snapshot or backup of the entire SQL Server 2005 environment. 2.Since we have taken os level snapshot so that the server would be running fine. Only thing is the data which was added or modified or deleted in sql server.so for that data we can get it by using bacpac or generate scripts Thanks & Regards MS SQL DBA TECH SUPPORT

[@ITHYKKATT]

hi, can u pls answer what is the difference between point in time restore and point in time recovery

[@MSSQLDBATechSupport]

Hi Greesh Thanks for reaching us. answering to your questions below: *Point-in-Time Restore* In SQL Server, a point-in-time restore refers to the ability to restore a database to a specific point in time. This involves rolling back the entire database to a state that existed at a particular moment. Point-in-time restores are typically performed using database backups, and SQL Server allows you to restore to a specific full or differential backup and then apply transaction log backups to reach the desired point in time. Example:you get deleted some date at 10.30.At 11.30 you got an email from application team that u need to get deleted data. so you need to use log back ups to get back at 10.30 *Point-in-Time Recovery* Point-in-time recovery in SQL Server is achieved by applying transaction log backups to roll forward a database to a specific point in time. Transaction log backups capture the changes made to the database after the last full or differential backup. By applying these transaction log backups sequentially, you can recover the database to any point in time within the range of available log backups. This process is often used to recover databases when failure often. Example:You database got crashed. u need to recover the data up to to failure point if possible based on backups available. Generally Both are same however depend upon the situations we used the terminology accordingly. so that every body can aware about the situations. Thanks & Regards MS SQL DBA TECH SUPPORT

[@ganavigowda1]

This interviewee voice or accent isnt clear. Pronounciation of words are very unclear to understand his answers.

[@MSSQLDBATechSupport]

It's always helpful to receive input from viewers. I'll make sure to take note. Thanks for watching and sharing your thoughts!"