Cadangkan dan pulihkan database SQL Server

Berlaku untuk:SQL Server

Artikel ini menjelaskan manfaat mencadangkan database SQL Server, memperkenalkan istilah dasar backup dan pemulihan, serta membahas strategi backup dan restore serta pertimbangan keamanan untuk SQL Server.

Catatan

Artikel ini memperkenalkan cadangan SQL Server. Untuk langkah-langkah tertentu untuk mencadangkan database SQL Server, lihat Membuat cadangan.

Komponen backup dan restore SQL Server menyediakan perlindungan penting untuk data penting yang disimpan di database SQL Server Anda. Untuk meminimalkan risiko kehilangan data yang katastrofik, lakukan cadangan database Anda secara rutin untuk mempertahankan modifikasi pada data Anda. Strategi backup dan restore yang terencana dengan baik membantu melindungi database dari kehilangan data yang disebabkan oleh berbagai jenis kegagalan. Uji strategi Anda dengan memulihkan serangkaian cadangan lalu memulihkan database Anda, sehingga Anda siap merespons bencana.

Selain penyimpanan lokal, SQL Server juga mendukung backup dan pemulihan dari Azure Blob Storage. Untuk informasi selengkapnya, lihat Pencadangan dan pemulihan SQL Server dengan Azure Blob Storage. Untuk file database yang disimpan menggunakan Azure Blob Storage, SQL Server 2016 (13.x) menyediakan opsi untuk menggunakan rekam jepret Azure untuk cadangan yang hampir seketika dan pemulihan yang lebih cepat. Untuk informasi selengkapnya, lihat Pencadangan rekam jepret file untuk file database di Azure. Azure juga menawarkan solusi pencadangan kelas perusahaan untuk SQL Server yang berjalan di Azure VM. Solusi pencadangan yang dikelola sepenuhnya, solusi ini mendukung grup ketersediaan Always On, retensi jangka panjang, pemulihan ke titik waktu tertentu, serta pengelolaan dan pemantauan terpusat. Untuk informasi selengkapnya, lihat Tentang pencadangan SQL Server di Azure VM.

Mengapa membuat cadangan?

• Mencadangkan database SQL Server Anda, menjalankan prosedur pengujian pemulihan pada cadangan Anda, dan menyimpan salinan cadangan di lokasi yang aman di luar lokasi melindungi Anda dari kehilangan data yang berpotensi bencana. Membuat cadangan adalah satu-satunya cara untuk melindungi data Anda.

  Dengan cadangan database yang valid, Anda dapat memulihkan data Anda dari banyak kegagalan, seperti:

  • Kegagalan media.

  • Kesalahan pengguna, misalnya, menghilangkan tabel secara tidak sengaja.

  • Kegagalan perangkat keras, misalnya, drive disk yang rusak atau hilangnya server secara permanen.

  • Bencana alam. Dengan menggunakan SQL Server Backup ke Azure Blob Storage, Anda dapat membuat cadangan off-site di wilayah yang berbeda dari lokasi on-premises Anda, untuk digunakan jika bencana alam memengaruhi lokasi on-premises Anda.

• Selain itu, cadangan database berguna untuk tujuan administratif rutin, seperti menyalin database dari satu server ke server lain, menyiapkan grup ketersediaan AlwaysOn atau pencerminan database, dan pengarsipan.

Glosarium istilah cadangan

