Membuat cadangan database lengkap

Berlaku untuk:SQL Server

Artikel ini menjelaskan cara membuat cadangan database penuh di SQL Server menggunakan SQL Server Management Studio (SSMS), Transact-SQL, atau PowerShell.

Untuk informasi selengkapnya, lihat Pencadangan dan pemulihan SQL Server dengan Azure Blob Storage dan cadangan SQL Server ke URL untuk Azure Blob Storage. Untuk gambaran umum konsep dan tugas backup, lihat Ikhtisar Backup (SQL Server).

Rekomendasi

• Seiring bertambahnya basis data, cadangan penuh basis data memakan waktu lebih lama dan membutuhkan lebih banyak penyimpanan. Untuk basis data besar, lengkapi cadangan penuh dengan serangkaian cadangan basis data diferensial.

• Perkirakan ukuran cadangan penuh database dengan prosedur tersimpan sistem sp_spaceused.

• Secara default, setiap backup yang berhasil menambahkan entri ke log kesalahan SQL Server dan log event sistem. Pencadangan yang sering dapat mengisi log ini dan membuat pesan lain sulit ditemukan.

  Untuk menekan entri log backup, gunakan trace flag 3226, asalkan skrip Anda tidak bergantung pada mereka.

Keterbatasan

Anda tidak bisa menggunakan pernyataan tersebut BACKUP dalam transaksi eksplisit atau implisit.

Anda tidak dapat memulihkan cadangan dari versi SQL Server yang lebih baru pada versi sebelumnya. Misalnya, Anda tidak dapat memulihkan cadangan SQL Server 2025 (17.x) di SQL Server 2022 (16.x).

Keamanan

TRUSTWORTHY diatur ke OFF pada cadangan database. Untuk mengatur TRUSTWORTHY ke ON, lihat ALTER DATABASE SET opsi.

Mulai dari SQL Server 2012 (11.x), PASSWORD opsi dan MEDIAPASSWORD tidak tersedia untuk membuat cadangan. Anda masih dapat memulihkan cadangan yang dibuat dengan kata sandi.

Perizinan

BACKUP DATABASE dan BACKUP LOG secara default diberikan kepada anggota peran server tetap sysadmin, serta peran database tetap db_owner dan db_backupoperator.

Akun layanan SQL Server harus memiliki izin baca dan tulis pada file fisik perangkat cadangan. Masalah kepemilikan atau izin pada file tersebut mencegah operasi backup dan pemulihan, dan hanya muncul saat operasi backup atau restore dijalankan.

Misalnya, prosedur tersimpan sistem sp_addumpdevice , yang menambahkan entri perangkat cadangan ke tabel sistem, tidak memvalidasi akses file.

Menggunakan SQL Server Management Studio

1. Di Object Explorer, hubungkan ke Database Engine, lalu perluas pohon server.

2. Perluas Databases, lalu pilih database pengguna. Atau, perluas System Databases dan pilih database sistem.

3. Klik kanan database yang ingin Anda cadangkan, arahkan ke Tasks, lalu pilih Cadangkan Kembali....

4. Di kotak dialog Cadangan Database , database yang Anda pilih akan muncul di daftar dropdown. Anda dapat mengubah database ke database lain di server.

5. Di daftar Jenis cadangan , pilih jenis cadangan. Defaultnya adalah Penuh.

   Penting

   Anda harus melakukan setidaknya satu pencadangan database penuh sebelum dapat melakukan pencadangan diferensial atau pencadangan log transaksi.

6. Di bawah Komponen cadangan, pilih Database.

7. Di bagian Tujuan , tinjau lokasi default untuk file cadangan (di .. Folder /mssql/data).

   Gunakan daftar Cadangkan ke untuk memilih perangkat lain. Pilih Tambah untuk menambahkan objek atau tujuan cadangan. Anda dapat membagi set cadangan ke beberapa file untuk meningkatkan kecepatan backup.

   Untuk menghapus tujuan pencadangan, pilih tujuan tersebut dan pilih Hapus. Untuk melihat isi tujuan cadangan yang sudah ada, pilih dan pilih Isi.

