Mencadangkan dan memulihkan database SQL Server

Berlaku untuk:SQL Server

Artikel ini menjelaskan manfaat mencadangkan database SQL Server, menjelaskan istilah pencadangan dan pemulihan dasar, dan memperkenalkan strategi pencadangan dan pemulihan untuk SQL Server dan pertimbangan keamanan untuk pencadangan dan pemulihan SQL Server.

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

Komponen pencadangan dan pemulihan SQL Server menyediakan perlindungan penting untuk melindungi data penting yang disimpan dalam database SQL Server Anda. Untuk meminimalkan risiko kehilangan data bencana, Anda perlu mencadangkan database Anda untuk mempertahankan modifikasi pada data Anda secara teratur. Strategi pencadangan dan pemulihan yang direncanakan dengan baik membantu melindungi database dari kehilangan data yang disebabkan oleh berbagai kegagalan. Uji strategi Anda dengan memulihkan sekumpulan cadangan lalu memulihkan database Anda untuk mempersiapkan Anda merespons bencana secara efektif.

Selain penyimpanan lokal untuk menyimpan cadangan, SQL Server juga mendukung pencadangan ke dan pemulihan dari Azure Blob Storage. Untuk informasi selengkapnya, lihat Pencadangan dan pemulihan SQL Server dengan Microsoft 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, mendukung grup ketersediaan AlwaysOn, retensi jangka panjang, pemulihan point-in-time, dan manajemen dan pemantauan pusat. Untuk informasi selengkapnya, lihat Tentang pencadangan SQL Server di Azure VM.

Mengapa mencadangkan?

• Mencadangkan database SQL Server Anda, menjalankan prosedur pemulihan pengujian pada cadangan Anda, dan menyimpan salinan cadangan di lokasi di luar situs yang aman melindungi Anda dari kehilangan data yang berpotensi besar. Mencadangkan 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 di luar situs di wilayah yang berbeda dari lokasi lokal Anda, untuk digunakan jika terjadi bencana alam yang memengaruhi lokasi lokal 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

cadangkan [kata kerja]
Proses pembuatan cadangan [kata benda] dengan menyalin rekaman data dari database SQL Server atau catatan log dari log transaksinya.

backup [noun]
Salinan data yang dapat Anda gunakan untuk memulihkan dan memulihkan data setelah 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 Microsoft Azure Blob Storage.

media cadangan
Satu atau beberapa pita atau file disk tempat satu atau beberapa cadangan telah ditulis.

pencadangan data
Cadangan data dalam database lengkap (cadangan database), database parsial (cadangan parsial), atau sekumpulan file data atau grup file (cadangan file).

pencadangan database
Cadangan database. Pencadangan database lengkap mewakili seluruh database pada saat pencadangan selesai. Cadangan database diferensial hanya berisi perubahan yang dilakukan pada database karena pencadangan database lengkap terbarunya.

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
Fase startup database atau pemulihan dengan pemulihan yang membawa database ke status konsisten transaksi.

model pemulihan
Properti database yang mengontrol pemeliharaan log transaksi pada database. Ada tiga model pemulihan: sederhana, penuh, dan dicatat secara massal. Model pemulihan database menentukan persyaratan pencadangan dan pemulihannya.

Mengembalikan
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

Mencadangkan dan memulihkan data harus disesuaikan dengan lingkungan tertentu dan harus bekerja dengan sumber daya yang tersedia. Oleh karena itu, penggunaan cadangan dan pemulihan yang andal untuk pemulihan memerlukan strategi pencadangan dan pemulihan. Strategi pencadangan dan pemulihan yang dirancang dengan baik menyeimbangkan persyaratan bisnis untuk ketersediaan data maksimum dan kehilangan data minimum sambil mempertimbangkan biaya pemeliharaan dan penyimpanan cadangan.

Strategi pencadangan dan pemulihan berisi bagian cadangan dan bagian pemulihan. Bagian cadangan dari strategi mendefinisikan jenis dan frekuensi cadangan, sifat dan kecepatan perangkat keras yang mereka butuhkan, bagaimana cadangan harus diuji, dan di mana dan bagaimana media cadangan akan disimpan (termasuk pertimbangan keamanan). Bagian pemulihan dari strategi menentukan siapa yang bertanggung jawab untuk melakukan pemulihan, bagaimana pemulihan harus dilakukan untuk memenuhi tujuan Anda untuk ketersediaan database dan meminimalkan kehilangan data, dan bagaimana pemulihan diuji.

Merancang strategi pencadangan dan pemulihan yang efektif memerlukan perencanaan, implementasi, dan pengujian yang cermat. Pengujian diperlukan: Anda tidak memiliki strategi pencadangan sampai Anda berhasil memulihkan cadangan dalam semua kombinasi yang disertakan dalam strategi pemulihan Anda dan telah menguji database yang dipulihkan untuk konsistensi fisik. Anda harus mempertimbangkan berbagai faktor. Ini 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

