Bagikan melalui

Membuat cadangan rekam jepret Transact-SQL

  • Artikel

Berlaku untuk: SQL Server 2022 (16.x)

Artikel ini menjelaskan apa, mengapa, dan cara menggunakan cadangan rekam jepret Transact-SQL. Cadangan rekam jepret Transact-SQL (T-SQL) diperkenalkan 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 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, fitur 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 langkah ini selesai, operasi tulis dijeda di SQL Server (permintaan baca masih diizinkan), dan kontrol diserahkan ke aplikasi cadangan untuk menyelesaikan rekam jepret. Setelah rekam jepret berhasil diselesaikan, aplikasi harus mengembalikan kontrol kembali ke SQL Server tempat operasi tulis kemudian dilanjutkan.

Karena kita harus membekukan operasi tulis selama 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 mengandalkan solusi non-Microsoft 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. Fungsionalitas 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. Sampel menggunakan kemampuan pencadangan rekam jepret T-SQL yang diperkenalkan di SQL Server 2022 (16.x).

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 ALTER perintah , yang memberikan kesempatan bagi Anda untuk melakukan rekam jepret penyimpanan yang mendasar. Setelah itu, Anda dapat mencukur database dan merekam rekam jepret dengan BACKUP perintah .

  2. Lakukan rekam jepret beberapa database secara bersamaan dengan perintah dan BACKUP SERVER baruBACKUP GROUP. 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.

  3. Lakukan FULL pencadangan dan COPY_ONLY FULL pencadangan. Cadangan ini juga dicatat dalam msdb .

  4. Lakukan pemulihan point-in-time menggunakan cadangan log yang diambil dengan pendekatan streaming normal setelah pencadangan rekam jepret FULL . Pencadangan diferensial streaming juga didukung jika diinginkan.

Catatan

Bitmap diferensial dibersihkan selama tahap pertama saat menangguhkan database dengan ALTER perintah . Jika pengguna memutuskan untuk mencairkan database tanpa melakukan pencadangan karena rekam jepret gagal atau karena alasan lain, bitmap diferensial tidak valid. Setiap cadangan diferensial berikutnya lebih intensif I/O, karena mereka harus memindai seluruh database untuk melakukan pencadangan diferensial. Bitmap diferensial menjadi valid lagi setelah pencadangan rekam jepret berhasil.

Diagram berikut mengilustrasikan alur kerja tingkat tinggi cadangan rekam jepret T-SQL:

Diagram yang memperlihatkan proses dari ditangguhkan, ke rekam jepret, ke cadangan.

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

Diagram menunjukkan contoh cara kerja skrip cadangan dengan SQL Server untuk menyelesaikan proses pencadangan.

Demikian pula, skrip pemulihan dapat berfungsi sebagai berikut:

Diagram memperlihatkan bagaimana skrip pemulihan dapat bekerja dengan SQL Server untuk menyelesaikan tugas pemulihan dari cadangan rekam jepret.

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. Database RESTORE dari file ini mengharuskan pengguna menyalin file database dari URI rekam jepret ke titik pemasangan, sebelum mengeluarkan RESTORE perintah. Pengguna dapat menjalankan semua perintah T-SQL tradisional, seperti RESTORE HEADERONLY dan 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.

J. 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;

B. Menangguhkan beberapa database pengguna untuk pencadangan rekam jepret

Jika beberapa database berada 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;

C. 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.

D. 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, SUSPEND_FOR_SNAPSHOT_BACKUP perintah menghapus bitmap diferensial. Jika Anda lebih suka melakukan pencadangan salin saja, gunakan COPY_ONLY kata kunci seperti yang ditunjukkan dalam contoh berikut.

E. Melakukan pencadangan rekam jepret khusus salinan

Karena bitmap diferensial dibersihkan sebelum pembekuan, SUSPEND_FOR_SNAPSHOT_BACKUP berikan 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 digunakan COPY_ONLY pada BACKUP perintah, karena sudah ditentukan saat menangguhkan database untuk pencadangan rekam jepret.

F. Mencadangkan database dengan file data dan log pada drive yang berbeda

Jika Anda memiliki database dengan file data (.mdf dan .ndf) di beberapa drive, dan file log transaksi (.ldf) pada drive yang berbeda, Anda dapat melakukan pencadangan rekam jepret sebagai berikut:

  1. Tangguhkan database (yang membekukan I/O tulis pada file data dan log).

    ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON;

  2. Ambil rekam jepret semua disk yang mendasar tempat data database dan file log ada. Langkah ini tergantung pada perangkat keras.

  3. Lakukan pencadangan menggunakan METADATA_ONLY opsi , yang membuat output yang berisi metadata cadangan rekam jepret (.bkm).

    BACKUP DATABASE testdb1
