Membuat cadangan rekam jepret Transact-SQL

Berlaku untuk:yes Pratinjau SQL Server 2022 (16.x)

Artikel ini menjelaskan apa, mengapa, dan cara menggunakan cadangan rekam jepret Transact-SQL. Pencadangan rekam jepret SQL bertransaksi baru di Pratinjau SQL Server 2022.

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 berdampak pada 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 pencadangan 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 dalam keadaan yang nantinya dapat dipulihkan. Setelah ini selesai, I/O dibekukan pada 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 (idealnya kurang dari satu detik), 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 SQL Writer untuk menyelesaikan pencadangan rekam jepret. Layanan SQL Writer bergantung 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 pada Windows. Dengan cadangan rekam jepret T-SQL, sisi SQL Server orkestrasi dapat ditangani dengan serangkaian perintah T-SQL. Ini memungkinkan pengguna untuk membuat aplikasi cadangan sederhana mereka sendiri yang dapat berjalan pada 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 dalam Komputer Virtual IaaS Azure SQL menggunakan kemampuan pencadangan rekam jepret T-SQL yang diperkenalkan pada SQL Server 2022 (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:

  1. 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.
  2. Lakukan rekam jepret dari beberapa database secara bersamaan dengan perintah BACKUP GROUP dan BACKUP SERVER baru. Ini memungkinkan rekam jepret dilakukan pada granularitas rekam jepret penyimpanan yang mendasar dan menghilangkan kebutuhan Anda untuk melakukan rekam jepret dari disk yang sama beberapa kali.
  3. Lakukan pencadangan PENUH serta COPY_ONLY pencadangan PENUH. Cadangan ini juga dicatat dalam msdb.
  4. 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 tidak akan 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:

Diagram that shows process from suspend, to snapshot, to backup.

Langkah rekam jepret tengah mengharuskan Anda untuk memulai rekam jepret pada penyimpanan yang mendasar. Diagram berikut menunjukkan contoh cara kerja skrip cadangan bersama dengan SQL Server untuk menyelesaikan proses pencadangan rekam jepret:

Diagram shows example of how the backup script can work with SQL Server to complete the backup process.

Demikian pula, skrip pemulihan mungkin berfungsi sebagai berikut:

Diagram shows how the restore script can work with SQL Server to complete the restore task from a snapshot backup.

Contoh

Bagian berikut menunjukkan perintah T-SQL 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 akan 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. RESTORE database dari file ini akan mengharuskan pengguna untuk menyalin file database dari URI rekam jepret ke titik pemasangan sebelum mengeluarkan perintah RESTORE. Pengguna akan 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

Database mungkin berada di disk yang mendasari yang sama. Dalam contoh ini, Anda mungkin merekam cadangan rekam jepret dari beberapa database ke dalam satu set cadangan.

ALTER DATABASE testdb1
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON

ALTER DATABASE testdb2
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON

BACKUP GROUP testdb1, testdb2
TO DISK='d:\temp\db.bkm'
WITH METADATA_ONLY, FORMAT

Menangguhkan server untuk membekukan semua database pengguna dan melakukan rekam jepret

Rekam rekam rekam semua database pengguna di server ke dalam satu set cadangan:

ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON

BACKUP SERVER
TO DISK='d:\temp\db.bkm'
WITH METADATA_ONLY, FORMAT

Menangguhkan beberapa database pengguna dengan satu perintah

Rekam rekam rekam 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 menangguhkan untuk perintah pencadangan rekam jepret akan menghapus bitmap diferensial. Jika Anda lebih suka melakukan pencadangan salin saja, gunakan kata kunci COPY_ONLY seperti yang ditunjukkan di bawah ini.

Melakukan pencadangan rekam jepret Copy-Only

Karena bitmap diferensial dibersihkan sebelum dibekukan, 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 (MODE=COPY_ONLY)

BACKUP SERVER
TO DISK='d:\temp\db.bkm'
WITH METADATA_ONLY

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:

BACKUP DATABASE testdb1
TO DISK='d:\temp\db.bkm'
WITH METADATA_ONLY, FORMAT

BACKUP GROUP testdb1, testdb2
TO DISK='d:\temp\db.bkm'
WITH METADATA_ONLY

BACKUP SERVER
TO DISK='d:\temp\db.bkm'
WITH METADATA_ONLY

RESTORE HEADERONLY
FROM DISK='d:\temp\db.bkm'
WITH METADATA_ONLY

Menandai kumpulan cadangan

Anda dapat menggunakan sakelar MEDIANAME dan MEDIADESCRIPTION dalam perintah cadangan untuk menyimpan URI yang terkait dengan rekam jepret. Penggunaan ini memungkinkan set cadangan untuk membawa informasi rekam jepret yang mendasar bersama dengan metadata database.

SQL Server tidak akan menginterpretasikan informasi LABEL dengan cara apa pun, namun akan 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 ditampilkan kemudian dalam tabel msdb.dbo.backupmediasetdatabase msdb .

BACKUP (Transact-SQL) - SQL Server | Microsoft Docsbackupmediaset (Transact-SQL) - SQL Server | Microsoft Docs

Output cadangan rekam jepret dengan RESTORE FILELISTONLY

Output dengan RESTORE FILELISTONLY menampilkan cadangan pertama yang ditetapkan 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

Screenshot of SSMS output to backups set from query.

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'

Screenshot of results of filtering RESTORE FILELISTONLY output to a database.

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

Menggunakan 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 akan menghasilkan satu set cadangan per database.

Jika rekam jepret server berisi 3 database dalam file cadangan yang berisi satu set cadangan, perintah berikut akan menghasilkan 3 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

Menggunakan 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 3 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)

Properti tingkat server dan database untuk memeriksa apakah database ditangguhkan untuk pencadangan rekam jepret

SELECT SERVERPROPERTY('SuspendedDatabaseCount')
SELECT DATABASEPROPERTYEX('db1', 'IsDatabaseSuspendedForSnapshotBackup’)