datablogs: Database Administration

Showing posts with label Database Administration. Show all posts

Thursday, February 19, 2026

MySQL Binlog Restore using Python

Selva | February 19, 2026 | No comments | Automation, Database Administration, MySQL, Zero Downtime

Its easy to recover mysql data like Amazon RDS , Its Sort and Easy Method if you are not able to understand manual method

https://github.com/selvackp/mysql-binlog-restore-using-python

Any additonal help please command with us

Tuesday, December 23, 2025

Steps to Change TempDB Location in SQL Server

Selva | December 23, 2025 | No comments | Automation, Database Administration, SQL Server

Its very easy and simple , Just sharing you the steps without so much theories

Why we are moving it , when its in C Drive or SQL Data Drive it will cause IO issues , so that we are moving TempDB files into different location

Step 1: Check Current TempDB Files Location

Run the following query:

USE tempdb;
EXEC sp_helpfile;

Step 2: Plan New Location

- Create a new folder on the desired drive (e.g., D:\SQLData\TempDB).

- Ensure SQL Server service account has Full Control on that folder.

Step 3: Modify TempDB File Paths

Run these commands (adjust paths as needed):

USE master;
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME =
'D:\SQLData\TempDB\tempdb.mdf');
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME =
'D:\SQLData\TempDB\templog.ldf');

Repeat for additional files if they exist.

Step 4: Restart SQL Server Service

Stop and Start the SQL Server instance. Restart is mandatory for changes to take effect.

Step 5: Verify New Location

USE tempdb;
EXEC sp_helpfile;

This should show the new paths.

Step 6: Clean Up Old Files

After SQL Server restarts successfully and TempDB is created in the new location, manually delete

the old tempdb files from the old drive.

SQL Server High CPU Issue Caused by Implicit Conversion (nvarchar vs varchar)

Selva | December 23, 2025 | No comments | Database Administration, Performance Tunning, SQL Server

Problem Overview

Recently, we encountered a high CPU utilization issue on a SQL Server instance — even though only one small SELECT query was executing.At first glance, it seemed unusual that such a simple query could consume so much CPU. However, on investigating the execution plan, we noticed that the optimizer was performing a conversion from varchar to nvarchar in the predicate.

Root Cause: Implicit Data Type Conversion

In SQL Server, when a query compares two different data types (for example, a column defined as varchar and a variable or literal passed as nvarchar), SQL Server performs an implicit conversion to make them compatible.

Unfortunately, this can:

Prevent index seeks (forcing a full scan instead)
Increase CPU usage
Cause plan cache bloat (multiple plans for same query)
Lead to unpredictable performance degradation

Example:

-- Table definition
CREATE TABLE Customer (
CustomerID INT,
CustomerCode VARCHAR(20)
);
-- Query from application
SELECT * FROM Customer WHERE CustomerCode = N'CUST001'; --
Notice the 'N' prefix (nvarchar)Even though an index exists on CustomerCode, SQL Server converts the column during
execution:
CONVERT_IMPLICIT(nvarchar(4000), [CustomerCode], 0)

As a result, the optimizer cannot perform an index seek, leading to a full table scan and

high CPU utilization.

Investigation Steps

Checked sys.dm_exec_requests and sys.dm_exec_query_stats – saw high CPU for a single query.
Reviewed the Actual Execution Plan – found CONVERT_IMPLICIT on the predicate.
Confirmed column type: varchar(20)
Confirmed query parameter or literal type: nvarchar

Resolution

Since the customer application could not modify the query, we proposed aligning the data types at the table level.
ALTER TABLE Customer ALTER COLUMN CustomerCode NVARCHAR(20);
After modifying the column data type to match the query, the optimizer was able to use the index correctly.

Result:

✔ CPU utilization dropped significantly

✔ Query execution time improved

✔ No functional impact

Best Practices to Avoid Implicit Conversions

Keep data types consistent between table columns and application parameters.
Avoid using the Unicode prefix (N'...') unless necessary.
Use parameter sniffing tests to identify mismatched parameter types.
In critical systems, standardize column data types across schemas and applications.

Summary

Even a small implicit conversion can lead to large performance issues in SQL Server.By ensuring data type alignment between the database schema and query parameters,you can prevent unnecessary scans and CPU spikes.

Friday, October 17, 2025

How to upgrade MySQL from 8.0 to 8.4 ?

Selva | October 17, 2025 | No comments | AWS, Database Administration, MySQL, Upgrade

Dear all my DBA Friends its time to upgrade to 8.4 . Yes We are getting notification from all the cloud partners as well on the customer side requests move to latest version

Here is detailed step for upgradtion on Ubuntu 22 . Before upgrading make sure you have noticed all the incompatible changes between 8.0 to 8.4 ,

https://dev.mysql.com/doc/relnotes/mysql/8.4/en/news-8-4-6.html

Best Practices for Updating MySQL from 8.0 to 8.4 | SQLFlash

Lets move on the steps ,

Environment :

Ubuntu 22
MySQL 8.0.43

Step 1 :

Please take complete backup of your existing database server

mysqldump -u root -p --routines --triggers --events --max_allowed_packet=1024M am80db > am80db_before_8.4_Upgrade.sql

Also take mysql database as well

Step 2 :

Download and add the MySQL APT repo package

wget https://dev.mysql.com/get/mysql-apt-config_0.8.33-1_all.deb
sudo dpkg -i mysql-apt-config_0.8.33-1_all.deb

root@ip-11-1-23-23:~# wget https://dev.mysql.com/get/mysql-apt-config_0.8.33-1_all.deb

sudo dpkg -i mysql-apt-config_0.8.33-1_all.deb

--2025-10-17 13:45:36-- https://dev.mysql.com/get/mysql-apt-config_0.8.33-1_all.deb

Resolving dev.mysql.com (dev.mysql.com)... 104.120.82.77, 2600:140f:1e00:486::2e31, 2600:140f:1e00:4b2::2e31

Connecting to dev.mysql.com (dev.mysql.com)|104.120.82.77|:443... connected.

HTTP request sent, awaiting response... 302 Moved Temporarily

Location: https://repo.mysql.com//mysql-apt-config_0.8.33-1_all.deb [following]

--2025-10-17 13:45:37-- https://repo.mysql.com//mysql-apt-config_0.8.33-1_all.deb

Resolving repo.mysql.com (repo.mysql.com)... 23.10.47.157, 2600:140f:1e00:a86::1d68, 2600:140f:1e00:a8f::1d68

Connecting to repo.mysql.com (repo.mysql.com)|23.10.47.157|:443... connected.

HTTP request sent, awaiting response... 200 OK

Length: 18072 (18K) [application/x-debian-package]

