Gambaran umum pemulihan dan pemulihan (SQL Server)

  • Artikel

Berlaku untuk:SQL Server

Untuk memulihkan database SQL Server dari kegagalan, administrator database harus memulihkan sekumpulan cadangan SQL Server dalam urutan pemulihan yang benar dan bermakna secara logis. Pemulihan dan pemulihan SQL Server mendukung pemulihan data dari cadangan seluruh database, file data, atau halaman data, sebagai berikut:

  • Database ( pemulihan database lengkap)

    Seluruh database dipulihkan dan dipulihkan, dan database offline selama operasi pemulihan dan pemulihan.

  • File data ( pemulihan file)

    File data atau sekumpulan file dipulihkan dan dipulihkan. Selama pemulihan file, grup file yang berisi file secara otomatis offline selama pemulihan. Setiap upaya untuk mengakses grup file offline menyebabkan kesalahan.

  • Halaman data ( pemulihan halaman)

    Di bawah model pemulihan penuh atau model pemulihan yang dicatat secara massal, Anda dapat memulihkan halaman individual. Pemulihan halaman dapat dilakukan pada database apa pun, terlepas dari jumlah grup file.

Pencadangan dan pemulihan SQL Server berfungsi di semua sistem operasi yang didukung. Untuk informasi tentang sistem operasi yang didukung, lihat Persyaratan Perangkat Keras dan Perangkat Lunak untuk Menginstal SQL Server 2016. Untuk informasi tentang dukungan untuk pencadangan dari versi SQL Server sebelumnya, lihat bagian "Dukungan Kompatibilitas" dari RESTORE (Transact-SQL).

Gambaran umum skenario pemulihan

Skenario pemulihan di SQL Server adalah proses memulihkan data dari satu atau beberapa cadangan lalu memulihkan database. Skenario pemulihan yang didukung bergantung pada model pemulihan database dan edisi SQL Server.

Tabel berikut memperkenalkan kemungkinan skenario pemulihan yang didukung untuk model pemulihan yang berbeda.

Skenario pemulihan Di bawah model pemulihan sederhana Di bawah model pemulihan penuh/dicatat massal
Menyelesaikan pemulihan database Ini adalah strategi pemulihan dasar. Pemulihan database lengkap mungkin hanya melibatkan pemulihan dan pemulihan cadangan database lengkap. Atau, pemulihan database lengkap mungkin melibatkan pemulihan cadangan database lengkap diikuti dengan memulihkan dan memulihkan cadangan diferensial.

Untuk informasi selengkapnya, lihat Menyelesaikan Pemulihan Database (Model Pemulihan Sederhana).		 Ini adalah strategi pemulihan dasar. Pemulihan database lengkap melibatkan pemulihan cadangan database lengkap dan, secara opsional, cadangan diferensial (jika ada), diikuti dengan memulihkan semua cadangan log berikutnya (secara berurutan). Pemulihan database lengkap selesai dengan memulihkan cadangan log terakhir dan juga memulihkannya (PULIHKAN DENGAN PEMULIHAN).

Untuk informasi selengkapnya, lihat Pemulihan Database Lengkap (Model Pemulihan Penuh)
Pemulihan file 1 Pulihkan satu atau beberapa file baca-saja yang rusak, tanpa memulihkan seluruh database. Pemulihan file hanya tersedia jika database memiliki setidaknya satu grup file baca-saja. Memulihkan satu atau beberapa file, tanpa memulihkan seluruh database. Pemulihan file dapat dilakukan saat database offline atau, untuk beberapa edisi SQL Server, sementara database tetap online. Selama pemulihan file, grup file yang berisi file yang sedang dipulihkan selalu offline.
Pemulihan halaman Tidak berlaku Memulihkan satu atau beberapa halaman yang rusak. Pemulihan halaman dapat dilakukan saat database offline atau, untuk beberapa edisi SQL Server, sementara database tetap online. Selama pemulihan halaman, halaman yang sedang dipulihkan selalu offline.

Rantai cadangan log yang tidak diborok harus tersedia, hingga file log saat ini, dan semuanya harus diterapkan untuk memutakhirkan halaman dengan file log saat ini.

