Preparing backup for SQLs

preparing-backup-for-sqls.md

Preparing backup for SQLs

Let's image a situation when you need to run some SQLs manually on PRODUCTION environment. Before applying any changes on PRODUCTION, It's always better to prepare backups and rollbacks for the affected data if you would need to return the previous changes.

Backup

One of the possible ways to prepare a backup is storing the affected data into the backup table. Your user should be able to create new tables.

It can be done with the next SQL

PostgreSQL

create table schema.table_backup_01012024 as
(
select
	*
from
	schema.table
where
	reference_id in (
	select
		id
	from
		schema.references
	where
		need_update = true)
) with data;

MSSQL

SELECT
	*
into
	schema.dbo.table_backup_01012024
from
	table
where
	reference_id in (
	select
		id
	from
		schema.dbo.references
	where
		need_update = true)
;

Rollback

This is about applying your backups in DB. It can be done with updating data in the affected rows from the backup table.

It can be done with the next SQL

update
	schema.table as out 
set
	reference_id = in.reference_id
from
	schema.table_backup_01012024 as out
where 
	out.property_id = in.property_id;

Example

We need to update some fields in the specific table in the DB. So, for this case we could do the next:

1. SQL to be run on PRODUCTION

update
	schema.table
set
	reference_id = null
where
	reference_id in (
	select
		id
	from
		schema.references
	where
		need_update = true);

2. Backup can be done with the SQL

create table schema.table_backup_01012024 as
(
select
	*
from
	schema.table
where
	reference_id in (
	select
		id
	from
		schema.references
	where
		need_update = true)

) with data;

3. Rollback SQL can be the next

update
	schema.table as dest 
set
	reference_id = src.reference_id
from
	schema.table_backup_01012024 as src
where 
	dest.property_id = src.property_id;