Istilah	Definition
cadangan[kata kerja]	Proses membuat cadangan[kata benda] dengan menyalin catatan data dari database SQL Server atau catatan log dari log transaksinya.
cadangan[kata benda]	Salinan data yang dapat Anda gunakan untuk memulihkan dan mengembalikan data setelah terjadi kegagalan. Cadangan database juga dapat digunakan untuk memulihkan salinan database ke lokasi baru.
perangkat cadangan	Disk atau perangkat pita tempat cadangan SQL Server ditulis dan dari mana cadangan tersebut dapat dipulihkan. Cadangan SQL Server juga dapat ditulis ke Azure Blob Storage, dan format URL digunakan untuk menentukan tujuan dan nama file cadangan. Untuk informasi selengkapnya, lihat Pencadangan dan pemulihan SQL Server dengan Azure Blob Storage.
media cadangan	Satu atau beberapa pita atau berkas disk yang berisi satu atau beberapa salinan cadangan.
pencadangan data	Cadangan data dalam basis data lengkap (cadangan basis data), dalam basis data parsial (cadangan parsial), atau dalam sekumpulan berkas data atau kelompok berkas (cadangan berkas).
pencadangan database	Salinan cadangan dari database. Pencadangan database lengkap mewakili seluruh database pada saat pencadangan selesai. Cadangan diferensial basis data hanya berisi perubahan yang dibuat pada basis data sejak cadangan penuh basis data yang paling baru.
cadangan diferensial	Cadangan data yang didasarkan pada cadangan lengkap terbaru dari database lengkap atau parsial atau sekumpulan file data atau grup file (basis diferensial) dan yang hanya berisi data yang telah berubah sejak dasar tersebut.
pencadangan penuh	Cadangan data yang berisi semua data dalam database atau kumpulan grup file atau file tertentu, dan juga log yang cukup untuk memungkinkan pemulihan data tersebut.
pencadangan log	Cadangan log transaksi yang mencakup semua catatan log yang tidak dicadangkan dalam cadangan log sebelumnya (model pemulihan penuh).
sembuh	Untuk mengembalikan database ke status stabil dan konsisten.
Pemulihan	Suatu fase inisialisasi basis data atau restore dengan recovery yang membawa basis data ke keadaan yang konsisten secara transaksi.
model pemulihan	Properti database yang mengontrol pemeliharaan log transaksi pada database. Ada tiga model pemulihan: dasar, penuh, dan dicatat secara massal. Model pemulihan database menentukan persyaratan pencadangan dan pemulihannya.
Pulihkan	Proses multiphase yang menyalin semua data dan halaman log dari cadangan SQL Server tertentu ke database tertentu, lalu meneruskan semua transaksi yang dicatat dalam cadangan dengan menerapkan perubahan yang dicatat untuk membawa data ke depan tepat waktu.

Strategi pencadangan dan pemulihan

Anda harus menyesuaikan strategi backup dan restore ke lingkungan dan sumber daya yang tersedia. Pemulihan yang andal memerlukan strategi pencadangan dan pemulihan. Strategi yang dirancang dengan baik menyeimbangkan kebutuhan bisnis untuk ketersediaan data maksimal dan kehilangan data minimal dengan biaya pemeliharaan dan penyimpanan cadangan.

Strategi pencadangan dan pemulihan berisi bagian cadangan dan bagian pemulihan. Bagian backup mendefinisikan jenis dan frekuensi backup, jenis dan kecepatan perangkat keras yang dibutuhkan, cara menguji backup, serta di mana dan bagaimana menyimpan media backup (termasuk pertimbangan keamanan). Bagian pemulihan mendefinisikan siapa yang bertanggung jawab melakukan pemulihan, cara melakukan pemulihan untuk mencapai tujuan ketersediaan database dan kehilangan data minimum, serta cara menguji pemulihan.

Strategi backup dan restore yang efektif membutuhkan perencanaan, implementasi, dan pengujian yang cermat. Pengujian diperlukan. Anda tidak memiliki strategi cadangan sampai Anda berhasil memulihkan cadangan dalam setiap kombinasi yang termasuk dalam strategi pemulihan Anda dan menguji setiap database yang dipulihkan untuk konsistensi fisik. Pertimbangkan beberapa faktor, termasuk:

• Tujuan organisasi Anda mengenai database produksi Anda, terutama persyaratan untuk ketersediaan dan melindungi data dari kehilangan atau kerusakan.

• Sifat setiap database: ukurannya, pola penggunaannya, sifat kontennya, persyaratan untuk datanya, dan sebagainya.