Untuk informasi selengkapnya, lihat Memulihkan Halaman (SQL Server).
Pemulihan sepotong 1 Pulihkan dan pulihkan database secara bertahap di tingkat grup file, dimulai dengan grup file sekunder utama dan semua baca/tulis. Pulihkan dan pulihkan database secara bertahap di tingkat grup file, dimulai dengan grup file utama.

Untuk informasi selengkapnya, lihat Pemulihan Piecemeal (SQL Server)

1 Pemulihan online hanya didukung di edisi Enterprise.

Langkah-langkah untuk memulihkan database

Untuk melakukan pemulihan file, Mesin Database menjalankan dua langkah:

  • Membuat file database yang hilang.

  • Menyalin data dari perangkat cadangan ke file database.

Untuk melakukan pemulihan database, Mesin Database menjalankan tiga langkah:

  • Membuat database dan file log transaksi jika belum ada.

  • Menyalin semua halaman data, log, dan indeks dari media cadangan database ke file database.

  • Menerapkan log transaksi, dalam apa yang dikenal sebagai proses pemulihan.

Terlepas dari bagaimana data dipulihkan, sebelum database dapat dipulihkan, Mesin Database SQL Server menjamin bahwa seluruh database konsisten secara logis. Misalnya, jika Anda memulihkan file, Anda tidak dapat memulihkannya dan membuatnya online sampai digulirkan cukup jauh ke depan agar konsisten dengan database.

Keuntungan dari pemulihan file atau halaman

Memulihkan dan memulihkan file atau halaman, alih-alih seluruh database, memberikan keuntungan berikut:

  • Memulihkan lebih sedikit data mengurangi waktu yang diperlukan untuk menyalin dan memulihkannya.

  • Pada SQL Server memulihkan file atau halaman mungkin memungkinkan data lain dalam database tetap online selama operasi pemulihan.

Pemulihan dan log transaksi

Untuk sebagian besar skenario pemulihan, perlu untuk menerapkan pencadangan log transaksi dan memungkinkan Mesin Database SQL Server menjalankan proses pemulihan agar database dibawa secara online. Pemulihan adalah proses yang digunakan oleh SQL Server untuk setiap database agar dimulai dalam kondisi konsisten secara transaksional - atau bersih - .

Dalam kasus failover atau mematikan non-bersih lainnya, database mungkin dibiarkan dalam keadaan di mana beberapa modifikasi tidak pernah ditulis dari cache buffer ke file data, dan mungkin ada beberapa modifikasi dari transaksi yang tidak lengkap dalam file data. Ketika instans SQL Server dimulai, SQL Server menjalankan pemulihan setiap database, yang terdiri dari tiga fase, berdasarkan titik pemeriksaan database terakhir:

  • Fase 1 adalah Fase Analisis yang menganalisis log transaksi untuk menentukan apa titik pemeriksaan terakhir, dan membuat Tabel Halaman Kotor (DPT) dan Tabel Transaksi Aktif (ATT). DPT berisi catatan halaman yang kotor pada saat database dimatikan. ATT berisi rekaman transaksi yang aktif pada saat database tidak dimatikan dengan bersih.

  • Fase 2 adalah Fase Pengulangan yang meneruskan setiap modifikasi yang dicatat dalam log yang mungkin belum ditulis ke file data pada saat database dimatikan. Nomor urutan log minimum (minLSN) yang diperlukan untuk pemulihan di seluruh database yang berhasil ditemukan di DPT, dan menandai dimulainya operasi pengulangan yang diperlukan pada semua halaman kotor. Pada fase ini, Mesin Database SQL Server menulis ke disk semua halaman kotor milik transaksi yang dilakukan.

  • Fase 3 adalah Fase Urungkan yang mengembalikan transaksi yang tidak lengkap yang ditemukan di ATT untuk memastikan integritas database dipertahankan. Setelah pembatalan, database online, dan tidak ada lagi cadangan log transaksi yang dapat diterapkan ke database.

