How to automate database backup and restore using T-SQL

Scenario 

We may have scenario to restore a development or test database or just create another copy of the database. Restoring the latest backups of Production databases on a various test servers is one of the frequent activity we perform on a regular basis, various application development teams often request a restore the latest backup on new or existing test or quality servers, many times we receive this kind of requests in the eleventh hour when packing up to go back home.

Script to provide backup sequence with the shortest restore time - Interview Question

Scenario 

You may be working in a team and there are scenarios where database backups are generated on an adhoc basis. Taking database backups in an adhoc manner breaks a backup plan that is scheduled in an production environment (We are not talking about COPY_ONLY backups). Later, it is difficult to keep a track and identify the right backup sequence to perform restore.
This script will help you to retrieve information about Full, Differential and Log backups for the databases residing on the same instance. It also shows the backup chain that will help us to restore this databases in the shortest time.
This question was asked to me during an interview.

Piecemeal Restore in SQL Server

A piecemeal restore sequence restores and recovers a database in stages at the file group level, beginning with the primary and all read-write, secondary file groups. In this process, we can restore the primary file group first making database online and the remaining file groups can be restored while recovery while the transactions are running on primary. Mostly suitable for data warehousing databases.

Features and Enhancements in SQL Server 2016, 2017 & 2019 Release.

Microsoft is moving very fast with feature enhancements, improving security, performance, enhancing the capabilities of machine learning, data integration, etc.
The post summarizes the features and its enhancement in each SQL Server release. View Microsoft SQL Server versions to compare features and find the right version to meet your data platform needs.

Finding the percentage of NULL values for each column in a table.

Scenario

Let`s create a scenario where you are working on data validation and data analysis and got the requirement in which you need to find the Percentage of Null values in every column for all the tables in SQL Server Database. This can be really helpful in analysis to know how much data you are really getting or you are getting no values (Null) and plan the storage. Also depending upon the analysis we can find some records those should not be Null but you have receive Null for those Columns, so data corrections can be next step.

How to get Min and Max Length for each Column Values for all the tables in SQL Server

In this post we will see how to find minimum and maximum length for each columns for all the tables in SQL Server.

Choosing Data Types 

Choosing a poor data type can have a significant impact on the database design and performance. Developers and DBAs can improve database performance by understanding the data types supported by SQL Server and the implications of choosing different ones. A best practice is to “right size” data types by asking business questions and determining the data types that are best suited to the organization’s and application’s needs.

Temporal Tables in SQL Server 2016

Overview

SQL Server 2016 has introduced a new type of a table that allows full tracking of any changes that the primary table has undergone. It does this by creating a separate 'linked' table called a history table that records all modifications.The great benefit is that it allows the dba to know the state of the data at any point in time.Previously, if we needed to track changes to the table, the developer needed to create triggers and store the data in separate table. A time consuming task.