Mencadangkan dan Memulihkan Database SQL Server

Berlaku untuk:SQL Server

Artikel ini menjelaskan manfaat mencadangkan database SQL Server, 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 Cadangan 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 Azure Backup untuk 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 mencatat catatan log dari log transaksinya.

backup [noun]
Salinan data yang dapat digunakan 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 menyertakan semua catatan log yang tidak dicadangkan dalam cadangan log sebelumnya. (model pemulihan penuh)

Memulihkan
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 multi-fase yang menyalin semua data dan halaman log dari cadangan SQL Server tertentu ke database tertentu, lalu meluncurkan 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 diperlukan untuk mereka, bagaimana cadangan akan 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 ketersediaan dan perlindungan 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. Model pemulihan penuh dapat bertambah 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 (SQL Server)

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 (SQL Server).

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 sistem sp_spaceused . Untuk informasi selengkapnya, lihat sp_spaceused (Transact-SQL).

Menjadwalkan pencadangan

Melakukan operasi pencadangan memiliki efek minimal pada transaksi yang sedang berjalan; oleh karena itu, operasi pencadangan dapat dijalankan 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.

Uji cadangan Anda!

Anda tidak memiliki strategi pemulihan hingga Anda menguji cadangan Anda. Sangat penting untuk menguji strategi cadangan 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. Disarankan juga bahwa setelah Anda memulihkan cadangan, Anda melakukan pemeriksaan konsistensi database melalui DBCC CHECKDB database untuk memvalidasi media cadangan tidak rusak.

Verifikasi Stabilitas dan Konsistensi Media

Gunakan opsi verifikasi yang disediakan oleh utilitas cadangan (perintah BACKUP T-SQL, SQL Server Maintenance Plans, perangkat lunak atau solusi cadangan Anda, dll.). Misalnya, lihat [RESTORE VERIFYONLY] (.. . /t-sql/statements/restore-statements-verifyonly-transact-sql.md) 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.

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 backup_restore_progress_trace diperluas untuk memantau kemajuan secara langsung. Untuk informasi selengkapnya tentang peristiwa yang diperluas, lihat peristiwa yang diperluas.

Peringatan

Menggunakan peristiwa 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 esssion
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

• Buat Pekerjaan

• Menjadwalkan Pekerjaan

Bekerja dengan perangkat cadangan dan media cadangan

• Menentukan Perangkat Cadangan Logis untuk File Disk (SQL Server)

• Menentukan Perangkat Pencadangan Logis untuk Tape Drive (SQL Server)

• Tentukan Disk atau Pita Sebagai Tujuan Pencadangan (SQL Server)

• Menghapus Perangkat Cadangan (SQL Server)

• Atur Tanggal Kedaluwarsa pada Cadangan (SQL Server)

• Menampilkan Isi 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 pencadangan parsial atau salin saja, Anda harus menggunakan pernyataan PENCADANGAN Transact-SQLdengan opsi PARSIAL atau COPY_ONLY.

Menggunakan SQL Server Management Studio

• Membuat Pencadangan Database Lengkap (SQL Server)

• Mencadangkan Log Transaksi (SQL Server)

• Mencadangkan File dan Grup File (SQL Server)

• 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 Apakah Operasi Pencadangan atau Pemulihan Berlanjut atau Berhenti Setelah Mengalami Kesalahan (SQL Server)

Memulihkan cadangan data

Menggunakan SQL Server Management Studio

• 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)

• Memulihkan Database ke Titik Kegagalan Di bawah Model Pemulihan Penuh (Transact-SQL)

• 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 SQL Server Management Studio

• 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 Terganggu (Transact-SQL)

• Memulihkan Database Tanpa Memulihkan Data (Transact-SQL)

Informasi dan sumber daya selengkapnya

Gambaran Umum Pencadangan (SQL Server)
Gambaran Umum Pengembalian dan Pemulihan (SQL Server)
BACKUP (Transact-SQL)
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 (SQL Server)
Model Pemulihan (SQL Server)
Set Media, Keluarga Media, dan Kumpulan Cadangan (SQL Server)