Saving to: ‘mysql-apt-config_0.8.33-1_all.deb.1’

mysql-apt-config_0.8.33-1_all.deb.1 100%[============================================================================>] 17.65K --.-KB/s in 0.001s

2025-10-17 13:45:37 (25.2 MB/s) - ‘mysql-apt-config_0.8.33-1_all.deb.1’ saved [18072/18072]

(Reading database ... 99983 files and directories currently installed.)

Preparing to unpack mysql-apt-config_0.8.33-1_all.deb ...

Unpacking mysql-apt-config (0.8.33-1) over (0.8.33-1) ...

Setting up mysql-apt-config (0.8.33-1) ...

root@ip-11-1-23-23:~# sudo dpkg-reconfigure mysql-apt-config

File '/usr/share/keyrings/mysql-apt-config.gpg' exists. Overwrite? (y/N) y

root@ip-11-1-23-23:~# sudo apt update

Hit:1 http://ap-south-1.ec2.archive.ubuntu.com/ubuntu jammy InRelease

Hit:2 http://ap-south-1.ec2.archive.ubuntu.com/ubuntu jammy-updates InRelease

Hit:3 http://ap-south-1.ec2.archive.ubuntu.com/ubuntu jammy-backports InRelease

Hit:4 http://repo.mysql.com/apt/ubuntu jammy InRelease

Hit:5 http://security.ubuntu.com/ubuntu jammy-security InRelease

Get:6 http://repo.mysql.com/apt/ubuntu jammy/mysql-8.4-lts Sources [965 B]

Get:7 http://repo.mysql.com/apt/ubuntu jammy/mysql-8.4-lts amd64 Packages [14.5 kB]

Fetched 15.5 kB in 4s (3798 B/s)

Reading package lists... Done

Building dependency tree... Done

Reading state information... Done

10 packages can be upgraded. Run 'apt list --upgradable' to see them.

While configure it will ask to choose mysql-8.4 LTS and Click OK . Make sure you have selected 8.4 not 8.0

then update the package list in the ubuntu

apt update

root@ip-11-1-23-23:~# sudo apt update

Hit:1 http://repo.mysql.com/apt/ubuntu jammy InRelease

Hit:2 http://ap-south-1.ec2.archive.ubuntu.com/ubuntu jammy InRelease

Hit:3 http://ap-south-1.ec2.archive.ubuntu.com/ubuntu jammy-updates InRelease

Hit:4 http://ap-south-1.ec2.archive.ubuntu.com/ubuntu jammy-backports InRelease

Hit:5 http://security.ubuntu.com/ubuntu jammy-security InRelease

Reading package lists... Done

Building dependency tree... Done

Reading state information... Done

10 packages can be upgraded. Run 'apt list --upgradable' to see them.

Step 3 :

Lets start installing the package , make sure selecting conf options properly

sudo apt install mysql-server -y

root@ip-11-1-23-23:~# sudo apt install mysql-server -y

Reading package lists... Done

Building dependency tree... Done

Reading state information... Done

The following packages were automatically installed and are no longer required:

libcgi-fast-perl libcgi-pm-perl libclone-perl libencode-locale-perl libevent-pthreads-2.1-7 libfcgi-bin libfcgi-perl libfcgi0ldbl libhtml-parser-perl

libhtml-tagset-perl libhtml-template-perl libhttp-date-perl libhttp-message-perl libio-html-perl liblwp-mediatypes-perl libprotobuf-lite23

libtimedate-perl liburi-perl

Use 'sudo apt autoremove' to remove them.

The following additional packages will be installed:

mysql-client mysql-common mysql-community-client mysql-community-client-core mysql-community-client-plugins mysql-community-server

mysql-community-server-core

The following packages will be REMOVED:

mysql-client-8.0 mysql-client-core-8.0 mysql-server-8.0 mysql-server-core-8.0

The following NEW packages will be installed:

mysql-client mysql-community-client mysql-community-client-core mysql-community-client-plugins mysql-community-server mysql-community-server-core

The following packages will be upgraded:

mysql-common mysql-server

2 upgraded, 6 newly installed, 4 to remove and 8 not upgraded.

Need to get 36.5 MB of archives.

After this operation, 47.3 MB of additional disk space will be used.

Get:1 http://repo.mysql.com/apt/ubuntu jammy/mysql-8.4-lts amd64 mysql-server amd64 8.4.6-1ubuntu22.04 [59.2 kB]

Get:2 http://repo.mysql.com/apt/ubuntu jammy/mysql-8.4-lts amd64 mysql-common amd64 8.4.6-1ubuntu22.04 [60.6 kB]

Get:3 http://repo.mysql.com/apt/ubuntu jammy/mysql-8.4-lts amd64 mysql-community-client-plugins amd64 8.4.6-1ubuntu22.04 [1524 kB]

Get:4 http://repo.mysql.com/apt/ubuntu jammy/mysql-8.4-lts amd64 mysql-community-client-core amd64 8.4.6-1ubuntu22.04 [1847 kB]

Get:5 http://repo.mysql.com/apt/ubuntu jammy/mysql-8.4-lts amd64 mysql-community-client amd64 8.4.6-1ubuntu22.04 [2174 kB]

Get:6 http://repo.mysql.com/apt/ubuntu jammy/mysql-8.4-lts amd64 mysql-client amd64 8.4.6-1ubuntu22.04 [59.3 kB]

Get:7 http://repo.mysql.com/apt/ubuntu jammy/mysql-8.4-lts amd64 mysql-community-server-core amd64 8.4.6-1ubuntu22.04 [30.7 MB]

Get:8 http://repo.mysql.com/apt/ubuntu jammy/mysql-8.4-lts amd64 mysql-community-server amd64 8.4.6-1ubuntu22.04 [69.3 kB]

Fetched 36.5 MB in 5s (7141 kB/s)

Preconfiguring packages ...

(Reading database ... 99983 files and directories currently installed.)

Preparing to unpack .../mysql-server_8.4.6-1ubuntu22.04_amd64.deb ...

Unpacking mysql-server (8.4.6-1ubuntu22.04) over (8.0.43-0ubuntu0.22.04.2) ...

(Reading database ... 99986 files and directories currently installed.)

Removing mysql-server-8.0 (8.0.43-0ubuntu0.22.04.2) ...

update-alternatives: using /etc/mysql/my.cnf.fallback to provide /etc/mysql/my.cnf (my.cnf) in auto mode

Removing mysql-client-8.0 (8.0.43-0ubuntu0.22.04.2) ...

Removing mysql-client-core-8.0 (8.0.43-0ubuntu0.22.04.2) ...