Informasi tentang kemajuan setiap tahap pemulihan database dicatat di log kesalahan SQL Server. Kemajuan pemulihan database juga dapat dilacak menggunakan Extended Events. Untuk informasi selengkapnya, lihat posting blog Peristiwa baru yang diperluas untuk kemajuan pemulihan database.

Catatan

Untuk skenario pemulihan Piecemeal, jika grup file baca-saja telah bersifat baca-saja karena sebelum cadangan file dibuat, menerapkan cadangan log ke grup file tidak perlu dan dilewati oleh pemulihan file.

Catatan

Untuk memaksimalkan ketersediaan database di lingkungan perusahaan setelah layanan SQL Server dimulai, seperti setelah failover Instans Kluster Failover AlwaysOn atau mulai ulang di tempat, SQL Server Enterprise Edition dapat membawa database online setelah Fase Pengulangan, sementara Fase Batalkan masih dijalankan. Ini dikenal sebagai Pemulihan Cepat.
Namun, Pemulihan Cepat tidak tersedia ketika database beralih ke status online tetapi layanan SQL Server belum dimulai ulang. Misalnya, menjalankan ALTER DATABASE AdventureWorks SET ONLINE; tidak akan memungkinkan database berada dalam status baca-tulis sampai ketiga fase pemulihan selesai.

Model pemulihan dan operasi pemulihan yang didukung

Operasi pemulihan yang tersedia untuk database bergantung pada model pemulihannya. Tabel berikut ini meringkas apakah dan sejauh mana setiap model pemulihan mendukung skenario pemulihan tertentu.

Operasi pemulihan Model pemulihan penuh Model pemulihan yang dicatat secara massal Model pemulihan sederhana
Pemulihan data Pemulihan lengkap (jika log tersedia). Beberapa paparan kehilangan data. Data apa pun sejak pencadangan penuh atau diferensial terakhir hilang.
Pemulihan titik waktu Setiap kali dicakup oleh cadangan log. Tidak diizinkan jika cadangan log berisi perubahan yang dicatat secara massal. Tidak didukung.
Pemulihan file 1 Dukungan penuh. Terkadang.2 Hanya tersedia untuk file sekunder baca-saja.
Pemulihan halaman 1 Dukungan penuh. Terkadang.2 Tidak ada.
Pemulihan piecemeal (tingkat grup file) 1 Dukungan penuh. Terkadang.2 Hanya tersedia untuk file sekunder baca-saja.

1 Hanya tersedia di SQL Server edisi Perusahaan

2 Untuk kondisi yang diperlukan, lihat Memulihkan Pembatasan Di bawah Model Pemulihan Sederhana, nanti di artikel ini.

Penting

Terlepas dari model pemulihan database, cadangan SQL Server tidak dapat dipulihkan ke versi Mesin Database SQL Server yang lebih lama dari versi yang membuat cadangan.

Pulihkan skenario di bawah model pemulihan sederhana

Model pemulihan sederhana memberlakukan pembatasan berikut pada operasi pemulihan:

Jika salah satu pembatasan ini tidak sesuai untuk kebutuhan pemulihan Anda, kami sarankan Anda mempertimbangkan untuk menggunakan model pemulihan penuh. Untuk informasi selengkapnya, lihat Gambaran Umum Pencadangan (SQL Server).

Penting

Terlepas dari model pemulihan database, cadangan SQL Server tidak dapat dipulihkan oleh versi SQL Server yang lebih lama dari versi yang membuat cadangan.

Pulihkan di bawah model pemulihan yang dicatat secara massal

Bagian ini membahas pertimbangan pemulihan yang unik untuk model pemulihan yang dicatat secara massal, yang dimaksudkan secara eksklusif sebagai suplemen untuk model pemulihan penuh.

Catatan

Untuk pengenalan model pemulihan yang dicatat secara massal, lihat Log Transaksi (SQL Server).

Umumnya, model pemulihan yang dicatat secara massal mirip dengan model pemulihan penuh, dan informasi yang dijelaskan untuk model pemulihan penuh juga berlaku untuk keduanya. Namun, pemulihan point-in-time dan pemulihan online dipengaruhi oleh model pemulihan yang dicatat secara massal.

