Mengembalikan Data yang Terhapus di SQL Server

Melakukan operasi UPDATE dan DELETE tanpa klausa WHERE dapat menyebabkan jutaan baris data terpengaruhi (bermasalah) atau bahkan hilang terhapus. Seperti mimpi buruk jika seorang DBA melakukan kesalahan melakukan operasi tersebut. Bisakah kita mengembalikan data yang terhapus secara tidak sengaja di SQL Server? Jawabannya BISA (dengan syarat tertentu).

Kali ini saya akan membagikan penjelasan tentang pemulihan data di SQL Server dan beberapa metode untuk mengembalikan data yang terhapus di SQL Server.

Persyaratan

Sebelum masuk ke pembahasan, kita mengenal sedikit membahas tentang Transaction Logs. Mengapa demikian? Karena untuk melakukan proses pemulihan data akan mengandalkan Transaction Logs atau Log Transaksi di SQL Server.

SYARAT
Untuk dapat memulihkan data yang terhapus dari tabel, model recovery database harus FULL atau BULK-LOGGED. Model recovery SIMPLE tidak mendukung backup log transaksi sehingga tidak mungkin dapat melakukan proses pemulihan.

Log Transaksi adalah file – bagian integral dari setiap database SQL Server. File ini berisi catatan log yang dihasilkan selama proses logging di database SQL Server. Log transaksi merupakan komponen paling penting di database SQL Server yang berguna untuk membantu proses disaster recovery atau pemulihan data. Transaksi terjadi disetiap proses modifikasi database, catatan log akan ditulis ke log transaksi. Semua perubahan di database akan ditulis secara berurutan.

Solusi Pemulihan

Ada beberapa cara untuk memulihkan data yang bermasalah (ter-update) atau bahkan terhapus. Mulai dari cara restore file backup sampai dengan mengandalkan software SQL Recovery.

1. Menggunakan Cara Restore File Backup

Cara umum dan sederhana yang mungkin dapat dilakukan untuk menangani operasi UPDATE dan DELETE yang tidak disengaja adalah mengandalkan file backup database. Solusi ini dapat diimplementasikan dengan berbagai cara seperti:

  1. Restore file backup terakhir ke server yang sama tapi dengan nama yang berbeda. Pastikan file backup terakhir dibuat sebelum Anda melakukan kesalahan operasi UPDATE dan DELETE.
  2. Cari data yang bermasalah (ter-update) dan yang terhapus. Jika ada, Anda dapat mengambilnya dan mengembalikannya ke database yang bermasalah.

Skenario yang disebutkan di atas hanya dapat dilakukan jika Anda memiliki file backup yang dibuat sebelum Anda melakukan kesalahan operasi UPDATE dan DELETE. Dalam kebanyakan kasus solusi ini gagal karena alasan:

  1. Tidak melakukan backup database otomatis
  2. Bertambahnya data baru yang dihasilkan oleh pengguna yang masih menggunakan aplikasi yang menyimpan data di database tersebut.
Baca Juga:  Microsoft Membawa SQL Server Untuk Linux

Anda juga dapat menggunakan fungsi fn_dblog untuk membantu Anda dalam membaca log transaksi SQL Server dan menelusuri data yang bermasalah (ter-update) atau data yang terhapus secara tidak sengaja, tapi data yang ditampilkan sangat acak dan sulit untuk memulihkan data yang sudah terhapus.

2. Menggunakan ApexSQL Recover

Ada solusi alternatif jika Anda memang tidak memiliki file backup database yang bermasalah. Anda dapat menggunakan ApexSQL Recover. Cara ApexSQL Recover memulihkan data yang hilang dengan cara membaca file data database .MDF dan file log transaksi .LDF.

Bagian ini kita akan melakukan demo pemulihan data yang bermasalah (ter-update) atau terhapus (anggap saja dengan tidak sengaja).

1. Buat Contoh Database dan Tabel

Buka SQL Management Studio dan kueri baru untuk membuat contoh database dan satu tabel mengunakan perintah SQL dibawah ini:

/*
 * Buat contoh database
 * dengan nama "DisasterRecovery"
 **/
USE [master]
GO

CREATE DATABASE DisasterRecovery
GO

ALTER DATABASE DisasterRecovery SET RECOVERY FULL 
GO

/*
 * Tambahkan satu tabel
 * dengan nama "Employee"
 **/
USE DisasterRecovery
GO