8. Sebagai opsional, tinjau pengaturan lain pada halaman Opsi Media dan Opsi Pencadangan .

   Untuk informasi lebih lanjut tentang opsi backup, lihat Cadangan Database (Halaman Umum),Cadangan Database (halaman Opsi Media), dan Cadangan Database (Halaman Opsi Cadangan).

9. Pilih OK untuk memulai proses pencadangan.

10. Setelah backup selesai dengan sukses, pilih OK untuk menutup dialog.

Komentar

• Saat Anda menentukan tugas cadangan di SQL Server Management Studio, Anda dapat menghasilkan skrip Transact-SQL BACKUP yang sesuai dengan memilih tombol Script lalu memilih tujuan skrip.

• Setelah Anda membuat cadangan database penuh, Anda dapat membuat cadangan database diferensial atau backup log transaksi.

• Opsional, pilih kotak centang Cadangan hanya salin untuk membuat cadangan hanya salinan. Cadangan khusus salinan adalah cadangan SQL Server yang independen dari urutan cadangan SQL Server konvensional. Untuk informasi selengkapnya, lihat Pencadangan salin-saja. Pencadangan khusus salinan tidak tersedia untuk jenis pencadangan Diferensial.

• Opsi Timpa media dinonaktifkan pada halaman Media Options saat Anda melakukan backup ke URL.

Contoh

Untuk contoh berikut, buat database uji dengan menggunakan kode Transact-SQL berikut:

USE master;
GO

CREATE DATABASE [SQLTestDB];
GO

USE [SQLTestDB];
GO

CREATE TABLE SQLTest
(
    ID INT NOT NULL PRIMARY KEY,
    c1 VARCHAR (100) NOT NULL,
    dt1 DATETIME DEFAULT getdate() NOT NULL
);
GO

USE [SQLTestDB];
GO

INSERT INTO SQLTest (ID, c1) VALUES (1, 'test1');
INSERT INTO SQLTest (ID, c1) VALUES (2, 'test2');
INSERT INTO SQLTest (ID, c1) VALUES (3, 'test3');
INSERT INTO SQLTest (ID, c1) VALUES (4, 'test4');
INSERT INTO SQLTest (ID, c1) VALUES (5, 'test5');
GO

SELECT *
FROM SQLTest;
GO

Sebuah. Pencadangan penuh ke disk di lokasi bawaan

Contoh ini mencadangkan basis data SQLTestDB ke diska di lokasi pencadangan bawaan.

1. Di Object Explorer, hubungkan ke Database Engine, lalu perluas pohon server.

2. Perluas Database, klik SQLTestDBkanan , arahkan ke Tugas, lalu pilih Cadangkan....

3. Pilih OK.

4. Setelah backup selesai dengan sukses, pilih OK untuk menutup dialog.

B. Pencadangan penuh ke disk ke lokasi selain default

Contoh ini mencadangkan basis data SQLTestDB ke disk pada lokasi yang Anda pilih.

1. Di Object Explorer, hubungkan ke Database Engine, lalu perluas pohon server.

2. Perluas Database, klik SQLTestDBkanan , arahkan ke Tugas, lalu pilih Cadangkan....

3. Pada halaman Umum, di bagian Tujuan, pilih Disk dalam daftar Cadangkan ke.

4. Pilih Hapus hingga semua file cadangan yang ada dihapus.

5. Pilih Tambahkan. Kotak dialog Pilih Tujuan Cadangan akan terbuka.

6. Masukkan jalur dan nama file yang valid dalam kotak Nama file. Gunakan .bak sebagai ekstensi untuk menyederhanakan klasifikasi file.

7. Pilih OK, lalu pilih OK lagi untuk memulai backup.

8. Setelah backup selesai dengan sukses, pilih OK untuk menutup dialog.

C. Membuat cadangan terenkripsi

Contoh ini membuat cadangan basis data SQLTestDB dengan enkripsi ke lokasi pencadangan default.

1. Di Object Explorer, hubungkan ke Database Engine, lalu perluas pohon server.

2. Perluas Database, perluas Database Sistem, klik masterkanan , lalu pilih Kueri Baru untuk membuka jendela kueri dengan koneksi ke database Anda SQLTestDB .

