SQL DBA

How to get deadlocks history in SQL Server using system_health extended events?

by

This article helps to find Deadlock history or How to get deadlocks history in SQL Server using system_health extended events? Deadlock : refers to a situation where two or more transactions are waiting for each other to release resources, resulting in a state of permanent blocking. This impasse prevents any of the transactions involved from … Read more

How to troubleshoot service principal name issue? | The SQL Server Network Interface library could not register the Service Principal Name (SPN) in SQL Server

by

This article help to resolve, How to troubleshoot service principal name issue? | The SQL Server Network Interface library could not register the Service Principal Name (SPN) in SQL Server What is SPN? In SQL Server, the Service Principal Name (SPN) is a unique identifier used to associate a service instance with a specific domain … Read more

How to restore database in SQL server 2022?

by

This Video have step by step process of How to restore database in SQL server 2022? How to Restore database on SQL Server from Backup? There are 2 ways to restore database. Option 1 : Restore database using SSMS Option 2 : We can restore database using T-SQL Point in time Restore : Sometime we … Read more

How to take database Backup on SQL Server?

by

This is video blog guide about

How to take database Backup on SQL Server?

Video Description : How to take database Backup on SQL Server?

How to perform Backup and restore activity on SQL Server?
As a SQL DBA you should know how backup and restore process work

SQL Server support 3 type of backups
FULL Backup
Differential Backup
Transaction log Backup – This used to perform point in time restore

We can take backup using SSMS or T-SQL backup using SSMS.
If database recovery model is FULL then only we can take Transaction Log backup. Recovery Model of WideWorldImporters is Simple so T log backup is not allowed.

How to take database Backup on SQL Server using T -SQL:

Backup database [WideWorldImporters] to disk = ‘C:\Omkar\backups\backup\WideWorldImporters.bak’ with compression,stats =10

Will try same process with DBATools database which have FULL Recovery MODEL.After taking full and Differential backup it allowed to take transaction log backup because
Recovery model is FULL and extension for transaction log backup is .trn

We can schedule backup of databases using SQL Server agent job periodically. This will help to recover data in case of disaster. There are many options available to take backup
Create maintenance plan is one of the way which take backup base on schedule.
So we have create FULL backup plan which will run daily at 10 AM.
Same as above we can create backup job for differential backup and T log backup

If your database size is huge 500+ GB then best schedule will be
FULL Backup – Weekly
Differential -Daily except full backup schedule
T-Log backup every 15 min(Depend on how much data you want to recover)

This plan will help to recover as much as data in case of disaster.
Backup plan executed successfully and taken backup for each database present in plan.

How do I backup my SQL Server database to Azure blob storage?

Get filename from path string in SQL 2022 | Extract file name in SQL query

by

This article guide , Get filename from path string in SQL 2022 | Extract file name in SQL query Sometime to performing task we need to get or extract filename from full file path For. e.g. If we need to restore database from another SQL server backup file. In that case we need to replace … Read more

How to resolve Cannot export bacpac in SQL Server Error SQL71564? | The element has been orphaned from its login and cannot be deployed

by

This article guides to resolved, How to resolve Cannot export bacpac in SQL Server Error SQL71564? | The element has been orphaned from its login and cannot be deployed What is bacpac? Bacpac is backup file imported from AZURE SQL database or SQL Server. Bacpac is used to to migrate databases from SQL Server to … Read more

The transaction log for database ‘msdb’ is full due to ‘ACTIVE_TRANSACTION’ | Sql server – log is full due to ACTIVE_TRANSACTION

by

This article helps to resolve, The transaction log for database ‘msdb’ is full due to ‘ACTIVE_TRANSACTION.’ or Sql server – log is full due to ACTIVE_TRANSACTION As a DBA we face issue like the transaction log for database ‘msdb’ is full due to ‘ACTIVE_TRANSACTION. Because of that disk is getting full and we are not … Read more

How to capture deadlock using extended events?

by

This article will guide you How to capture deadlock using extended events? What is deadlock? Deadlock is a situation in SQL Server where two or more transactions are waiting for each other to release a resource, such as a table or a row, that is locked by the other transaction. This results in a circular … Read more

PowerShell delete files in folder older than specific date. | How to remove files from folder using PowerShell?

by

This article guides. How to remove files from folder using PowerShell? or PowerShell delete files in folder older than specific date. Sometimes you need to delete old files from particular folder. There are many scenarios where you have to delete those files like deletion based on last modified or deletion based on file name etc. … Read more

How to resolve Microsoft SQL Server, Error: 5120? | How to resolve CREATE DATABASE failed. Some file names listed could not be created.

by

This article guides to resolve, How to resolve Microsoft SQL Server, Error: 5120? | How to resolve CREATE DATABASE failed. Some file names listed could not be created. Sometimes you will get below error while attaching SQL Server database MDF file. This error cause due to file access issue. TITLE: Microsoft SQL Server Management Studio … Read more