• Batasan pada sumber daya, seperti perangkat keras, personel, ruang untuk menyimpan media cadangan, keamanan fisik media yang disimpan, dan sebagainya.

Rekomendasi praktik terbaik

Jangan berikan lebih banyak hak istimewa kepada akun yang melakukan operasi backup atau restore daripada yang diperlukan. Untuk informasi lebih lanjut, lihat cadangan dan pemulihan untuk detail izin spesifik. Enkripsi cadangan basis data dan, jika memungkinkan, kompresinya .

Gunakan ekstensi file yang konsisten untuk memudahkan identifikasi dan pengelolaan cadangan. SQL Server tidak memerlukan atau menegakkan ekstensi ini, tetapi konsistensi membantu dalam tugas operasional seperti mengonfigurasi pengecualian antivirus untuk file cadangan. Untuk informasi selengkapnya, lihat Mengonfigurasi perangkat lunak antivirus untuk bekerja dengan SQL Server.

• File cadangan database harus memiliki ekstensi tersebut .BAK .
• File cadangan log harus memiliki .TRN ekstensi .

Gunakan penyimpanan terpisah

Tempatkan cadangan database Anda di lokasi fisik terpisah atau di perangkat terpisah dari file database. Ketika drive fisik yang menyimpan database Anda gagal atau crash, pemulihan bergantung pada kemampuan Anda mengakses drive terpisah atau perangkat jarak jauh yang menyimpan cadangan. Anda dapat membuat beberapa volume logis atau partisi dari drive fisik yang sama. Tinjau dengan cermat partisi disk dan tata letak volume logis sebelum Anda memilih lokasi penyimpanan untuk cadangan.

Pilih model pemulihan yang sesuai

Operasi pencadangan dan pemulihan terjadi dalam konteks model pemulihan. Model pemulihan adalah properti database yang mengontrol bagaimana log transaksi dikelola. Oleh karena itu, model pemulihan database menentukan jenis skenario backup dan restore yang didukung database, serta ukuran backup log transaksinya. Biasanya, database menggunakan model pemulihan sederhana atau model pemulihan penuh. Anda dapat menambah model pemulihan penuh dengan beralih ke model pemulihan yang dicatat secara massal sebelum operasi massal. Untuk pengenalan model pemulihan ini dan pengaruhnya terhadap manajemen log transaksi, lihat log transaksi.

Pilihan terbaik model pemulihan basis data tergantung pada kebutuhan bisnis Anda. Untuk menghindari manajemen log transaksi dan menyederhanakan pencadangan dan pemulihan, gunakan model pemulihan sederhana. Untuk meminimalkan risiko kehilangan pekerjaan dengan konsekuensi bertambahnya overhead administratif, gunakan model pemulihan penuh. Untuk meminimalkan efek pada ukuran log selama operasi yang dicatat secara massal sambil tetap memungkinkan pemulihan operasi tersebut, gunakan model pemulihan yang dicatat secara massal. Untuk informasi tentang efek model pemulihan terhadap backup dan restore, lihat Backup overview (SQL Server).

Rancang strategi pencadangan Anda

Setelah Anda memilih model pemulihan yang memenuhi kebutuhan bisnis Anda untuk basis data tertentu, rencanakan dan terapkan strategi cadangan yang sesuai. Strategi backup terbaik bergantung pada beberapa faktor. Faktor-faktor berikut sangat penting:

• Berapa jam per hari aplikasi membutuhkan akses ke database?

  Jika ada periode off-peak yang dapat diprediksi, Anda harus menjadwalkan backup database penuh untuk periode tersebut.