3. Jalankan perintah berikut untuk membuat kunci master database dan sertifikat di master database.

   -- Create the master key.
   CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';
   
   -- If the master key already exists, open it in the same session that you create the certificate. (See next step.)
   OPEN MASTER KEY DECRYPTION BY PASSWORD = '<password>';
   
   -- Create the certificate encrypted by the master key.
   CREATE CERTIFICATE MyCertificate
   WITH SUBJECT = 'Backup Cert', EXPIRY_DATE = '20201031';
   

4. Di Object Explorer, di simpul Databases, klik kanan SQLTestDB, arahkan ke Tugas, lalu pilih Cadangkan....

5. Pada halaman Opsi Media, di bagian Timpa media, pilih Cadangkan ke kumpulan media baru, dan hapus semua kumpulan cadangan yang ada.

6. Pada halaman Opsi Cadangan , di bagian Enkripsi , pilih Enkripsi cadangan.

7. Di daftar Algoritma , pilih AES 256.

8. Di daftar Kunci sertifikat atau Asimetris , pilih MyCertificate.

9. Pilih OK.

D. Membackup ke Azure Blob Storage

Contoh ini membuat cadangan SQLTestDB database lengkap ke Azure Blob Storage. Contoh ini mengasumsikan Anda sudah memiliki akun penyimpanan dengan kontainer blob. Contoh ini membuat tanda tangan akses bersama, dan gagal jika kontainer memiliki tanda tangan akses bersama yang sudah ada.

Jika Anda tidak memiliki kontainer Blob Storage di akun penyimpanan, buat dulu sebelum melanjutkan. Lihat Membuat akun penyimpanan tujuan umum dan Membuat kontainer.

1. Di Object Explorer, hubungkan ke Database Engine, lalu perluas pohon server.

2. Perluas Database, klik SQLTestDBkanan , arahkan ke Tugas, lalu pilih Cadangkan....

3. Pada halaman Umum, di bagian Tujuan, pilih URL dari daftar Cadangkan ke.

4. Pilih Tambahkan. Kotak dialog Pilih Tujuan Cadangan akan terbuka.

5. Jika sebelumnya Anda telah mendaftarkan kontainer penyimpanan Azure yang ingin Anda gunakan dengan SSMS, pilihlah kontainer tersebut. Jika tidak, pilih Kontainer baru untuk mendaftarkan kontainer baru.

6. Di kotak dialog Hubungkan ke Langganan Microsoft, masuk ke akun Anda.

7. Dalam kotak Pilih Akun Penyimpanan , pilih akun penyimpanan Anda.

8. Dalam kotak Pilih Kontainer Blob , pilih kontainer blob Anda.

9. Di kotak kalender Kadaluarsa Kebijakan Akses Bersama , pilih tanggal kedaluwarsa untuk kebijakan akses bersama yang dibuat oleh contoh ini.

10. Pilih Buat Kredensial untuk menghasilkan tanda tangan akses bersama dan kredensial di SSMS.

11. Pilih OK untuk menutup kotak dialog Hubungkan ke Langganan Microsoft.

12. Dalam kotak File Cadangan , ubah nama file cadangan jika Anda ingin.

13. Pilih OK untuk menutup kotak dialog Pilih destinasi cadangan .

14. Pilih OK untuk memulai proses pencadangan.

15. Setelah backup selesai dengan sukses, pilih OK untuk menutup dialog.

Catatan

Mencadangkan ke Blob Storage dengan menggunakan identitas terkelola saat ini tidak didukung.

Gunakan Transact-SQL

Buat cadangan database penuh dengan menjalankan pernyataan tersebut BACKUP DATABASE . Tentukan:

• Nama database yang akan dicadangkan.
• Perangkat cadangan yang menyimpan cadangan lengkap database.

Sintaks Transact-SQL dasar untuk pencadangan database lengkap adalah:

BACKUP DATABASE <database>
TO <backup_device> [ , ...n ]
[ WITH <with_options> [ , ...o ] ];