Akun yang melakukan operasi pencadangan atau pemulihan tidak boleh diberikan lebih banyak hak istimewa daripada yang diperlukan. Tinjau pencadangan dan pemulihan untuk detail izin tertentu. Disarankan agar cadangan dienkripsi dan, jika memungkinkan, dikompresi.

Untuk memastikan keamanan, file cadangan harus memiliki ekstensi yang mengikuti konvensi yang tepat:

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

Menggunakan Penyimpanan Terpisah

Penting

Pastikan Anda menempatkan cadangan database Anda di lokasi fisik atau perangkat terpisah dari file database. Saat drive fisik Anda yang menyimpan kerusakan atau crash database Anda, pemulihan tergantung pada kemampuan untuk mengakses drive terpisah atau perangkat jarak jauh yang menyimpan cadangan untuk melakukan pemulihan. Perlu diingat bahwa Anda dapat membuat beberapa volume logis atau partisi dari drive disk fisik yang sama. Pelajari partisi disk dan tata letak volume logis dengan cermat sebelum 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. Dengan demikian, model pemulihan database menentukan jenis skenario pencadangan dan pemulihan apa yang didukung untuk database, dan berapa ukuran cadangan log transaksi. 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 model pemulihan terbaik untuk database tergantung pada kebutuhan bisnis Anda. Untuk menghindari manajemen log transaksi dan menyederhanakan pencadangan dan pemulihan, gunakan model pemulihan sederhana. Untuk meminimalkan paparan kehilangan kerja dengan biaya overhead administratif, gunakan model pemulihan penuh. Untuk meminimalkan dampak pada ukuran log selama operasi yang dicatat secara massal sementara pada saat yang sama memungkinkan pemulihan operasi tersebut, gunakan model pemulihan yang dicatat secara massal. Untuk informasi tentang efek model pemulihan pada pencadangan dan pemulihan, lihat Gambaran umum pencadangan.

Merancang strategi pencadangan Anda

Setelah Anda memilih model pemulihan yang memenuhi persyaratan bisnis Anda untuk database tertentu, Anda harus merencanakan dan menerapkan strategi pencadangan yang sesuai. Strategi pencadangan yang optimal tergantung pada berbagai faktor, di mana berikut ini sangat signifikan:

• Berapa jam sehari aplikasi harus mengakses database?

  Jika ada periode di luar puncak yang dapat diprediksi, kami sarankan Anda menjadwalkan pencadangan database penuh untuk periode tersebut.

• Seberapa sering perubahan dan pembaruan mungkin terjadi?

  Jika perubahan sering terjadi, pertimbangkan hal berikut:

  • Di bawah model pemulihan sederhana, pertimbangkan untuk menjadwalkan pencadangan diferensial antara pencadangan database lengkap. Cadangan diferensial hanya menangkap perubahan sejak pencadangan database lengkap terakhir.

  • Di bawah model pemulihan penuh, Anda harus menjadwalkan pencadangan log yang 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 bagian kecil database atau di sebagian besar database?

  Untuk database besar di mana perubahan dikonsentrasikan di bagian file atau grup file, cadangan parsial dan atau pencadangan file lengkap dapat berguna. Untuk informasi selengkapnya, lihat Pencadangan parsial (SQL Server) dan Pencadangan file lengkap (SQL Server).

• Berapa banyak ruang disk yang diperlukan pencadangan database lengkap?

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

  Pastikan Anda memiliki jadwal pencadangan yang tepat yang ditetapkan sesuai dengan kebutuhan aplikasi dan persyaratan bisnis. Ketika cadangan menjadi tua, risiko kehilangan data lebih tinggi kecuali Anda memiliki cara untuk meregenerasi semua data sampai titik kegagalan. Sebelum Anda memilih untuk membuang cadangan lama karena keterbatasan sumber daya penyimpanan, pertimbangkan apakah pemulihan diperlukan sejauh itu di masa lalu.

Memperkirakan ukuran cadangan database lengkap

Sebelum menerapkan strategi pencadangan dan pemulihan, Anda harus memperkirakan berapa banyak ruang disk yang akan digunakan cadangan database lengkap. Operasi pencadangan menyalin data dalam database ke file cadangan. Cadangan hanya berisi data aktual dalam database dan bukan ruang yang tidak digunakan. Oleh karena itu, cadangan biasanya lebih kecil dari database itu sendiri. Anda dapat memperkirakan ukuran cadangan database lengkap dengan menggunakan prosedur tersimpan sp_spaceused sistem. Untuk informasi selengkapnya, lihat sp_spaceused (Transact-SQL).

Menjadwalkan pencadangan

Melakukan operasi pencadangan memiliki efek minimal pada transaksi yang sedang berjalan; oleh karena itu, Anda dapat menjalankan operasi pencadangan selama operasi reguler. Anda dapat melakukan pencadangan SQL Server dengan efek minimal pada beban kerja produksi.

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

Setelah Anda memutuskan jenis cadangan apa yang Anda butuhkan dan seberapa sering Anda harus melakukan setiap jenis, kami sarankan Anda menjadwalkan cadangan reguler sebagai bagian dari rencana pemeliharaan database untuk database. Untuk informasi tentang rencana pemeliharaan dan cara membuatnya untuk pencadangan database dan cadangan log, lihat Menggunakan Wizard Rencana Pemeliharaan.