• Seberapa sering perubahan dan pembaruan mungkin terjadi?

  Jika perubahan sering terjadi, pertimbangkan:

  • Di bawah model pemulihan sederhana, Anda dapat menjadwalkan cadangan berbeda antara cadangan database penuh. Cadangan diferensial hanya menangkap perubahan sejak pencadangan database lengkap terakhir.

  • Di bawah model pemulihan penuh, Anda dapat menjadwalkan pencadangan log secara sering. Menjadwalkan pencadangan diferensial antara pencadangan penuh dapat mengurangi waktu pemulihan dengan mengurangi jumlah cadangan log yang harus Anda pulihkan setelah memulihkan data.

• Apakah perubahan kemungkinan hanya terjadi di sebagian kecil basis data, atau sebagian besar?

  Untuk basis data besar di mana perubahan terkonsentrasi pada sebagian file atau grup file, cadangan parsial atau cadangan file penuh dapat berguna. Untuk informasi selengkapnya, lihat Pencadangan Parsial (SQL Server) dan Pencadangan File Lengkap (SQL Server).

• Berapa banyak ruang disk yang dibutuhkan untuk backup database penuh?

• Seberapa jauh di masa lalu apakah bisnis Anda perlu mempertahankan cadangan?

  Pastikan Anda memiliki jadwal cadangan yang tepat sesuai dengan kebutuhan aplikasi dan kebutuhan bisnis. Seiring bertambahnya usia cadangan, risiko kehilangan data meningkat kecuali Anda memiliki cara untuk meregenerasi semua data hingga titik kegagalan. Sebelum Anda membuang cadangan lama karena keterbatasan penyimpanan, pertimbangkan apakah Anda membutuhkan pemulihan sejauh itu di masa lalu.

Memperkirakan ukuran cadangan database lengkap

Sebelum Anda menerapkan strategi backup dan restore, perkirakan berapa banyak ruang disk yang digunakan oleh backup database penuh. Operasi pencadangan menyalin data dalam database ke file cadangan. Cadangan hanya berisi data aktual dalam database, bukan ruang yang tidak terpakai. Oleh karena itu, cadangan biasanya lebih kecil dari database itu sendiri. Untuk memperkirakan ukuran cadangan database penuh, gunakan sp_spaceused prosedur tersimpan sistem. Untuk informasi selengkapnya, lihat sp_spaceused.

Menjadwalkan pencadangan

Operasi backup memiliki efek minimal pada transaksi yang berjalan, sehingga Anda dapat menjalankan backup selama operasi reguler. Anda dapat melakukan pencadangan SQL Server dengan efek minimal pada beban kerja produksi.

Catatan

Untuk informasi tentang pembatasan konkurensi selama pencadangan, lihat Gambaran Umum Pencadangan (SQL Server).

Setelah Anda memutuskan jenis cadangan yang dibutuhkan dan seberapa sering melakukan setiap tipe, jadwalkan cadangan rutin sebagai bagian dari rencana pemeliharaan basis data. Untuk informasi tentang rencana pemeliharaan dan cara membuatnya untuk pencadangan database dan pencadangan log, lihat Menggunakan Wizard Rencana Pemeliharaan.

Menguji cadangan Anda

Anda tidak memiliki strategi pemulihan sampai Anda menguji cadangan Anda. Uji strategi backup Anda secara menyeluruh untuk setiap database dengan mengembalikan salinan database ke sistem pengujian. Anda harus menguji pemulihan setiap jenis cadangan yang ingin Anda gunakan. Setelah Anda memulihkan cadangan, jalankan DBCC CHECKDB terhadap database untuk memastikan media cadangan tidak rusak.

Memverifikasi stabilitas dan konsistensi media

Gunakan opsi verifikasi yang disediakan oleh utilitas cadangan (BACKUPperintah T-SQL, Rencana Pemeliharaan SQL Server, perangkat lunak atau solusi cadangan Anda, dan sebagainya). Sebagai contoh, lihat RESTORE Pernyataan - VERIFYONLY.

Gunakan fitur canggih seperti BACKUP CHECKSUM mendeteksi masalah pada media cadangan itu sendiri. Untuk informasi selengkapnya, lihat Kemungkinan Kesalahan Media Selama Pencadangan dan Pemulihan (SQL Server).