Opsi	Deskripsi
<database>	Basis data yang akan dicadangkan.
<backup_device> [ , ...n ]	Menentukan 1 hingga 64 perangkat cadangan yang digunakan untuk operasi backup. Tentukan perangkat cadangan fisik, atau tentukan perangkat cadangan logis yang sesuai jika sudah didefinisikan. Untuk menentukan perangkat cadangan fisik, gunakan DISKopsi , TAPE, atau URL : { DISK | URL | TAPE} = physical_backup_device_name Gunakan URL untuk mencadangkan ke Azure Blob Storage atau penyimpanan objek yang kompatibel dengan S3. Untuk informasi selengkapnya, lihat Perangkat Cadangan (SQL Server).
WITH <with_options> [ , ...n ]	Digunakan untuk menentukan satu atau lebih opsi, n. Beberapa opsi dasar WITH dijelaskan dalam daftar berikut.

Secara opsional, tentukan satu atau beberapa WITH opsi. Beberapa opsi dasar WITH dijelaskan di sini. Untuk informasi tentang semua WITH opsi, lihat BACKUP.

Opsi kumpulan cadangan dasar WITH:

• { KOMPRESI | TANPA_KOMPRESI }. SQL Server menentukan apakah kompresi cadangan dilakukan pada cadangan, dengan menggantikan default tingkat server.

• ENKRIPSI (ALGORITMA, SERVER CERTIFICATE | ASYMMETRIC KEY). Di SQL Server 2014 atau versi lebih baru, menentukan algoritma enkripsi yang akan digunakan, serta sertifikat atau kunci asimetris yang digunakan untuk mengamankan enkripsi.

• DESKRIPSI = { 'teks' | @text_variable }. Menentukan teks bentuk bebas yang menjelaskan kumpulan cadangan. String dapat memiliki maksimal 255 karakter.

• NAME = { backup_set_name | @backup_set_name_var }. Menentukan nama kumpulan cadangan. Nama dapat memiliki maksimal 128 karakter. Jika Anda tidak mencantumkan NAME, maka kosong.

Secara default, BACKUP menambahkan cadangan ke set media yang ada, mempertahankan set cadangan yang ada. Untuk menentukan konfigurasi ini secara eksplisit, gunakan NOINIT opsi . Untuk informasi tentang menambahkan ke set cadangan yang ada, lihat Set media, keluarga media, dan set cadangan (SQL Server).

Untuk memformat media cadangan, gunakan FORMAT opsi :

FORMAT [ , MEDIANAME = { media_name | @media_name_variable } ] [ , MEDIADESCRIPTION = { text | @text_variable } ]

Gunakan klausa FORMAT ini saat Anda menggunakan media untuk pertama kalinya, atau saat Anda ingin menimpa semua data yang ada. Secara opsional, tetapkan media baru nama dan deskripsi media.

Penting

Berhati-hatilah saat menggunakan FORMAT klausa pernyataan BACKUP karena opsi ini akan menghancurkan cadangan yang sebelumnya disimpan di media cadangan.

Contoh

Untuk contoh berikut, buat database uji dengan menggunakan kode Transact-SQL berikut:

USE master;
GO

CREATE DATABASE [SQLTestDB];
GO

USE [SQLTestDB];
GO

CREATE TABLE SQLTest
(
    ID INT NOT NULL PRIMARY KEY,
    c1 VARCHAR (100) NOT NULL,
    dt1 DATETIME DEFAULT GETDATE() NOT NULL
);
GO

USE [SQLTestDB];
GO

INSERT INTO SQLTest (ID, c1) VALUES (1, 'test1');
INSERT INTO SQLTest (ID, c1) VALUES (2, 'test2');
INSERT INTO SQLTest (ID, c1) VALUES (3, 'test3');
INSERT INTO SQLTest (ID, c1) VALUES (4, 'test4');
INSERT INTO SQLTest (ID, c1) VALUES (5, 'test5');
GO

SELECT *
FROM SQLTest;
GO

Sebuah. Cadangkan ke perangkat disk

Contoh berikut membuat cadangan basis data lengkap SQLTestDB ke disk. Fitur ini menggunakan FORMAT untuk membuat kumpulan media baru.

USE SQLTestDB;
GO

BACKUP DATABASE SQLTestDB
TO DISK = 'c:\tmp\SQLTestDB.bak'
WITH FORMAT,
     MEDIANAME = 'SQLServerBackups',
     NAME = 'Full Backup of SQLTestDB';
GO

B. Mencadangkan ke perangkat pita

Contoh berikut membuat cadangan database lengkap SQLTestDB ke pita. Menambahkan cadangan ke cadangan yang sudah ada sebelumnya.

