Membuat cadangan rekam jepret Transact-SQL
Berlaku untuk:
SQL Server 2022 (16.x)
Artikel ini menjelaskan apa, mengapa, dan cara menggunakan cadangan rekam jepret Transact-SQL. Pencadangan rekam jepret Transact-SQL baru di SQL Server 2022 (16.x).
Database semakin besar dan lebih besar setiap hari. Secara tradisional, cadangan SQL Server adalah cadangan streaming. Pencadangan streaming tergantung pada ukuran database. Operasi pencadangan menggunakan sumber daya (CPU, memori, I/O, jaringan) yang memengaruhi throughput beban kerja OLTP bersamaan selama durasi pencadangan. Salah satu cara untuk membuat performa cadangan konstan, daripada bergantung pada ukuran data, adalah dengan melakukan pencadangan rekam jepret menggunakan mekanisme yang disediakan oleh perangkat keras atau layanan penyimpanan yang mendasar.
Karena cadangan itu sendiri terjadi di tingkat perangkat keras, ini bukan solusi SQL Server murni. SQL Server harus terlebih dahulu menyiapkan data dan file log untuk rekam jepret, sehingga file dijamin berada dalam status yang nantinya dapat dipulihkan. Setelah ini selesai, I/O dibekukan di SQL Server, dan kontrol diserahkan ke aplikasi cadangan untuk menyelesaikan rekam jepret. Setelah rekam jepret berhasil diselesaikan, aplikasi harus mengembalikan kontrol kembali ke SQL Server tempat I/O kemudian dilanjutkan. Karena kita harus membekukan I/O selama durasi operasi rekam jepret, sangat penting bahwa rekam jepret terjadi dengan cepat, sehingga beban kerja di server tidak terganggu untuk jangka waktu yang lama. Di masa lalu, pengguna telah mengandalkan solusi pihak ketiga yang dibangun di atas layanan Penulis SQL untuk menyelesaikan pencadangan rekam jepret. Layanan Penulis SQL tergantung pada Windows VSS (Layanan Bayangan Volume) bersama dengan SQL Server VDI (Antarmuka Perangkat Virtual) untuk melakukan orkestrasi antara SQL Server dan rekam jepret tingkat disk. Klien cadangan berdasarkan layanan SQL Writer cenderung kompleks, dan mereka hanya bekerja di Windows. Dengan cadangan rekam jepret T-SQL, sisi SQL Server dari orkestrasi dapat ditangani dengan serangkaian perintah T-SQL. Ini memungkinkan pengguna untuk membuat aplikasi cadangan kecil mereka sendiri yang dapat berjalan di Windows atau Linux, atau bahkan solusi skrip jika penyimpanan yang mendasarinya mendukung antarmuka pembuatan skrip untuk memulai rekam jepret.
Berikut adalah contoh skrip PowerShell yang menunjukkan solusi end-to-end untuk mencadangkan dan memulihkan database di Azure SQL IaaS Virtual Machine menggunakan kemampuan pencadangan rekam jepret T-SQL yang diperkenalkan di SQL Server 2022 (16.x) (dan yang lebih tinggi).
Alur kerja
Sintaks cadangan rekam jepret T-SQL memisahkan mekanisme rekam jepret yang bergantung pada vendor dari operasi penangguhan dan pencadangan. Dengan sintaks ini, Anda dapat:
- Bekukan database dengan perintah ALTER – memberikan kesempatan bagi Anda untuk melakukan rekam jepret penyimpanan yang mendasar. Setelah itu, Anda dapat mencairkan database dan merekam rekam jepret dengan perintah BACKUP.
- Lakukan rekam jepret beberapa database secara bersamaan dengan perintah BACKUP GROUP dan BACKUP SERVER baru. Dengan opsi ini, rekam jepret dapat dilakukan pada granularitas rekam jepret penyimpanan yang mendasar, menghilangkan kebutuhan untuk melakukan rekam jepret dari disk yang sama beberapa kali.
- Lakukan pencadangan PENUH dan COPY_ONLY pencadangan PENUH. Cadangan ini juga dicatat dalam
msdb. - Lakukan pemulihan point-in-time menggunakan cadangan log yang diambil dengan pendekatan streaming normal setelah pencadangan PENUH rekam jepret. Pencadangan diferensial streaming juga didukung jika diinginkan.
Catatan
Bitmap diferensial dibersihkan selama tahap pertama saat menangguhkan database dengan perintah ALTER. Jika pengguna memutuskan untuk mencairkan database tanpa melakukan pencadangan karena rekam jepret gagal atau karena alasan lain, bitmap diferensial akan tidak valid. Jadi, setiap cadangan diferensial berikutnya akan lebih intensif I/O karena mereka harus memindai seluruh database untuk melakukan pencadangan diferensial. Bitmap diferensial akan menjadi valid lagi setelah pencadangan rekam jepret berhasil.
Diagram berikut mengilustrasikan alur kerja tingkat tinggi cadangan rekam jepret T-SQL:
Langkah rekam jepret tengah mengharuskan Anda untuk memulai rekam jepret pada penyimpanan yang mendasar. Diagram berikut menunjukkan contoh cara kerja skrip cadangan dengan SQL Server untuk menyelesaikan proses pencadangan rekam jepret:
Demikian pula, skrip pemulihan mungkin berfungsi sebagai berikut:
Batasan
Jumlah maksimum database yang dapat Anda cadangkan dengan fitur ini adalah 64. Jika ada lebih dari 64 database di server, Anda akan melihat kesalahan berikut:
Error message:
Msg 925, Level 19, State 1, Line 4
Maximum number of databases used for each query has been exceeded. The maximum allowed is 64.
Contoh
Bagian berikut menunjukkan perintah T-SQL yang berbeda yang digunakan untuk melakukan pencadangan rekam jepret ke disk. Saat cadangan rekam jepret ditulis ke disk, hanya metadata yang terhubung ke cadangan rekam jepret yang ditulis ke file. Output tidak berisi konten database apa pun kecuali untuk header dan konten file. File shell yang dibuat sebagai bagian dari melakukan pencadangan rekam jepret harus digunakan dengan URI rekam jepret aktual untuk membuat cadangan lengkap. PEMULIHAN database dari file ini mengharuskan pengguna menyalin file database dari URI rekam jepret ke titik pemasangan sebelum mengeluarkan perintah RESTORE. Pengguna dapat menjalankan semua perintah T-SQL tradisional seperti RESTORE HEADERONLY, RESTORE FILELISTONLY pada file metadata cadangan rekam jepret ini bersama dengan RESTORE DATABASE. Sintaks mendukung penulisan metadata cadangan rekam jepret ke DISK atau URL. Kumpulan cadangan rekam jepret juga dapat ditambahkan seperti set cadangan streaming ke dalam satu file.
Catatan
Untuk cadangan ke URL, blob blok lebih disukai meskipun blob halaman didukung untuk SQL Server di Windows. Untuk SQL Server di Linux dan kontainer, hanya blob blok yang didukung.
Menangguhkan database pengguna tunggal untuk pencadangan rekam jepret dan merekam cadangan database
ALTER DATABASE testdb1
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON;
BACKUP DATABASE testdb1
TO DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FORMAT;
Menangguhkan beberapa database pengguna untuk pencadangan rekam jepret
Jika beberapa database pada disk dasar yang sama, Anda dapat menangguhkan beberapa database dengan perintah berikut.
ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON
(GROUP = (testdb1, testdb2));
BACKUP GROUP testdb1, testdb2
TO DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FORMAT;
Menangguhkan semua database pengguna di server untuk pencadangan rekam jepret
Jika semua database pengguna di server perlu ditangguhkan, gunakan perintah berikut.
ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON;
BACKUP SERVER
TO DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FORMAT;
Catatan
Tidak ada perintah ini yang mendukung penangguhan database sistem: master, , modeldan msdb untuk pencadangan rekam jepret.
Menangguhkan beberapa database pengguna dengan satu perintah
Rekam rekam jepret semua database pengguna di server ke dalam satu set cadangan:
ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON
(GROUP = (testdb1, testdb2));
BACKUP GROUP testdb1, testdb2
TO DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FORMAT;
Catatan
Secara default ditangguhkan untuk perintah pencadangan rekam jepret akan menghapus bitmap diferensial. Jika Anda lebih suka melakukan pencadangan salinan saja, gunakan kata kunci COPY_ONLY seperti yang ditunjukkan dalam contoh berikut.
Melakukan pencadangan rekam jepret khusus salinan
Karena bitmap diferensial dibersihkan sebelum membeku, SUSPEND_FOR_SNAPSHOT_BACKUP menyediakan opsi (COPY_ONLY) untuk tidak menghapus bitmap diferensial sebelum membeku.
ALTER DATABASE testdb1
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON
(MODE = COPY_ONLY);
BACKUP DATABASE testdb1
TO DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FORMAT;
ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON
(GROUP = (testdb1, testdb2), MODE = COPY_ONLY);
BACKUP GROUP testdb1, testdb2
TO DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FORMAT;
ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON
(MODE = COPY_ONLY);
BACKUP SERVER
TO DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FORMAT;
Catatan
Tidak perlu menggunakan COPY_ONLY pada perintah BACKUP, karena sudah ditentukan saat menangguhkan database untuk pencadangan rekam jepret.
Menandai set cadangan
Anda dapat menggunakan opsi MEDIANAME dan MEDIADESCRIPTION dalam perintah cadangan untuk menandai URI yang terkait dengan rekam jepret. Penggunaan ini memungkinkan file cadangan untuk membawa informasi rekam jepret yang mendasar bersama dengan metadata database. Anda juga dapat menggunakan opsi NAMA dan DESKRIPSI untuk menandai URI dengan rekam jepret set cadangan individual.
SQL Server tidak menginterpretasikan informasi LABEL dengan cara apa pun. Namun, ini membantu pengguna untuk melihat URI yang terkait dengan cadangan rekam jepret dengan perintah RESTORE LABELONLY.
Anda kemudian dapat melampirkan disk rekam jepret yang terletak di URI ke VM untuk memulihkan rekam jepret. URI rekam jepret yang disimpan di MEDIANAME dan MEDIADESCRIPTION juga akan tersedia untuk dilihat kemudian dalam msdb tabel msdb.dbo.backupmediasetdatabase .
Output cadangan rekam jepret dengan RESTORE HEADERONLY
Output dengan RESTORE HEADERONLY terlihat seperti berikut ini jika database, grup, dan server dijalankan secara berurutan dan ditulis ke file output yang sama:
RESTORE HEADERONLY
FROM DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY;
Output cadangan rekam jepret dengan RESTORE FILELISTONLY
Output dengan RESTORE FILELISTONLY menampilkan kumpulan cadangan pertama secara default:
RESTORE FILELISTONLY
FROM DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY;
Memfilter output RESTORE FILELISTONLY ke kumpulan cadangan
Untuk secara khusus memilih kumpulan cadangan tertentu dari beberapa set cadangan dengan RESTORE FILELISTONLY, gunakan klausa FILE yang sudah didukung pada RESTORE FILELISTONLY.
RESTORE FILELISTONLY
FROM DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FILE = 3;
Memfilter output RESTORE FILELISTONLY ke database
Filter Untuk lebih memilih database tunggal dari beberapa database dalam kumpulan cadangan yang dipilih dengan RESTORE FILELISTONLY gunakan klausa FILE dengan klausa DBNAME yang baru diperkenalkan. Klausa DBNAME hanya dapat digunakan pada kumpulan cadangan rekam jepret.
RESTORE FILELISTONLY
FROM DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FILE = 3, DBNAME = 'testdb3';
Memulihkan database rekam jepret
Memulihkan database dari cadangan rekam jepret seperti melampirkan database. Jalankan perintah pemulihan tanpa opsi RECOVERY jika database perlu dilampirkan tanpa pemulihan. Secara default, RESTORE memilih database pertama dalam kumpulan cadangan rekam jepret. Contoh berikut memulihkan testdb1. Jika testdb1 sudah ada di server, sertakan klausa REPLACE. Anda perlu memasang file database sebelum menjalankan RESTORE.
RESTORE DATABASE testdb1
FROM DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FILE = 3, REPLACE, --> no DBNAME clause - restore first database in backup set
MOVE 'testdb1' TO 'd:\temp\snap\testdb1.mdf',
MOVE 'testdb1_log' TO 'd:\temp\snap\testdb1_log.ldf';
Memulihkan database rekam jepret yang tercantum di tengah
Jika database yang perlu DIPULIHKAN berada di tengah, tentukan database yang akan dipulihkan dengan klausa DBNAME. Sintaks berikut memulihkan database yang ditentukan dalam klausa DBNAME.
RESTORE DATABASE testdb3
FROM DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FILE = 3, DBNAME = 'testdb3', --> restores testdb3 database
MOVE 'testdb3' TO 'd:\temp\snap\testdb3.mdf',
MOVE 'testdb3_log' TO 'd:\temp\snap\testdb3_log.ldf',
NORECOVERY;
Memulihkan database dengan nama yang berbeda
Anda bisa memulihkan database dengan nama yang berbeda. Jika database yang perlu DIPULIHKAN berada di tengah, tentukan database yang akan dipulihkan dengan klausa DBNAME. Sintaks berikut memulihkan database yang ditentukan dengan klausul DBNAME dan mengganti namanya menjadi testdb33.
RESTORE DATABASE testdb33 --> renames the specified database testdb3 to testdb33.
FROM DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FILE = 3, DBNAME = 'testdb3', --> original name specified here
MOVE 'testdb3' TO 'd:\temp\snap\testdb3.mdf',
MOVE 'testdb3_log' TO 'd:\temp\snap\testdb3_log.ldf',
NORECOVERY;
Gunakan RESTORE BACKUPSETONLY untuk mengekstrak database dari kumpulan cadangan yang berisi beberapa database
Kumpulan cadangan rekam jepret yang berisi beberapa database dari rekam jepret grup atau server dapat dipisahkan dengan perintah RESTORE BACKUPSETONLY. Ini menghasilkan satu set cadangan per database.
Jika rekam jepret server berisi tiga database dalam file cadangan yang berisi satu set cadangan, perintah berikut menghasilkan tiga set cadangan, satu untuk setiap database. Ini membuat direktori dengan <file_name_prefix>_<unique_time_stamp> untuk file output.
RESTORE BACKUPSETONLY
FROM DISK = 'd:\temp\db1.bkm'
WITH METADATA_ONLY;
Gunakan RESTORE BACKUPSETONLY untuk mengekstrak database tertentu dalam kumpulan cadangan yang berisi beberapa database
RESTORE BACKUPSETONLY mendukung parameter DBNAME jika pengguna ingin menghasilkan satu database dari tiga database dalam kumpulan cadangan. Ini juga mendukung parameter FILE untuk memfilter beberapa set cadangan dalam file cadangan.
RESTORE BACKUPSETONLY
FROM DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FILE = 3, DBNAME = 'testdb2';
Tampilan Manajemen Dinamis (DMV) untuk melihat status tangguhan dan kunci yang diperoleh
sys.dm_server_suspend_status (db_id, db_name, suspend_session_id, suspend_time_ms, is_diffmap_cleared, is_writeio_frozen)
sys.dm_tran_locks (resource_type, resource_database_id, resource_lock_partition, request_mode, request_type, request_status, request_owner_type, request_session_id)
Mencantumkan detail set cadangan untuk cadangan rekam jepret T-SQL
SELECT database_name,
type,
backup_size,
backup_start_date,
backup_finish_date,
is_snapshot
FROM msdb.dbo.backupset
WHERE is_snapshot = 1;
Properti tingkat server dan database untuk memeriksa apakah database ditangguhkan untuk pencadangan rekam jepret
SELECT SERVERPROPERTY('SuspendedDatabaseCount');
SELECT SERVERPROPERTY('IsServerSuspendedForSnapshotBackup');
SELECT DATABASEPROPERTYEX('db1', 'IsDatabaseSuspendedForSnapshotBackup');
Sampel skrip pemecahan masalah T-SQL
Contoh skrip T-SQL berikut dapat digunakan untuk mendeteksi database yang ditangguhkan di server, dan membatalkan penggunaannya jika perlu.
IF (SERVERPROPERTY('IsServerSuspendedForSnapshotBackup') = 1)
BEGIN
--full server suspended, requires server level thaw
PRINT 'Full server is suspended, requires server level thaw'
ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP = OFF
END
ELSE
BEGIN
IF (SERVERPROPERTY('SuspendedDatabaseCount') > 0)
BEGIN
DECLARE @curdb SYSNAME
DECLARE @sql NVARCHAR(500)
DECLARE mycursor CURSOR FAST_FORWARD
FOR
SELECT db_name
FROM sys.dm_server_suspend_status;
OPEN mycursor
FETCH NEXT
FROM mycursor
INTO @curdb
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'unfreezing DB ' + @curdb
SET @sql = 'ALTER DATABASE ' + @curdb + ' SET SUSPEND_FOR_SNAPSHOT_BACKUP = OFF'
EXEC sp_executesql @SQL
FETCH NEXT
FROM mycursor
INTO @curdb
END
PRINT 'All DB unfrozen'
CLOSE mycursor;
DEALLOCATE mycursor;
END
ELSE
-- no suspended database, thus no user action needed.
PRINT 'No database/server is suspended for snapshot backup'
END
Baca juga
Saran dan Komentar
Segera hadir: Sepanjang tahun 2024 kami akan menghentikan penggunaan GitHub Issues sebagai mekanisme umpan balik untuk konten dan menggantinya dengan sistem umpan balik baru. Untuk mengetahui informasi selengkapnya, lihat: https://aka.ms/ContentUserFeedback.
Kirim dan lihat umpan balik untuk