TO DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY;

Untuk memulihkan cadangan ini di tahap selanjutnya, ikuti langkah-langkah berikut:

  1. Pasang atau lampirkan disk rekam jepret pada VM tempat Anda ingin memulihkan.

  2. .bkm Gunakan file (dari langkah 3 di daftar sebelumnya) saat Anda melakukan pemulihan database.

  3. Jika drive berbeda selama pemulihan, gunakan MOVE opsi untuk file logis untuk menempatkannya di tujuan yang diperlukan. Misalnya, lihat Contoh N.

G. Menandai set cadangan

Anda dapat menggunakan MEDIANAME opsi 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 NAME opsi dan DESCRIPTION untuk menandai URI dengan rekam jepret set cadangan individual.

SQL Server tidak menginterpretasikan informasi dengan LABEL cara apa pun. Namun, ini membantu pengguna untuk melihat URI yang terkait dengan cadangan rekam jepret dengan RESTORE LABELONLY perintah .

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 kemudian tersedia untuk ditampilkan dalam msdb tabel dbo.backupmediasetdatabase .

H. Output cadangan rekam jepret dengan RESTORE HEADERONLY

Output dengan RESTORE HEADERONLY terlihat seperti sampel berikut, 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;

I. 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;

j. Memfilter output RESTORE FILELISTONLY ke kumpulan cadangan

Untuk secara khusus memilih kumpulan cadangan tertentu dari beberapa set cadangan dengan RESTORE FILELISTONLY, gunakan FILE klausul yang sudah didukung pada RESTORE FILELISTONLY.

RESTORE FILELISTONLY
FROM DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY, FILE = 3;

Cuplikan layar output SSMS ke kumpulan cadangan dari kueri.

K. Memfilter output RESTORE FILELISTONLY ke database

Untuk lebih memilih database tunggal dari beberapa database dalam kumpulan cadangan yang dipilih dengan RESTORE FILELISTONLY, gunakan FILE klausa dengan DBNAME klausa . 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';

Cuplikan layar hasil pemfilteran output RESTORE FILELISTONLY ke database.

L. Memulihkan database rekam jepret

Memulihkan database dari cadangan rekam jepret seperti melampirkan database. Jalankan perintah pemulihan tanpa RECOVERY opsi , 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 REPLACE klausa. 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';

M. Memulihkan database rekam jepret yang tercantum di tengah

Jika database yang perlu RESTORED berada di tengah, tentukan database yang akan dipulihkan dengan DBNAME klausa. Sintaks berikut memulihkan database yang ditentukan dalam DBNAME klausa.

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;

N. Memulihkan database dengan nama yang berbeda

Anda bisa memulihkan database dengan nama yang berbeda. Jika database yang perlu RESTORED berada di tengah, tentukan database yang akan dipulihkan dengan DBNAME klausa. Sintaks berikut memulihkan database yang ditentukan dengan DBNAME klausul 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;

O. 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 RESTORE BACKUPSETONLY perintah . Perintah 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;

P. Gunakan RESTORE BACKUPSETONLY untuk mengekstrak database tertentu dalam kumpulan cadangan yang berisi beberapa database

RESTORE BACKUPSETONLYDBNAME mendukung parameter jika pengguna ingin menghasilkan satu database dari tiga database dalam kumpulan cadangan. Ini juga mendukung FILE parameter untuk memfilter beberapa set cadangan dalam file cadangan.

RESTORE BACKUPSETONLY
FROM DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY, FILE = 3, DBNAME = 'testdb2';

T. Memantau status tangguhan dan kunci yang diperoleh

Anda dapat menggunakan tampilan manajemen dinamis (DMV) berikut:

  • sys.dm_server_suspend_status (lihat status ditangguhkan)
  • sys.dm_tran_locks (lihat kunci yang diperoleh)

R. Mencantumkan detail set cadangan

Contoh skrip berikut mencantumkan informasi set cadangan untuk cadangan rekam jepret Transact-SQL.

SELECT database_name,
    type,
    backup_size,
    backup_start_date,
    backup_finish_date,
    is_snapshot
FROM msdb.dbo.backupset
WHERE is_snapshot = 1;

S. Periksa apakah database ditangguhkan untuk pencadangan rekam jepret

Contoh skrip berikut menghasilkan properti tingkat database untuk database yang ditangguhkan untuk pencadangan rekam jepret.

SELECT SERVERPROPERTY('SuspendedDatabaseCount');
SELECT SERVERPROPERTY('IsServerSuspendedForSnapshotBackup');
SELECT DATABASEPROPERTYEX('db1', 'IsDatabaseSuspendedForSnapshotBackup');

T. Sampel skrip pemecahan masalah T-SQL

Contoh skrip berikut 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

Konten terkait