DBA

How to resolve Report server URL was not working? | Fix rsCannotValidateEncryptedData error

by

This Article help , How to resolve Report server URL was not working? or Fix rsCannotValidateEncryptedData error What is SSRS? SQL Server Reporting Services are getting used to create various type of reports from database or data warehouse. This reports are useful to identify various trends, business metrics etc. This reports can be accessed from … Read more

Availability Group database stuck in Not Synchronizing | Log file full due to Availability Replica

by

This article guide, How to resolve, Availability Group database stuck in Not Synchronizing or Database is not coming in restoring mode post removing it from AG group Why this issue occurred? Database size was 5+ TB of one of the database on SQL Server. Index jobs runs weekly. Due to index maintenance job , log … Read more

Why Data file is not shrinking in SQL Server? | DBCC ShrinkFile doesn’t shrink file

by

This article help to resolve, Data file is not shrinking in SQL Server | DBCC ShrinkFile doesn’t shrink file You can follow the steps and check which best solution work for your case. What is issue? or Why Data file is not shrinking in SQL Server or DBCC ShrinkFile doesn’t shrink file Database size was … Read more

How to fix An explicit value for the identity column error? | Insert statement failed with Error 8101

by

This Article guide , How to fix An explicit value for the identity column error? | or Insert statement failed with Error 8101 What is identity column? An identity column, also referred to as an auto-increment column or a sequence, is an integral component of a database table that automatically generates a unique numeric value … Read more

How to resolve, the server principal owns one or more availability group(s) and cannot be dropped?

by

This article about, How to resolve, the server principal owns one or more availability group(s) and cannot be dropped? What is availability group? Availability Groups are a feature in Microsoft SQL Server that provide high-availability and disaster recovery for user databases. They were introduced in SQL Server 2012 as a database-level solution, allowing multiple databases … Read more

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?

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