Removing mysql-server-core-8.0 (8.0.43-0ubuntu0.22.04.2) ...

(Reading database ... 99784 files and directories currently installed.)

Preparing to unpack .../0-mysql-common_8.4.6-1ubuntu22.04_amd64.deb ...

Unpacking mysql-common (8.4.6-1ubuntu22.04) over (5.8+1.0.8) ...

Selecting previously unselected package mysql-community-client-plugins.

Preparing to unpack .../1-mysql-community-client-plugins_8.4.6-1ubuntu22.04_amd64.deb ...

Unpacking mysql-community-client-plugins (8.4.6-1ubuntu22.04) ...

Selecting previously unselected package mysql-community-client-core.

Preparing to unpack .../2-mysql-community-client-core_8.4.6-1ubuntu22.04_amd64.deb ...

Unpacking mysql-community-client-core (8.4.6-1ubuntu22.04) ...

Selecting previously unselected package mysql-community-client.

Preparing to unpack .../3-mysql-community-client_8.4.6-1ubuntu22.04_amd64.deb ...

Unpacking mysql-community-client (8.4.6-1ubuntu22.04) ...

Selecting previously unselected package mysql-client.

Preparing to unpack .../4-mysql-client_8.4.6-1ubuntu22.04_amd64.deb ...

Unpacking mysql-client (8.4.6-1ubuntu22.04) ...

Selecting previously unselected package mysql-community-server-core.

Preparing to unpack .../5-mysql-community-server-core_8.4.6-1ubuntu22.04_amd64.deb ...

Unpacking mysql-community-server-core (8.4.6-1ubuntu22.04) ...

Selecting previously unselected package mysql-community-server.

Preparing to unpack .../6-mysql-community-server_8.4.6-1ubuntu22.04_amd64.deb ...

Unpacking mysql-community-server (8.4.6-1ubuntu22.04) ...

Setting up mysql-common (8.4.6-1ubuntu22.04) ...

Installing new version of config file /etc/mysql/conf.d/mysql.cnf ...

Installing new version of config file /etc/mysql/my.cnf.fallback ...

Setting up mysql-community-server-core (8.4.6-1ubuntu22.04) ...

Setting up mysql-community-client-plugins (8.4.6-1ubuntu22.04) ...

Setting up mysql-community-client-core (8.4.6-1ubuntu22.04) ...

Setting up mysql-community-client (8.4.6-1ubuntu22.04) ...

Setting up mysql-client (8.4.6-1ubuntu22.04) ...

Setting up mysql-community-server (8.4.6-1ubuntu22.04) ...

Installing new version of config file /etc/apparmor.d/usr.sbin.mysqld ...

Installing new version of config file /etc/mysql/mysql.cnf ...

Configuration file '/etc/mysql/mysql.conf.d/mysqld.cnf'

==> Modified (by you or by a script) since installation.

==> Package distributor has shipped an updated version.

What would you like to do about it ? Your options are:

Y or I : install the package maintainer's version

N or O : keep your currently-installed version

D : show the differences between the versions

Z : start a shell to examine the situation

The default action is to keep your current version.

*** mysqld.cnf (Y/I/N/O/D/Z) [default=N] ? Y

Installing new version of config file /etc/mysql/mysql.conf.d/mysqld.cnf ...

update-alternatives: using /etc/mysql/mysql.cnf to provide /etc/mysql/my.cnf (my.cnf) in auto mode

Setting up mysql-server (8.4.6-1ubuntu22.04) ...

Processing triggers for man-db (2.10.2-1) ...

Processing triggers for libc-bin (2.35-0ubuntu3.11) ...

Scanning processes...

Scanning candidates...

Scanning linux images...

Running kernel seems to be up-to-date.

Restarting services...

Service restarts being deferred:

systemctl restart networkd-dispatcher.service

systemctl restart unattended-upgrades.service

systemctl restart user@0.service

No containers need to be restarted.

No user sessions are running outdated binaries.

No VM guests are running outdated hypervisor (qemu) binaries on this host.

Step 4 :

Lets do the final restart and check the status of MySQL

sudo systemctl restart mysql
sudo systemctl status mysql

root@ip-11-1-23-23:~# sudo systemctl restart mysql

root@ip-11-1-23-23:~# sudo systemctl status mysql

● mysql.service - MySQL Community Server

Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)

Active: active (running) since Fri 2025-10-17 13:48:46 UTC; 7s ago

Docs: man:mysqld(8)

http://dev.mysql.com/doc/refman/en/using-systemd.html

Process: 95024 ExecStartPre=/usr/share/mysql-8.4/mysql-systemd-start pre (code=exited, status=0/SUCCESS)

Main PID: 95063 (mysqld)

Status: "Server is operational"

Tasks: 36 (limit: 9387)

Memory: 444.4M

CPU: 1.905s

CGroup: /system.slice/mysql.service

└─95063 /usr/sbin/mysqld

Oct 17 13:48:42 ip-10-1-94-82 systemd[1]: Starting MySQL Community Server...

Oct 17 13:48:46 ip-10-1-94-82 systemd[1]: Started MySQL Community Server.

Step 5 :

Dont try mysql_upgrade is no longer exist on MySQL 8.0.16 then lets complete mysql_secure_installation and update proper password for the root user

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'ewewewewewe';
Query OK, 0 rows affected (0.01 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

Step 6 :

Lets verify the MySQL Upgrade has been success or not on the log file , Highlighted logs clarifing upgrade successfully completed

sudo tail -n 50 /var/log/mysql/error.log

2025-10-17T13:47:41.670888Z 0 [System] [MY-015015] [Server] MySQL Server - start.

2025-10-17T13:47:42.054060Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.4.6) starting as process 94072

2025-10-17T13:47:42.129906Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.

2025-10-17T13:47:49.394972Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.

2025-10-17T13:47:49.429761Z 1 [System] [MY-011090] [Server] Data dictionary upgrading from version '80023' to '80300'.

2025-10-17T13:47:53.024004Z 1 [System] [MY-013413] [Server] Data dictionary upgrade from version '80023' to '80300' completed.

2025-10-17T13:47:57.289846Z 4 [System] [MY-013381] [Server] Server upgrade from '80043' to '80406' started.

2025-10-17T13:48:07.600863Z 4 [System] [MY-013381] [Server] Server upgrade from '80043' to '80406' completed.

2025-10-17T13:48:08.000645Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.

2025-10-17T13:48:08.000744Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.

2025-10-17T13:48:08.081364Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock

2025-10-17T13:48:08.082336Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.4.6' socket: '/var/run/mysqld/mysqld.sock' port: 3306 MySQL Community Server - GPL.