Strategi pencadangan/pemulihan dokumen

Dokumentasikan prosedur backup dan restore Anda, dan simpan salinan dokumentasi tersebut dalam run book Anda.

Anda juga harus memelihara manual operasi untuk setiap basis data. Manual operasi ini harus mendokumentasikan lokasi cadangan, nama perangkat cadangan (jika ada), dan waktu yang dibutuhkan untuk memulihkan cadangan uji.

Risiko keamanan memulihkan cadangan dari sumber yang tidak tepercaya

Bagian ini menguraikan risiko keamanan yang terkait dengan pemulihan cadangan dari sumber yang tidak tepercaya ke lingkungan SQL Server apa pun, termasuk lokal, Azure SQL Managed Instance, SQL Server di Azure Virtual Machines (VM) dan lingkungan lainnya.

Mengapa ini penting

Memulihkan file cadangan SQL (.bak) menimbulkan potensi risiko jika cadangan berasal dari sumber yang tidak tepercaya. Risiko keamanan diperburuk lebih lanjut ketika lingkungan SQL Server memiliki beberapa instans, karena memperkuat area ancaman. Meskipun cadangan yang tetap berada dalam batas tepercaya tidak menimbulkan masalah keamanan, memulihkan cadangan berbahaya dapat membahayakan keamanan seluruh lingkungan.

File berbahaya .bak dapat:

• Ambil alih seluruh instans SQL Server.
• Tingkatkan hak istimewa dan dapatkan akses tidak sah ke host atau komputer virtual yang mendasarinya.

Serangan ini terjadi sebelum skrip validasi atau pemeriksaan keamanan dapat dijalankan, yang membuatnya sangat berbahaya. Memulihkan cadangan yang tidak tepercaya setara dengan menjalankan aplikasi yang tidak tepercaya pada server penting atau komputer virtual, dan memperkenalkan eksekusi kode arbitrer ke lingkungan Anda.

Praktik terbaik

Ikuti praktik terbaik keamanan cadangan ini untuk mengurangi ancaman terhadap lingkungan SQL Server Anda:

• Perlakukan pemulihan cadangan sebagai operasi berisiko tinggi.
• Kurangi area layanan ancaman dengan menggunakan instans terisolasi.
• Hanya izinkan cadangan tepercaya: jangan pernah memulihkan cadangan dari sumber yang tidak diketahui atau eksternal.
• Hanya izinkan cadangan yang tetap berada dalam batas tepercaya: pastikan cadangan berasal dari dalam batas tepercaya.
• Jangan melewati kontrol keamanan untuk kenyamanan.
• Aktifkan audit tingkat server untuk mencatat peristiwa pencadangan dan pemulihan serta mengurangi penghindaran audit.

Memantau kemajuan dengan XEvent

Operasi backup dan restore dapat memakan waktu lama karena ukuran database dan kompleksitas operasi yang terlibat. Ketika masalah terjadi pada salah satu dari kedua operasi tersebut, gunakan extended event backup_restore_progress_trace untuk memantau progres secara real time. Untuk informasi selengkapnya tentang peristiwa yang diperluas, lihat Gambaran umum Kejadian yang Diperluas.

Peringatan

Event yang backup_restore_progress_trace diperpanjang dapat menyebabkan masalah performa dan menghabiskan banyak ruang disk. Gunakan untuk waktu singkat, berhati-hati, dan uji secara menyeluruh sebelum digunakan dalam produksi.

-- Create the backup_restore_progress_trace extended event session
CREATE EVENT SESSION [BackupRestoreTrace] ON SERVER
ADD EVENT sqlserver.backup_restore_progress_trace
ADD TARGET package0.event_file (SET filename = N'BackupRestoreTrace')
WITH
(
    MAX_MEMORY = 4096 KB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 5 SECONDS,
    MAX_EVENT_SIZE = 0 KB,
    MEMORY_PARTITION_MODE = NONE,
    TRACK_CAUSALITY = OFF,
    STARTUP_STATE = OFF
);
GO