CREATE TABLE dbo.Employee (
	EmployeeId INT IDENTITY(1,1) NOT NULL,
	Name VARCHAR(150) NULL,
	Department VARCHAR(50) NULL,
	Active BIT NULL,

	CONSTRAINT PK_Employee PRIMARY KEY CLUSTERED (EmployeeId ASC)
	WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CATATAN
Dalam demonstrasi ini, saya menggunakan Microsoft SQL Server 2017 Enterprise di Windows 10.

2. Tambahkan Contoh Data

Untuk menambahkan contoh data ke tabel dbo.Employee, Anda dapat menjalankan perintah dibawah ini:

/*
 * Tambahkan contoh data
 **/
USE DisasterRecovery
GO

INSERT INTO dbo.Employee (Name, Department, Active) VALUES
('FAIQ AHMAD FAUZI', 'Accounting', '1'),
('FARID ARIF NASRULLAH', 'Accounting', '1'),
('FITRIA RAHMAWATI AZZAHRA', 'Accounting', '1'),
('HAIBA MUHAMMAD NURWAHID', 'Accounting', '1'),
('HANA LUTFIANA ZULFA', 'Accounting', '1'),
('HERNI JULIANTI', 'Finance', '1'),
('HESTI SRI WULANDARI', 'Finance', '1'),
('HILMA QANIA RAHMAN', 'Finance', '1'),
('IHFAZIELLAH YAHFAZKI', 'Finance', '1'),
('IIF NUR ARIFIN', 'Finance', '1'),
('IMELDA JANUAR', 'IT', '1'),
('IMRON ROSADI', 'IT', '1'),
('IRDAM APRILHADI', 'IT', '1'),
('IZLAL MULKI RAMADHAN', 'IT', '1'),
('MIRAA SHOFIYAH', 'IT', '1')

GO

Sampai tahap ini Anda dapat memeriksa dan memastikan bahwa data sudah masuk ke tabel dbo.Employee dengan menjalankan perintah dibawah ini:

USE DisasterRecovery
GO

SELECT * FROM dbo.Employee
-- Output: Menampilkan 15 baris data pegawai

3. Hapus Beberapa Baris Data

Dalam tahap ini Anda dapat menghapus beberapa baris data. Anggap saja terhapus dengan tidak sengaja. Pastikan ID baris data (nilai pada kolom EmployeeId) yang akan dihapus di salin (ke notepad), tujuannya agar kita dapat mencari data yang terhapus berdasarkan EmployeeId. Kita akan mencoba hapus 3 baris data dengan nilai EmployeeId: 2, 4 dan 8.

USE DisasterRecovery
GO

-- Menghapus data employee dimana EmployeeId memiliki nilai 2, 4, 8
DELETE FROM dbo.Employee WHERE EmployeeId IN (2, 4, 8)
GO

Anda dapat menjalankan perintah SELECT untuk memastikan bahwa ID 2, 4 dan 8 sudah tidak ada pada tabel dbo.Employee.

4. Kembalikan Data yang Terhapus

Anda dapat membuka ApexSQL Recover dan pilih menu recover Transaction Log Data. And akan dimintai kredensial untuk mengakses SQL Server; kemudian pilih database DisasterRecovery seperti gambar dibawah ini:

Kredensial ApexSQL Recover
Masukan kredensial SQL Server Anda.

PENTING
Jika ApexSQL Recover menutup sendiri atau force close pada saat mengklik tombol Next, ulangi buka ApexSQL Recover kemudian masukan ulang kredensial SQL Server. Kemungkinan besar ini bug atau bisa jadi belum sepenuhnya stabil dengan versi SQL Server 2017. Tapi setelah diulangi, ApexSQL Recover berhasil masuk.

Baca Juga:  Konversi dan Optimasi Gambar ke JPG dengan Jpeg.io

Pada halaman wizard selanjutnya (seperti gambar dibawah ini), Anda akan diminta untuk memilih file data log. Disana Anda dapat menambahkan jika memang Anda memiliki file backup data log sebelumnya.

ApexSQL Recover Pilih Data Log
Menentukan file Log Transaksi yang tersedia.

Setelah menentukan log transaksi, Anda akan diminta untuk memilih output atau cara apa yang Anda inginkan untuk memulihkan data. Anda dapat memilih Open result in grid; artinya Anda dapat menelusuri catatan log transaksi (operasi INSERT, UPDATE dan DELETE) dan memilih data yang akan dikembalikan.

ApexSQL Recover Select Output
Pilih output untuk menampilkan atau mengembalikan data.

Kemudian Anda akan diminta untuk memfilter data. Ada dapat mengingat baik-baik tanggal dan jam pada saat melakukan kesalahan operasi. Filter ini akan mempersempit data yang akan ditampilkan. Setelah melalui proses ini, Anda dapat melanjutkan dengan menekan tombol Finish.

ApexSQL Select Filter
Tentukan tanggal dan jam kejadian untuk memfilter data.

Terakhir Anda dapat melihat catatan log transaksi selama database tersebut beroperasi (digunakan). Anda dapat menyeleksi data mana yang akan dikembalikan, kemudian pilih Create undo script di menu klik kanan untuk menghasilkan kueri operasi INSERT ke tabel dbo.Employee (mengembalikan data). Atau Anda dapat menggunakan tombol Undo Script dibawah sebelah kiri Anda untuk melihat undo script berdasarkan data yang dipilih.

ApexSQL Recover Result
3 baris data yang dihapus (ID 2, 4, 8) tercatat di log transaksi.
ApexSQL Recover Undo Script

Anda dapat menyalin dan menjalankan kueri baru Undo Script di SQL Server Management Studio untuk mengembalikan data. Dalam hal ini perintah yang akan dijalankan seperti dibawah ini:

--	This UNDO script was generated with ApexSQL Recover 2018.02.1198 on 2019-05-03 01:33:16.376
--	NOTE: Operations in UNDO scripts are always output in descending order.
--	SERVER SKELETON
--	DATABASE DisasterRecovery

--	DELETE (00000024:0000022B:0006) done at 2019-05-02 23:56:28.490 by sa in transaction 0000:000003AF (Committed)
SET IDENTITY_INSERT [dbo].[Employee] ON
INSERT INTO [dbo].[Employee] ([EmployeeId], [Name], [Department], [Active]) VALUES (8, N'HILMA QANIA RAHMAN' COLLATE Latin1_General_CI_AS, N'Finance' COLLATE Latin1_General_CI_AS, 1)
--	DELETE (00000024:0000022B:0005) done at 2019-05-02 23:56:28.490 by sa in transaction 0000:000003AF (Committed)
INSERT INTO [dbo].[Employee] ([EmployeeId], [Name], [Department], [Active]) VALUES (4, N'HAIBA MUHAMMAD NURWAHID' COLLATE Latin1_General_CI_AS, N'Accounting' COLLATE Latin1_General_CI_AS, 1)
--	DELETE (00000024:0000022B:0002) done at 2019-05-02 23:56:28.490 by sa in transaction 0000:000003AF (Committed)
INSERT INTO [dbo].[Employee] ([EmployeeId], [Name], [Department], [Active]) VALUES (2, N'FARID ARIF NASRULLAH' COLLATE Latin1_General_CI_AS, N'Accounting' COLLATE Latin1_General_CI_AS, 1)
SET IDENTITY_INSERT [dbo].[Employee] OFF
GO

--	FINISHED ON 2019-05-03 01:33:16.377
--	TOTAL OPERATIONS PROCESSED 3
--	END OF FILE

Setelah Anda menjalankan Undo Script diatas, Anda dapat memeriksa bahwa data yang sudah terhapus dapat dikembalikan dengan cara menggunakan perintah SELECT:

USE DisasterRecover
GO

SELECT * FROM dbo.Employee
-- Output: 3 baris data yang dihapus (tidak disengaja) telah kembali

DOWNLOAD APEXSQL RECOVER
Harga Lisensi ApexSQL Recover edisi Developer Standard disitus resminya yaitu $799. Didalamnya mencakup fitur Auditing, Backup, BI Monitoring, Recovery dan lihat lebih jelasnya disini.

Baca Juga:  Microsoft Membawa SQL Server Untuk Linux

Kesimpulan

Sekarang kita tahu bahwa seberapa pentingnya file log transaksi SQL Server. Sampai saat ini, saya hanya mengenal dua cara diatas untuk melihat dan membaca log transaksi guna untuk memulihkan data yang bermasalah (ter-update) atau bahkan terhapus. Anda dapat memilih salah satu teknik sesuai pilihan Anda sendiri. Perlu bantuan? Anda dapat menanyakan melalui komentar dibawah ini.