2025-10-17T13:48:39.677032Z 0 [System] [MY-013172] [Server] Received SHUTDOWN from user <via user signal>. Shutting down mysqld (Version: 8.4.6).

2025-10-17T13:48:41.686113Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 10 user: 'Ruban'.

2025-10-17T13:48:41.689689Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 11 user: 'Ruban'.

2025-10-17T13:48:42.651459Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.4.6) MySQL Community Server - GPL.

2025-10-17T13:48:42.651514Z 0 [System] [MY-015016] [Server] MySQL Server - end.

2025-10-17T13:48:43.120978Z 0 [System] [MY-015015] [Server] MySQL Server - start.

2025-10-17T13:48:43.492756Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.4.6) starting as process 95063

2025-10-17T13:48:43.529553Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.

2025-10-17T13:48:45.123068Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.

2025-10-17T13:48:46.095152Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.

2025-10-17T13:48:46.095299Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.

2025-10-17T13:48:46.181691Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock

2025-10-17T13:48:46.182507Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.4.6' socket: '/var/run/mysqld/mysqld.sock' port: 3306 MySQL Community Server - GPL.

Step 7 :

Final Validation from my end ,

Reference :

https://dev.mysql.com/doc/relnotes/mysql/8.4/en/news-8-4-6.html
https://dev.mysql.com/doc/relnotes/mysql/8.4/en/news-8-4-0.html
Best Practices for Updating MySQL from 8.0 to 8.4 | SQLFlash

Lets chat if you need further assistance on this , Please comment

Monday, June 23, 2025

MySQL ERROR 1146 (42S02): Table 'datablogs.tbl_followup' doesn't exist

Selva | June 23, 2025 | No comments | Amazon RDS, Database Administration, MySQL, Point In Time Restore, Troubleshooting

Had a some conversation with friend he is facing this issue for long back and unable to solve it . But i had an thought why we cant replicate in our test environment and fix this quickly . After a long time had a nice troubleshooting hours

Lets deep dive into the troubleshooting , before enter into the troubleshooting requested few sample output for the same

Outputs for the issues :

First things requested to login without grant tables and asked to repair the tables

root@ip-100-23-45-122:sudo mysqld_safe --skip-grant-tables --skip-networking --skip-plugin-load &

When trying to repair it mysql database most of the tables got corrupted , So corruption happened on the master tables itself ,

root@ip-100-23-45-122:/var/lib/mysql/mysql# mysqlcheck -u root -p --repair --databases mysql
Enter password:
mysql.columns_priv OK
mysql.db OK
mysql.engine_cost
Error : Table 'mysql.engine_cost' doesn't exist
status : Operation failed
mysql.event OK
mysql.func OK
mysql.general_log OK
mysql.gtid_executed
Error : Table 'mysql.gtid_executed' doesn't exist
status : Operation failed
mysql.help_category
Error : Table 'mysql.help_category' doesn't exist
status : Operation failed
mysql.help_keyword
Error : Table 'mysql.help_keyword' doesn't exist
status : Operation failed
mysql.help_relation
Error : Table 'mysql.help_relation' doesn't exist
status : Operation failed
mysql.help_topic
Error : Table 'mysql.help_topic' doesn't exist
status : Operation failed
mysql.innodb_index_stats
Error : Table 'mysql.innodb_index_stats' doesn't exist
status : Operation failed
mysql.innodb_table_stats
Error : Table 'mysql.innodb_table_stats' doesn't exist
status : Operation failed
mysql.ndb_binlog_index OK
mysql.plugin

So further we need to try something to resolve it . I have replicated same issues with my local environment and followed below steps in the local

Troubleshooting Step 1 :

Installed separate mysql instance with different port and copied all user database directories to new server then tried to start mysql its started also

But when trying to access the same user table got an same error

MySQL ERROR 1146 (42S02): Table 'datablogs.tbl_followup' doesn't exist

So issue not with master table corruption , something different

Troubleshooting Step 2 :

I have gone through so many links and references tried below things as well ,

Stop the MySQL then Remove ib_logfile0 , ib_logfile1 . Again start the MySQL Server - Its not worked
Move corrupted ibdata1 file in the newly installed MySQL server and then restart MySQL Server - Its not worked
More links requested to restart multiple times its very funny thing but tried it will or not - Its also not worked

Finally complete MySQL troubleshooting reference resolved this issue with replacing tablespace method

Troubleshooting Step 3 :

As per reference link mentioned below Restoring Orphan File-Per-Table ibd Files then its resolved and able to retrive the records from the table

https://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html

Again installed one more MySQL Server and Created dummy database as datablogs and created only structure from the sample

Note : Only if you have structure of the table its possible to recover

mysql> create database datablogs;
Query OK, 1 row affected (0.01 sec)

mysql> use datablogs;
Database changed

mysql> CREATE TABLE tbl_followup (
id int(11) NOT NULL AUTO_INCREMENT,
table_header text,
action varchar(100) DEFAULT NULL,
action_button_text varchar(100) DEFAULT NULL,
parent_template text,
created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
modified_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.03 sec)

mysql> ALTER TABLE datablogs.tbl_followup DISCARD TABLESPACE;
Query OK, 0 rows affected (0.01 sec)

Copied only tbl_followup.ibd file from the corrupted server to new server then imported the tablespace

mysql> ALTER TABLE datablogs.tbl_followup IMPORT TABLESPACE;
Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql> select * from datablogs.tbl_followup;
Empty set (0.01 sec)

So overall while reading it very simple but its very hard troubleshooting did in lifetime !!!

Anyway instead of doing multiple solution follow my steps to resolve the issue sortly !!!

Tuesday, November 14, 2023

PostgreSQL Table Partition on AWS RDS

Selva | November 14, 2023 | 2 comments | Amazon RDS, AWS, Database Administration, Development, Performance Tunning, Postgresql

If we grow bigger in the business , seamlessly our customer and transaction data also increases . In the meantime performance needs to consider as well

So in this case of bigger tables indexes will not help us to achieve good performance on peak times . Alternatively we have partitioning to split the tables data into multiple pieces on all the relational database environments

Like wise we are going to do range partition on sample table in PostgreSQL Database , In PostgreSQL three types of partition methods are available ,

Below are the important concern in the PostgreSQL Partition ,

Possible to attach regular table into partitioned one
Not Possible to transform regular table to partitioned one

So based on the above formula , we have tried to transform regular table into partitioned one for your reference

Any one can use this example and perform partitioning in AWS PostgreSQL RDS easily

Click GitHub Link for Code : AWS-PostgreSQL-RDS-Table-Partition

Step 1 : Create base datablogspaycheck table and insert some sample records

