SQL script

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 write simple SQL queries? | Learn simple SQL queries

by

This is video tutorial on How to write simple SQL queries? or Learn simple SQL queries SQL queries are versatile, and they can be utilized for numerous operations, including creating and modifying tables, inserting, updating, or deleting data from tables, selecting data from one or multiple tables based on specified conditions, joining tables to combine … 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 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

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

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

How Do I shrink TempDB database? | TempDB not shrinking in SQL server

by

This article guides to resolve, How Do I shrink TempDB database? or empDB not shrinking in SQL server Temp DB is SQL server system database used for sorting, temp table creation, index maintenance jobs to rebuild index and many more. Sometime creation of temp table to sort data in large processing tempdb grow huge and … Read more