USE SQLTestDB;
GO

BACKUP DATABASE SQLTestDB
TO TAPE = '\\.\Tape0'
WITH NOINIT,
     NAME = 'Full Backup of SQLTestDB';
GO

C. Membuat cadangan ke perangkat pita logis

Contoh berikut membuat perangkat cadangan logis untuk tape drive. Contoh tersebut kemudian membuat cadangan basis data lengkap SQLTestDB ke perangkat tersebut.

-- Create a logical backup device,
-- SQLTestDB_Bak_Tape, for tape device \\.\tape0.
USE master;
GO

EXECUTE sp_addumpdevice 'tape', 'SQLTestDB_Bak_Tape', '\\.\tape0';

USE SQLTestDB;
GO

BACKUP DATABASE SQLTestDB
TO SQLTestDB_Bak_Tape
WITH FORMAT,
     MEDIANAME = 'SQLTestDB_Bak_Tape',
     MEDIADESCRIPTION = '\\.\tape0',
     NAME = 'Full Backup of SQLTestDB';
GO

PowerShell

Gunakan cmdlet Backup-SqlDatabase. Untuk secara eksplisit menunjukkan cadangan database lengkap, tentukan -BackupAction parameter dengan nilai defaultnya, Database. Parameter ini bersifat opsional untuk pencadangan database lengkap.

Jika Anda membuka jendela PowerShell dari dalam SSMS untuk terhubung ke Mesin Database SQL Server, Anda dapat menghilangkan bagian kredensial karena kredensial Anda di SSMS secara otomatis membangun koneksi antara PowerShell dan Database Engine.

Catatan

Contoh ini memerlukan modul SqlServer. Untuk informasi selengkapnya, lihat Provider PowerShell SQL Server.

Contoh

Sebuah. Pencadangan penuh (lokal)

Contoh berikut membuat cadangan database lengkap database <myDatabase> ke lokasi cadangan default instans Computer\Instanceserver . Secara opsional, contoh ini menentukan -BackupAction Database.

Untuk contoh sintaks lengkap, lihat Backup-SqlDatabase.

$credential = Get-Credential

Backup-SqlDatabase -ServerInstance Computer[\Instance] -Database <myDatabase> -BackupAction Database -Credential $credential

B. Pencadangan penuh ke Azure

Contoh berikut membuat cadangan lengkap database <myDatabase> pada <myServer> instans ke Blob Storage. Kebijakan akses tersimpan dibuat dengan hak baca, tulis, dan daftar. Kredensial SQL Server, https://<myStorageAccount>.blob.core.windows.net/<myContainer>, dibuat dengan menggunakan tanda tangan akses bersama yang terkait dengan kebijakan akses tersimpan. Perintah menggunakan $backupFile parameter untuk menentukan lokasi (URL) dan nama file cadangan.

$credential = Get-Credential
$container = 'https://<myStorageAccount>blob.core.windows.net/<myContainer>'
$fileName = '<myDatabase>.bak'
$server = '<myServer>'
$database = '<myDatabase>'
$backupFile = $container + '/' + $fileName

Backup-SqlDatabase -ServerInstance $server -Database $database -BackupFile $backupFile -Credential $credential

Tugas terkait

• Membuat cadangan database diferensial (SQL Server)
• Memulihkan cadangan database menggunakan SQL Server Management Studio (SSMS)
• Pulihkan cadangan database di bawah model pemulihan sederhana
• Memulihkan database ke titik kegagalan - pemulihan penuh
• Memulihkan database ke lokasi baru (SQL Server)
• Menggunakan Wizard Rencana Pemeliharaan

Konten terkait

• Memecahkan masalah operasi pencadangan dan pemulihan SQL Server
• Gambaran umum pencadangan (SQL Server)
• Pencadangan log transaksi (SQL Server)
• Set media, keluarga media, dan set cadangan (SQL Server)
• sys.sp_addumpdevice (Transact-SQL)
• BACKUP (Transact-SQL)
• Mencadangkan Database (Halaman Umum)
• Pencadangan Database (Halaman Opsi Cadangan)
• Cadangan diferensial (SQL Server)
• Pencadangan database lengkap (SQL Server)