-- Start the event session
ALTER EVENT SESSION [BackupRestoreTrace] ON SERVER
STATE = START;
GO

-- Stop the event session
ALTER EVENT SESSION [BackupRestoreTrace] ON SERVER
STATE = STOP;
GO

Contoh keluaran dari Extended Event

Pelajari lebih lanjut tentang tugas pencadangan

• Membuat rencana pemeliharaan
• Membuat pekerjaan SQL Server Agent di SQL Server Management Studio
• Mengonfigurasi jadwal untuk pekerjaan SQL Server Agent

Bekerja dengan perangkat cadangan dan media cadangan

• Menentukan perangkat cadangan logis untuk file disk (SQL Server)
• Menentukan perangkat cadangan logis untuk tape drive (SQL Server)
• Tentukan tujuan pencadangan disk atau pita (SQL Server)
• Menghapus perangkat cadangan (SQL Server)
• Mengatur tanggal kedaluwarsa pada cadangan (SQL Server)
• Menampilkan konten pita atau file cadangan (SQL Server)
• Menampilkan data dan file log dalam kumpulan cadangan (SQL Server)
• Menampilkan properti dan konten perangkat cadangan logis (SQL Server)
• Memulihkan cadangan dari perangkat (SQL Server)

Membuat cadangan

Untuk cadangan parsial atau cadangan khusus salinan, gunakan pernyataan Transact-SQL BACKUP dengan opsi PARTIAL atau COPY_ONLY, masing-masing.

Menggunakan SSMS

• Membuat cadangan database lengkap
• Mencadangkan log transaksi
• Cadangkan file dan grup file
• Membuat cadangan database diferensial (SQL Server)

Menggunakan T-SQL

• Gunakan Resource Governor untuk membatasi penggunaan CPU melalui kompresi cadangan
• Cadangkan log transaksi saat basis data rusak (SQL Server)
• Mengaktifkan atau menonaktifkan checksum cadangan selama pencadangan atau pemulihan (SQL Server)
• Tentukan pencadangan atau pemulihan untuk melanjutkan atau berhenti setelah kesalahan

Memulihkan cadangan data

Menggunakan SSMS

• Memulihkan cadangan database menggunakan SQL Server Management Studio (SSMS)
• Memulihkan database ke lokasi baru (SQL Server)
• Memulihkan cadangan database diferensial (SQL Server)
• Pulihkan file dan filegroup (SQL Server)

Menggunakan T-SQL

• Pulihkan cadangan database di bawah model pemulihan sederhana
• Memulihkan database ke titik kegagalan - pemulihan penuh
• Pulihkan file dan filegroup di atas file yang sudah ada (SQL Server)
• Pulihkan file ke lokasi baru (SQL Server)
• Pulihkan database master

Memulihkan log transaksi (model pemulihan penuh)

Menggunakan SSMS

• Memulihkan database ke transaksi yang ditandai (SQL Server Management Studio)
• Memulihkan cadangan log transaksi (SQL Server)
• Pulihkan database SQL Server ke titik waktu tertentu (model pemulihan penuh)

Menggunakan T-SQL

• Pulihkan database SQL Server ke titik waktu tertentu (model pemulihan penuh)
• Memulai ulang operasi pemulihan yang terganggu
• Pulihkan database tanpa memulihkan data

Konten terkait

• Gambaran umum pencadangan (SQL Server)
• Gambaran umum pemulihan data dan pemulihan (SQL Server)
• BACKUP (Transact-SQL)
• RESTORE Pernyataan (Transact-SQL)
• Pencadangan dan Pemulihan Basis Data Analysis Services
• Cadangkan dan pulihkan katalog dan indeks teks penuh
• Cadangkan dan pulihkan database yang telah direplikasi
• Log transaksi
• Model pemulihan (SQL Server)
• Set media, keluarga media, dan set cadangan (SQL Server)