DROP TABLE IF EXISTS datablogspaycheck CASCADE;
DROP SEQUENCE IF EXISTS public.paycheck_id_seq;
CREATE SEQUENCE public.paycheck_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
create table datablogspaycheck
(
payment_id int NOT NULL DEFAULT nextval('public.paycheck_id_seq'::regclass),
created timestamptz NOT NULL,
updated timestamptz NOT NULL DEFAULT now(),
amount float,
status varchar DEFAULT 'new'
);
CREATE INDEX idx_paycheck ON datablogspaycheck (created);
INSERT INTO datablogspaycheck (created) VALUES (
generate_series(timestamp '2023-01-01'
, now()
, interval '5 minutes') );

Step 2 : Rename base table with new name

ALTER TABLE datablogspaycheck RENAME TO datablogspaycheck_basetable;

Step 3 : Create Partitioned table

create table datablogspaycheck
(
payment_id int NOT NULL DEFAULT nextval('public.paycheck_id_seq'::regclass),
created timestamptz NOT NULL,
updated timestamptz NOT NULL DEFAULT now(),
amount float,
status varchar DEFAULT 'new'
)PARTITION BY RANGE (created);

Step 4 : Create Separate Partition for each create date

CREATE TABLE datablogspaycheck_202303 PARTITION OF datablogspaycheck
FOR VALUES FROM ('2023-01-01') TO ('2023-03-01');

CREATE TABLE datablogspaycheck_20230304 PARTITION OF datablogspaycheck
FOR VALUES FROM ('2023-03-01') TO ('2023-04-01');

CREATE TABLE datablogspaycheck_202304 PARTITION OF datablogspaycheck
FOR VALUES FROM ('2023-04-01') TO ('2023-05-01');

CREATE TABLE datablogspaycheck_202311 PARTITION OF datablogspaycheck
FOR VALUES FROM ('2023-05-01') TO ('2023-11-01');

CREATE TABLE datablogspaycheck_2024 PARTITION OF datablogspaycheck
FOR VALUES FROM ('2023-11-01') TO ('2024-01-01');

Step 5 : Migrate the all records

insert into datablogspaycheck (payment_id,created,updated,amount,status) select payment_id,created,updated,amount,status from datablogspaycheck_basetable;

Step 6 : Validate each partition

select * from datablogspaycheck_202303 order by 2 desc
select * from datablogspaycheck_20230304 order by 2 desc
select * from datablogspaycheck_202311 order by 2 desc

Its done , Easily migrated normal table data into partitioned table

Thanks for Reading !!!

Wednesday, June 21, 2023

Oracle RDS Audit log enable

Selva | June 21, 2023 | No comments | Amazon RDS, Audit, AWS, Database Administration, Oracle

Oracle Audit Log :

Oracle Audit Log refers to the feature in Oracle Database that records and stores information about various database activities and events. It provides a mechanism to track and monitor user activities, system events, and changes made to the database.

User Logins: Recording user login attempts and authentication information.
Database Activities: Logging SQL statements executed by users, including select, insert, update, and delete operations.
Privilege Usage: Monitoring the usage of privileges, such as granting or revoking permissions.
Schema Changes: Tracking modifications to database objects, such as creating or altering tables, views, or indexes.
System Events: Recording system-level events, such as startup and shutdown of the database.
Security Violations: Detecting unauthorized access attempts or suspicious activities.
Administrative Operations: Logging administrative tasks performed by database administrators, such as user management or database configuration changes.

The Oracle Audit Log provides an essential tool for security, compliance, and troubleshooting purposes.

Types of Auditing in Amazon RDS for Oracle :

Standard Auditing
Unified Auditing
Fine-grained Auditing

We are going to see , how do we enable Standard auditing in Oracle RDS

How to enable Audit Log in Oracle RDS?

Make sure you have enabled custom parameter group for Oracle RDS

Modify below values for Audit_Trail Parameter

Audit_Trail - DB, EXTENDED

Next ,Just needs to modify below DDL or DML statements to capture the logs from the server

AUDIT DELETE ANY TABLE;

AUDIT DELETE TABLE BY USER_01 BY ACCESS;

AUDIT DELETE TABLE BY USER_02 BY ACCESS;

AUDIT ALTER, GRANT, INSERT, UPDATE, DELETE ON DEFAULT;

AUDIT READ ON DIRECTORY datapump_dir;

Its all done , we have enabled required logs to capture for security purpose

How to we monitor Audit Logs ?

We can just run the below command get the captured audit logs in Oracle RDS ,

SELECT * FROM DBA_AUDIT_TRAIL order by 1 desc

Its just for normal scenario , explained the process . Still we can separate Audit Table space and many further things are available in Oracle . Let see on another blogs

Happy Auditing !!!

Friday, February 24, 2023

How to Automate MongoDB Database Backups in Linux

Selva | February 24, 2023 | No comments | Automation, Backup, Database Administration, MongoDB, NoSQL

We have setup of One Primary with Multiple Secondary

Even if we configured highly available setup and backups , native backup are so special to take it and keep it somewhere in the cloud

using the below script we can easily schedule backup in Linux environments

https://github.com/selvackp/MongoNativeBackup-/blob/main/mongo_dump.sh

export PATH=/bin:/usr/bin:/usr/local/bin
#Decalre Today Date
TODAY=`date +"%d%b%Y"`

#Declare Variables Required to pass for mongo dump command
DB_BACKUP_PATH='/mnt/mongobackup'
MONGO_HOST='localhost'
MONGO_PORT='27017'
MONGO_USER='xxxxxxxxxxx'
MONGO_PASSWD='xxxxxxxxxxxxx'
DATABASE_NAMES='ALL'

#Remove Old Backup Files 
find ${DB_BACKUP_PATH} -name "*.zip" -type f -mtime +3 -delete

find ${DB_BACKUP_PATH} -type d -mtime +3 -exec rm -rf {} \;

#Create Directory for Backup
mkdir -p ${DB_BACKUP_PATH}/${TODAY}
cd ${DB_BACKUP_PATH}/${TODAY}/

if [ ${DATABASE_NAMES} = "ALL" ]; then
	echo "You have choose to backup all database"
	mongodump --uri="mongodb://${MONGO_USER}:${MONGO_PASSWD}@${MONGO_HOST}:${MONGO_PORT}"
else
	echo "Running backup for selected databases"
	for DB_NAME in ${DATABASE_NAMES}
	do
	mongodump --uri="mongodb://${MONGO_USER}:${MONGO_PASSWD}@${MONGO_HOST}:${MONGO_PORT}/${DB_NAME}"
	done
fi

#Compress The Backup
cd ${DB_BACKUP_PATH}/${TODAY}