Pembatasan untuk pemulihan point-in-time

Jika cadangan log yang diambil di bawah model pemulihan yang dicatat secara massal berisi perubahan yang dicatat secara massal, pemulihan point-in-time tidak diizinkan. Mencoba melakukan pemulihan point-in-time pada cadangan log yang berisi perubahan massal akan menyebabkan operasi pemulihan gagal.

Pembatasan untuk pemulihan online

Urutan pemulihan online hanya berfungsi jika kondisi berikut terpenuhi:

  • Semua cadangan log yang diperlukan harus diambil sebelum urutan pemulihan dimulai.

  • Perubahan massal harus didukung sebelum memulai urutan pemulihan online.

  • Jika perubahan massal ada dalam database, semua file harus online atau tidak berfungsi. (Ini berarti bahwa database tidak lagi menjadi bagian dari database.)

Jika kondisi ini tidak terpenuhi, urutan pemulihan online gagal.

Catatan

Sebaiknya beralih ke model pemulihan penuh sebelum memulai pemulihan online. Untuk informasi selengkapnya, lihat Model Pemulihan (SQL Server).

Untuk informasi tentang cara melakukan pemulihan online, lihat Pemulihan Online (SQL Server).

Database Recovery Advisor (SQL Server Management Studio)

Database Recovery Advisor memfasilitasi pembuatan rencana pemulihan yang menerapkan urutan pemulihan yang benar yang optimal. Banyak masalah pemulihan database yang diketahui dan penyempurnaan yang diminta oleh pelanggan telah diatasi. Peningkatan utama yang diperkenalkan oleh Database Recovery Advisor meliputi hal-hal berikut:

  • Algoritma rencana pemulihan: Algoritma yang digunakan untuk membuat rencana pemulihan telah meningkat secara signifikan, terutama untuk skenario pemulihan yang kompleks. Banyak kasus tepi, termasuk skenario forking dalam pemulihan titik waktu, ditangani lebih efisien daripada di versi SQL Server sebelumnya.

  • Pemulihan titik waktu: Database Recovery Advisor sangat menyederhanakan pemulihan database ke titik waktu tertentu. Garis waktu pencadangan visual secara signifikan meningkatkan dukungan untuk pemulihan titik waktu. Garis waktu visual ini memungkinkan Anda mengidentifikasi titik waktu yang layak sebagai titik pemulihan target untuk memulihkan database. Garis waktu memfasilitasi melintasi jalur pemulihan fork (jalur yang mencakup fork pemulihan). Rencana pemulihan titik waktu tertentu secara otomatis menyertakan cadangan yang relevan dengan pemulihan ke titik waktu target Anda (tanggal dan waktu). Untuk informasi selengkapnya, lihat Memulihkan Database SQL Server ke Titik Waktu (Model Pemulihan Penuh).

Untuk informasi selengkapnya, lihat tentang Database Recovery Advisor, lihat blog Pengelolaan SQL Server berikut ini:

Pemulihan database yang dipercepat

Pemulihan database yang dipercepat tersedia mulai dari SQL Server 2019 (15.x) dan di Azure SQL Database. Pemulihan database yang dipercepat sangat meningkatkan ketersediaan database, terutama di hadapan transaksi yang berjalan lama, dengan mendesain ulang proses pemulihan Mesin Database SQL Server. Database yang pemulihan database yang dipercepat diaktifkan menyelesaikan proses pemulihan secara signifikan lebih cepat setelah failover atau non-bersih lainnya dimatikan. Ketika diaktifkan, pemulihan database yang dipercepat juga menyelesaikan pembatalan transaksi jangka panjang yang dibatalkan secara signifikan lebih cepat.

Anda dapat mengaktifkan pemulihan database yang dipercepat per database pada SQL Server 2019 (15.x) menggunakan sintaks berikut:

ALTER DATABASE [<db_name>] SET ACCELERATED_DATABASE_RECOVERY = ON;

Catatan

Pemulihan database yang dipercepat diaktifkan secara default di Azure SQL Database.

Langkah berikutnya