Menguji cadangan Anda

Anda tidak memiliki strategi pemulihan sampai Anda menguji cadangan Anda. Sangat penting untuk menguji strategi pencadangan Anda secara menyeluruh untuk setiap database Anda dengan memulihkan salinan database ke sistem pengujian. Anda harus menguji pemulihan setiap jenis cadangan yang ingin Anda gunakan. Kami juga menyarankan agar setelah Anda memulihkan cadangan, Anda melakukan pemeriksaan konsistensi database melalui DBCC CHECKDB database untuk memvalidasi media cadangan tidak rusak.

Memverifikasi stabilitas dan konsistensi media

Gunakan opsi verifikasi yang disediakan oleh utilitas cadangan (BACKUP perintah T-SQL, Rencana Pemeliharaan SQL Server, perangkat lunak atau solusi cadangan Anda, dll.). Misalnya, lihat pernyataan RESTORE - VERIFYONLY.

Gunakan fitur lanjutan seperti BACKUP CHECKSUM untuk mendeteksi masalah dengan media cadangan itu sendiri. Untuk informasi selengkapnya, lihat Kemungkinan kesalahan media selama pencadangan dan pemulihan (SQL Server)

Strategi pencadangan/pemulihan dokumen

Kami menyarankan agar Anda mendokumentasikan prosedur pencadangan dan pemulihan Anda dan menyimpan salinan dokumentasi dalam buku eksekusi Anda. Kami juga menyarankan agar Anda mempertahankan manual operasi untuk setiap database. Manual operasi ini harus mendokumentasikan lokasi cadangan, nama perangkat cadangan (jika ada), dan jumlah waktu yang diperlukan untuk memulihkan cadangan pengujian.

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 pencadangan dan pemulihan dapat memakan waktu yang cukup lama karena ukuran database dan kompleksitas operasi yang terlibat. Saat masalah muncul dengan salah satu operasi, Anda dapat menggunakan peristiwa yang backup_restore_progress_trace diperluas untuk memantau kemajuan secara langsung. Untuk informasi selengkapnya tentang peristiwa yang diperluas, lihat Gambaran umum Kejadian yang Diperluas.

Peringatan

Menggunakan peristiwa yang backup_restore_progress_trace diperluas dapat menyebabkan masalah performa dan mengonsumsi sejumlah besar ruang disk. Gunakan untuk waktu yang singkat, berhati-hatilah, dan uji secara menyeluruh sebelum menerapkan 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

Sampel output dari peristiwa yang diperluas

Selengkapnya 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

Catatan

Untuk cadangan parsial atau salin saja, Anda harus menggunakan pernyataan Transact-SQL BACKUP dengan PARTIAL opsi atau COPY_ONLY .

Menggunakan SSMS

• Membuat Pencadangan Database Lengkap (SQL Server)

• Mencadangkan log transaksi

• Mencadangkan File dan Grup File

• Membuat Cadangan Database Diferensial (SQL Server)

Menggunakan T-SQL

• Gunakan Resource Governor untuk Membatasi Penggunaan CPU dengan Kompresi Cadangan (Transact-SQL)

• Mencadangkan Log Transaksi Saat Database 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 Pencadangan Database Menggunakan SQL Server Management Directory

• Memulihkan database ke lokasi baru (SQL Server)

• Memulihkan cadangan database diferensial (SQL Server)

• Pulihkan File dan Grup File (SQL Server)

Menggunakan T-SQL

• Memulihkan cadangan database di bawah model pemulihan sederhana (Transact-SQL)

• Pulihkan Database ke Titik Kegagalan - Pemulihan Penuh

• Pulihkan File dan Grup File melalui File yang Ada (SQL Server)

• Pulihkan File ke Lokasi Baru (SQL Server)

• Memulihkan database master (Transact-SQL)

Memulihkan log transaksi (model pemulihan penuh)

Menggunakan SSMS

• Memulihkan database ke transaksi yang ditandai (SQL Server Management Studio)

• Memulihkan Pencadangan Log Transaksi (SQL Server)

• Memulihkan Database SQL Server ke Titik Waktu (Model Pemulihan Penuh)

Menggunakan T-SQL

• Memulihkan Database SQL Server ke Titik Waktu (Model Pemulihan Penuh)

• Mulai ulang operasi pemulihan yang terganggu (Transact-SQL)

• Memulihkan database tanpa memulihkan data (Transact-SQL)

Konten terkait

• Gambaran umum pencadangan (SQL Server)
• Gambaran umum pemulihan dan pemulihan (SQL Server)
• BACKUP (Transact-SQL)
• Pernyataan RESTORE (Transact-SQL)
• Pencadangan dan Pemulihan Database Analysis Services
• Cadangkan dan Pulihkan Katalog dan Indeks Teks Lengkap
• Mencadangkan dan Memulihkan Database yang Direplikasi
• Log transaksi
• Model pemulihan (SQL Server)
• Set media, keluarga media, dan set cadangan (SQL Server)