zip -r ${DB_BACKUP_PATH}_${TODAY}.zip ${DB_BACKUP_PATH}/${TODAY}

cd ${DB_BACKUP_PATH}/${TODAY}

#Copy the Compressed file into Azure Container using Shared Access Token
azcopy cp ${DB_BACKUP_PATH}_${TODAY}.zip "https://xxxxxxxxxxx.blob.core.windows.net/xxxxxxxxxxxx?sp=w&st=xxxxxTxxxxxxxZ&se=xxxxxxZ&spr=https&sv=2021-06-08&sr=c&sig=csdfcdsxxxxxxxxxxxxxxx" --recursive=true

#Send Mail with Backup Logs 
if [ $? -ne 0 ]
then
        echo "Mongo Native backup Failed in $(hostname) $(date). Please contact administrator." | mail -r mail@datablogs.com -s "Mongo Native backup Failed $(hostname)" dbsupport@datablogs.com < /mongodata/cronscripts/mongo_backup_log.log

        else

        echo "Mongo Native backup completed in $(hostname)." | mail -r mail@datablogs.com -s "Mongo Native backup completed in $(hostname)" dbsupport@datablogs.com < /mongodata/cronscripts/mongo_backup_log.log
fi

Friday, February 17, 2023

Restore and Point in time Restore with Percona Backup of MongoDB on another instance

Selva | February 17, 2023 | No comments | Database Administration, medium, MongoDB, NoSQL, percona, Point In Time Restore

Its easy to recover MongoDB Backup using Percona Backup for MongoDB

https://medium.com/@datablogs/restore-and-point-in-time-restore-with-percona-backup-of-mongodb-on-another-instance-4766f4468443

How to tune long running Queries on Azure Synapse DW (Dedicated Pool) - Part 2

Selva | February 17, 2023 | No comments | Azure Cloud, Azure Synapse Analytics, Database Administration, Performance Tunning, SQL Server

Its took little long time to derive our approach tunning on azure data factory with Azure Synapse why because we need to run the system atleast two months validate our approach is smooth

Yes its all running good as expected performance on the ETL loads and Processes

Here are the major things we need to take care on Azure Synapse Dedicated Pool ,

Dedicated SQL Pool Scaling
Dedicated SQL Pool Workload Management

Dedicated SQL Pool Scaling :

We have decide below metrices to optimize the Azure Synapse

Data Load and Processing timelines
Critical Process running timelines
Minimal Processing timelines
Reporting and Visualtions Timelines

Based on above requirements , we can easily scalable the Dedicated SQL Pool based on the load

Consider we need to load the data and process with in 2 hours , we can upscale the Dedicated SQL Pool to as like we want

Key Part to increase the DW is concurrency , Instance Capacity and allocated resource for each queries

Before start our critical process , we can automate upscale process with ADF Pipelines itself . So many blogs available to configure that

Best method configure the authentication method with service principle ,

Dedicated SQL Pool Workload Management :

We have decide below metrices to prepare workload management

Short Queries
Critical Queries
High Intensive Queries
Reporting Queries

Based on the classifications , we have to split workload group for above queries

Step 1 :

We need to create login and user for workload management in Dedicated SQL Pool

--CREATE LOGIN [Analyticsprocess] WITH PASSWORD='xxxxxxxxx'
--CREATE USER [Analyticsprocess] FOR LOGIN [Analyticsprocess]
--GRANT CONTROL ON DATABASE::[sql-datablogs-dw] TO
Analyticsprocess

Step 2 :

Consider you have upscaled instance into DW400c below are the resources allocation for the DW400c instance concurrency requirements

In the workload group --> New workload group --> Click ELT

Consider analytics process user is used for high intensive queries we have to allocate as much as minimum resource for workload group

Click Classifiers --> Add Classifiers --> Name it as ELT --> and specify Member should be the login and Label is important to mention

Once click Add , we will get below concurrency range based on DW400c

By Default , its having system level workload group to handle the queries but its not effective we have to force our workload group

Step 3 :

This is very important to utilize the workload group properly . We need to specify Label on the heavy processing queries so that it will utilized properly

CREATE TABLE rpt_datablogs_finalreport.smgreport WITH (HEAP,DISTRIBUTION = REPLICATE) AS select * into rpt_datablogs_finalreport.smgreport_vw from rpt_datablogs_finalreport.vw_smgreport OPTION (LABEL='highintensiveprocess')

So these two things given much improvement on performance as well as reduced operational cost below 60%

Still few more things to learn to optimize it on Azure Data Factory lets see on that next blog

Happy Troubleshooting !!!

Monday, January 30, 2023

Update Backup and Restore in Same SQL Server RDS

Selva | January 30, 2023 | No comments | Amazon RDS, Database Administration, SQL Server

Update of Previous post ,

As mentioned in below blog , past years we cant restore database into same SQL Server RDS

https://medium.com/@yogitha.o/backup-and-restore-in-same-sql-server-rds-3e667622f7ff

But now you can able to restore without any issues from recent releases of SQL Server RDS

Thursday, December 15, 2022

ORA-01940 cannot drop a user that is currently connected

Selva | December 15, 2022 | No comments | Amazon RDS, AWS, Database Administration, Oracle 19C, Restore

Its Easy to achieve it in easy method ,

https://datablogs.medium.com/ora-01940-cannot-drop-a-user-that-is-currently-connected-and-kill-inactive-sessions-in-oracle-c009fbbbc726

Sunday, August 28, 2022

How to tune long running Queries on Azure Synapse DW (Dedicated Pool) - Part 1

Selva | August 28, 2022 | No comments | Azure Cloud, Azure Synapse Analytics, Database Administration, Performance Tunning, SQL Server

Three hours of long running pipeline is reduced to run in 35 Minutes when we given right kind of top bottom optimization like our Body !!!

Yes Sometimes if we give proper core and cross workouts you can run 10km in 55 Minutes !!!

Its happened to me But Not Now 😆😆😆

Oh Oh we are away from Data ground !!! Lets Move into Azure Synapse tunning,

In the starting phase every piece of code and pipeline process was really trouble us to optimize it , whatever we do on tunning it will run as same time

Waited long time until each process to complete as its running 3 hours !!!

But Reecha blog helped something to start with basic check and given some hope to fine tune it

Remember below piece of monitoring code always help us to too dig more

--Check Long Running Query in Azure Synapse

SELECT *
FROM sys.dm_pdw_exec_requests
WHERE status not in ('Completed','Failed','Cancelled')
  AND session_id <> session_id()
ORDER BY submit_time DESC;

--Find the distributed query plan steps for long running query

SELECT * FROM sys.dm_pdw_request_steps WHERE request_id = 'xxxxxx'  ORDER BY step_index; 

***Replace your request_id from first query result

Sample Running Query :

Initially we thought data volume is huge , so we are expecting this much delay on complete . But distributed query Plans are given more finishing time for each queries in Azure Synapse

We will go step by step process of tunning ,

Multiple ways we can see the Execution plan of your Azure Synapse Queries

Using Azure Console --> Click you Dedicated Pool --> Click Query Activity --> Each Queries you have Query Plans

2.Using SSMS , you can run below query and get your explain plan in XML Format

EXPLAIN select * from dbo_datablogs.vw_fact_transferdata (nolock)

So ,We have figured out the execution plan and further need to fix below things to make it faster

We need to reduce data movement operations ( Shuffle Move , Trim Move , Partition Move ) and needs to have proper indexing on your business tables

Based on your data distribution on tables and indexing , queries will be executed on SQL Pool . We need to distribute the tables accordingly . So Major part of the tunning on table level only

Once we done that in proper way we have achieved your milestone ,

How to we distribute table in Azure Synapse ?

Two types of distribution is available in Azure Synapse . Round Robin and Hash Distribution

Round Robin Distribution

By Default , If you create table it will create in Round Robin Distribution
Table rows are distributed in all distribution
Main purpose of this table to improve loading speed we can use round robin distribution
If your table is just junk or doesn't have proper any constraints and keys use round robin distribution

CREATE TABLE [dbo_datablogs].[ControlTable_blogs](

[Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED NOT ENFORCED,

[SourceObjectSettings] [nvarchar](max) NULL)

WITH ( CLUSTERED COLUMNSTORE INDEX, DISTRIBUTION = ROUND_ROBIN )

Hash Distribution

If your table is having frequent DML operations ( Insert , Update , Delete ) use hash distribution
It will increase performance 200% of your queries
It will distribute the data to nodes based on your distributed column

CREATE TABLE [dbo_datablogs].[ControlTable_blogs](

[Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED NOT ENFORCED,

[SourceObjectSettings] [nvarchar](max) NULL)

WITH ( HEAP, DISTRIBUTION = HASH(Id))

Replicate

This is another way to storing the table in SQL Pool

Full copy of the table will be available in all distribution to avoid data movement
Table is having less size use replicate option

CREATE TABLE [dbo_datablogs].[ControlTable_blogs](

[Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED NOT ENFORCED,

[SourceObjectSettings] [nvarchar](max) NULL)

WITH ( HEAP, DISTRIBUTION = REPLICATE )

Thumb of Rules of Creating tables

First Level : Find the absolute unique column or else use partial unique column for creating table as Hash Distribution

Second Level : If your table doesn't have proper unique columns , try to create the table with used columns in Join , Group By , Distinct , Over and Having Clauses on Procedures or Select Clauses

Lets examine First Level ,

When creating the table with Round Robin and created table with below script ,

CREATE TABLE dbt_datablogs.fact_transferdata

WITH

(

CLUSTERED COLUMNSTORE INDEX,

DISTRIBUTION = ROUND_ROBIN

)

AS SELECT * FROM dbt_datablogs.vw_fact_transferdata

Its taken 28 Minutes to complete

So we figured out good candidate key and created table with below script

CREATE TABLE dbt_datablogs.fact_transferdata

WITH

(

CLUSTERED COLUMNSTORE INDEX,

DISTRIBUTION = HASH(tranferaddressid)

)

AS SELECT * FROM dbt_datablogs.vw_fact_transferdata

Its taken 4 Minutes to complete

We will see the huge difference when creating proper keys

Lets examine Second Level ,

When we don't have proper keys , based on your business logics or select clause join conditions we can try to evaluate with multiple columns and create proper hash function to improve performance

Lets do the smaller table tunning on this blog , you can try the same in your own environments ,

Below table is small but its having shuffle move and its delaying the process 11 Minutes ,

Then based on shuffle columns , add the proper hash function and recreate the table

CREATE TABLE dbt_datablogs.fact_memberinfo

WITH

(

Heap,

DISTRIBUTION = HASH(infoid)

)

AS SELECT * FROM dbt_datablogs.vw_memberinfo

Once created the table again examined the Execution plan for the same statement and process is finished with in a seconds

This is not an end , Also we have tunned few things on Data Factory and Azure Synapse Workload Management to get expected performance , we will see further on next blog

To learn more in-depth , Please use below references from Microsoft Site as well

References :

1.Cheat Sheet - https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/cheat-sheet?view=azure-sqldw-latest

2.Distributed Tables - https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-distribute

3.Replicated Tables - https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/design-guidance-for-replicated-tables

Thursday, August 11, 2022

SQL Server Point in Time Recovery

Selva | August 11, 2022 | No comments | Automation, Database Administration, Point In Time Restore, SQL Server

Oooooouch !!! I have deleted critical data in the ledger table !!!

When we ran delete or drop Script on any database without double check , Its Really big problem for the end users !!! Also you wont sleep 😁😁😁

If you have GODDBA , you are saved millions dollars of your data also your valuable customers .

Oh Great 😀😀😀 How we can achieve that ?

In SQL Server Multiple Database Backup Methods are available to tackle that ,

If SQL Server database is in full recovery model we can take transaction log backups on every hour/Minute/Seconds into local or remote drives .

So, Yes am taking Log Backup 😕😕😕 How to we restore particular time ?

Using with transaction logs we can bring back the data as much as you want depends on your log backup strategy .

To achieve that here is the automated point in time restore script for your valuable environment ,

Scripts are available in https://github.com/selvackp/SQLServerPointInTimeRestore.git

So finally you have saved your millions dollars worth of data in 10 Minutes !!!

USE master
GO

DECLARE 
        @DatabaseOLDName			sysname  = 'test',										
        @DatabaseNewName			sysname  = 'test1',										
        @PrimaryDataFileName		        sysname  = 'test',									
	@SecDataFileName                        sysname  =  NULL,
        @DatabaseLogFileName		        sysname  = 'test_log',								
	@PrimaryDataFileCreatePath              sysname  = 'D:\MISC\Bkp\testdata.mdf',			
        @SecDataFileCreatePath		        sysname  = NULL,									
        @SecDataFileCreatePath1		        sysname  = NULL,									
        @DatabaseLogFileCreatePath              sysname  = 'D:\MISC\Bkp\test_log.ldf',		
        @PITRDateTime				datetime = '2022-08-11T20:44:11';

DECLARE @command				nvarchar(MAX),
        @OldPhysicalPathName		        nvarchar(MAX),
        @FullBackupDateTime			datetime,
        @DiffBackupDateTime			datetime,
        @LogBackupDateTime			datetime,
        @message				nvarchar(MAX);

SET @command = N'RESTORE DATABASE @DatabaseNewName FROM DISK = @OldPhysicalPathName WITH FILE = 1, NORECOVERY, NOUNLOAD, REPLACE, STATS = 5, STOPAT = @PITRDateTime,
     MOVE N''' + @PrimaryDataFileName + N''' TO N''' + @PrimaryDataFileCreatePath + N''','
           + COALESCE('
     MOVE N''' + @SecDataFileName + ''' TO N''' + @SecDataFileCreatePath + ''',', '')
           + N'
     MOVE N''' + @DatabaseLogFileName + N''' TO N''' + @DatabaseLogFileCreatePath + N''';';

SELECT     TOP (1) @OldPhysicalPathName = bmf.physical_device_name,@FullBackupDateTime = bs.backup_start_date
FROM       msdb.dbo.backupset AS bs INNER JOIN msdb.dbo.backupmediafamily AS bmf ON  bmf.media_set_id = bs.media_set_id
WHERE      bs.database_name = @DatabaseOLDName AND  bs.type= 'D' AND  bs.backup_start_date < @PITRDateTime
ORDER BY   bs.backup_start_date DESC;

SET @message = N'Starting restore of full backup file '+ @OldPhysicalPathName + N', taken ' + CONVERT(nvarchar(30), @FullBackupDateTime, 120);

RAISERROR(@message, 0, 1) WITH NOWAIT;

EXEC sys.sp_executesql @command,
                       N'@DatabaseNewName sysname, @OldPhysicalPathName nvarchar(260), @PITRDateTime datetime',
                       @DatabaseNewName,
                       @OldPhysicalPathName,
                       @PITRDateTime;


SET @command = N'RESTORE DATABASE @DatabaseNewName FROM DISK = @OldPhysicalPathName WITH FILE = 1, NORECOVERY, NOUNLOAD, REPLACE, STATS = 5, STOPAT = @PITRDateTime;';

SELECT     TOP (1) @OldPhysicalPathName = bmf.physical_device_name,@DiffBackupDateTime = bs.backup_start_date
FROM       msdb.dbo.backupset   AS bs INNER JOIN msdb.dbo.backupmediafamily AS bmf ON  bmf.media_set_id = bs.media_set_id
WHERE      bs.database_name = @DatabaseOLDName AND  bs.type  = 'I' AND  bs.backup_start_date >= @FullBackupDateTime AND  bs.backup_start_date< @PITRDateTime
ORDER BY   bs.backup_start_date DESC;

IF @@ROWCOUNT > 0
BEGIN;
    SET @message = N'Starting restore of differential backup file ' + @OldPhysicalPathName + N', taken ' + CONVERT(nvarchar(30), @DiffBackupDateTime, 120);

    RAISERROR(@message, 0, 1) WITH NOWAIT;

EXEC sys.sp_executesql @command,
                       N'@DatabaseNewName sysname, @OldPhysicalPathName nvarchar(260), @PITRDateTime datetime',
                       @DatabaseNewName,
                       @OldPhysicalPathName,
                       @PITRDateTime;
END;

SET @command = N'RESTORE LOG @DatabaseNewName
FROM DISK = @OldPhysicalPathName
WITH FILE = 1, NORECOVERY, NOUNLOAD, REPLACE, STATS = 5, STOPAT = @PITRDateTime;';

DECLARE c CURSOR LOCAL FAST_FORWARD READ_ONLY TYPE_WARNING FOR
SELECT     bmf.physical_device_name,
           bs.backup_start_date
FROM       msdb.dbo.backupset         AS bs
INNER JOIN msdb.dbo.backupmediafamily AS bmf
   ON      bmf.media_set_id = bs.media_set_id
WHERE      bs.database_name = @DatabaseOLDName
AND        bs.type                 = 'L'
AND        bs.backup_start_date    >= COALESCE(@DiffBackupDateTime, @FullBackupDateTime)
ORDER BY   bs.backup_start_date ASC;

OPEN c;

FETCH NEXT FROM c
INTO @OldPhysicalPathName,
     @LogBackupDateTime;

WHILE @@FETCH_STATUS = 0
BEGIN;
    SET @message = N'Starting restore of log backup file '
                   + @OldPhysicalPathName + N', taken '
                   + CONVERT(nvarchar(30), @LogBackupDateTime, 120);
    RAISERROR(@message, 0, 1) WITH NOWAIT;
    EXEC sys.sp_executesql @command,
                           N'@DatabaseNewName sysname, @OldPhysicalPathName nvarchar(260), @PITRDateTime datetime',
                           @DatabaseNewName,
                           @OldPhysicalPathName,
                           @PITRDateTime;

    IF @LogBackupDateTime >= @PITRDateTime
        BREAK;

    FETCH NEXT FROM c
    INTO @OldPhysicalPathName,
         @LogBackupDateTime;
END;

CLOSE c;
DEALLOCATE c;

SET @command = N'RESTORE DATABASE @DatabaseNewName
WITH RECOVERY;';

RAISERROR('Starting recovery', 0, 1) WITH NOWAIT;
EXEC sys.sp_executesql @command,
                       N'@DatabaseNewName sysname, @OldPhysicalPathName nvarchar(260), @PITRDateTime datetime',
                       @DatabaseNewName,
                       @OldPhysicalPathName,
                       @PITRDateTime;
GO

datablogs

Data will talk to you if you're willing to listen !!!

Categories

Thursday, February 19, 2026

MySQL Binlog Restore using Python

Tuesday, December 23, 2025

Steps to Change TempDB Location in SQL Server

SQL Server High CPU Issue Caused by Implicit Conversion (nvarchar vs varchar)

Friday, October 17, 2025

How to upgrade MySQL from 8.0 to 8.4 ?

Monday, June 23, 2025

MySQL ERROR 1146 (42S02): Table 'datablogs.tbl_followup' doesn't exist

Tuesday, November 14, 2023

PostgreSQL Table Partition on AWS RDS

Wednesday, June 21, 2023

Oracle RDS Audit log enable

Friday, February 24, 2023

How to Automate MongoDB Database Backups in Linux

Friday, February 17, 2023

Restore and Point in time Restore with Percona Backup of MongoDB on another instance

How to tune long running Queries on Azure Synapse DW (Dedicated Pool) - Part 2

Monday, January 30, 2023

Update Backup and Restore in Same SQL Server RDS

Thursday, December 15, 2022

ORA-01940 cannot drop a user that is currently connected

Sunday, August 28, 2022

How to tune long running Queries on Azure Synapse DW (Dedicated Pool) - Part 1

Thursday, August 11, 2022

SQL Server Point in Time Recovery