Membuat Pencadangan Database Lengkap

Artikel
01/09/2024

Berlaku untuk:SQL Server

Artikel ini menjelaskan cara membuat cadangan database lengkap di SQL Server menggunakan SQL Server Management Studio, Transact-SQL, atau PowerShell.

Untuk informasi selengkapnya, lihat Pencadangan dan Pemulihan SQL Server dengan Azure Blob Storage dan Pencadangan SQL Server ke URL.

Pembatasan dan batasan

Pernyataan BACKUP tidak diizinkan dalam transaksi eksplisit atau implisit.
Cadangan yang dibuat oleh versi SQL Server yang lebih baru tidak dapat dipulihkan di versi SQL Server yang lebih lama.

Untuk gambaran umum, dan penyelaman yang lebih mendalam, konsep dan tugas pencadangan, lihat Gambaran Umum Cadangan (SQL Server) sebelum melanjutkan.

Rekomendasi

Seiring bertambahnya ukuran database, pencadangan database lengkap membutuhkan lebih banyak waktu untuk diselesaikan dan membutuhkan lebih banyak ruang penyimpanan. Untuk database besar, pertimbangkan untuk melengkapi cadangan database lengkap dengan serangkaian cadangan database diferensial.
Perkirakan ukuran cadangan database lengkap dengan menggunakan prosedur tersimpan sistem sp_spaceused .
Secara default, setiap operasi pencadangan yang berhasil menambahkan entri di log kesalahan SQL Server dan di log peristiwa sistem. Jika Anda sering mencadangkan, pesan keberhasilan akan terakumulasi dengan cepat, yang mengakibatkan log kesalahan besar, membuat menemukan pesan lain menjadi sulit. Dalam kasus seperti itu, Anda dapat menekan entri log cadangan ini dengan menggunakan bendera pelacakan 3226 jika tidak ada skrip Anda yang bergantung pada entri tersebut. Untuk informasi selengkapnya, lihat Bendera Pelacakan (Transact-SQL).

Keamanan

TRUSTWORTHY diatur ke NONAKTIF pada cadangan database. Untuk informasi tentang cara mengatur TRUSTWORTHY ke AKTIF, lihat MENGUBAH Opsi SET DATABASE (Transact-SQL).

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

Izin

BACKUP DATABASE dan BACKUP LOG izin default untuk anggota peran server tetap sysadmin dan peran database tetap db_owner dan db_backupoperator .

Masalah kepemilikan dan izin pada file fisik perangkat cadangan dapat mengganggu operasi pencadangan. Layanan SQL Server harus membaca dan menulis ke perangkat. Akun tempat layanan SQL Server berjalan harus memiliki izin tulis ke perangkat cadangan. Namun, sp_addumpdevice, yang menambahkan entri untuk perangkat cadangan dalam tabel sistem, tidak memeriksa izin akses file. Masalah dalam file fisik perangkat cadangan mungkin tidak muncul sampai cadangan digunakan atau pemulihan dicoba.

Menggunakan SQL Server Management Studio

Catatan

Saat Anda menentukan tugas pencadangan dengan menggunakan SQL Server Management Studio, Anda dapat membuat skrip Pencadangan Transact-SQL yang sesuai dengan mengklik tombol Skrip dan memilih tujuan skrip.

Setelah menyambungkan ke instans microsoft SQL Server Database Engine yang sesuai, di Object Explorer, perluas pohon server.
Perluas Database, dan pilih database pengguna atau perluas Database Sistem dan pilih database sistem.
Klik kanan database yang ingin Anda cadangkan, arahkan ke Tugas, lalu pilih Cadangkan....
Dalam kotak dialog Cadangkan Database , database yang Anda pilih muncul di daftar drop-down (yang bisa Anda ubah ke database lain di server).
Di daftar drop-down Jenis cadangan, pilih jenis cadangan - defaultnya adalah Penuh.

Penting

Anda harus melakukan setidaknya satu pencadangan database penuh sebelum dapat melakukan diferensial atau pencadangan log transaksi.
Di bawah Komponen cadangan, pilih Database.
Di bagian Tujuan , tinjau lokasi default untuk file cadangan (di .. Folder /mssql/data).

Anda dapat menggunakan daftar drop-down Cadangkan ke untuk memilih perangkat lain. Pilih Tambahkan untuk menambahkan objek cadangan dan atau tujuan. Anda dapat menghapus set cadangan di beberapa file untuk meningkatkan kecepatan pencadangan.

Untuk menghapus tujuan pencadangan, pilih dan pilih Hapus. Untuk melihat konten tujuan pencadangan yang sudah ada, pilih dan pilih Konten.
(opsional) Tinjau pengaturan lain yang tersedia di bawah halaman Opsi Media dan Opsi Cadangan.

Untuk informasi selengkapnya tentang berbagai opsi pencadangan, lihat halaman Umum, halaman Opsi media, dan halaman Opsi cadangan.
Pilih OK untuk memulai pencadangan.
Ketika pencadangan berhasil diselesaikan, pilih OK untuk menutup kotak dialog SQL Server Management Studio.

Informasi tambahan

Setelah membuat cadangan database lengkap, Anda dapat membuat cadangan database diferensial atau cadangan log transaksi.
(opsional) Anda dapat memilih kotak centang Pencadangan khusus salin untuk membuat cadangan khusus salinan. Cadangan khusus salinan adalah cadangan SQL Server yang independen dari urutan cadangan SQL Server konvensional. Untuk informasi selengkapnya, lihat Pencadangan Khusus Salin (SQL Server). Cadangan khusus salinan tidak tersedia untuk jenis cadangan Diferensial .
Opsi Timpa media dinonaktifkan pada halaman Opsi Media jika Anda mencadangkan ke URL.

Contoh

Untuk contoh berikut, buat database pengujian dengan 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 NOT NULL DEFAULT getdate()
   );
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

J. Pencadangan penuh ke disk ke lokasi default

Dalam contoh ini, SQLTestDB database akan dicadangkan ke disk di lokasi cadangan default.

Setelah menyambungkan ke instans microsoft SQL Server Database Engine yang sesuai, di Object Explorer, perluas pohon server.
Perluas Database, klik SQLTestDBkanan , arahkan ke Tugas, lalu pilih Cadangkan....
Pilih OK.
Ketika pencadangan berhasil diselesaikan, pilih OK untuk menutup kotak dialog SQL Server Management Studio.

Take SQL backup

B. Pencadangan penuh ke disk ke lokasi non-default

Dalam contoh ini, SQLTestDB database akan dicadangkan ke disk di lokasi pilihan Anda.

Setelah menyambungkan ke instans microsoft SQL Server Database Engine yang sesuai, di Object Explorer, perluas pohon server.
Perluas Database, klik SQLTestDBkanan , arahkan ke Tugas, lalu pilih Cadangkan....
Pada halaman Umum di bagian Tujuan pilih Disk dari daftar drop-down Cadangkan ke: .
Pilih Hapus hingga semua file cadangan yang ada telah dihapus.
Pilih Tambahkan dan kotak dialog Pilih Tujuan Cadangan akan terbuka.
Masukkan jalur dan nama file yang valid dalam kotak teks Nama file dan gunakan .bak sebagai ekstensi untuk menyederhanakan klasifikasi file ini.
Pilih OK lalu pilih OK lagi untuk memulai pencadangan.
Ketika pencadangan berhasil diselesaikan, pilih OK untuk menutup kotak dialog SQL Server Management Studio.

Change DB location

C. Membuat cadangan terenkripsi

Dalam contoh ini, SQLTestDB database akan dicadangkan dengan enkripsi ke lokasi cadangan default.

Setelah menyambungkan ke instans microsoft SQL Server Database Engine yang sesuai, di Object Explorer, perluas pohon server.
Perluas Database, perluas Database Sistem, klik masterkanan , dan pilih Kueri Baru untuk membuka jendela kueri dengan koneksi ke database Anda SQLTestDB .

Jalankan perintah berikut untuk membuat kunci master database dan sertifikat dalam master database.

-- Create the master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '23987hxJ#KL95234nl0zBe';  

-- 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 = '23987hxJ#KL95234nl0zBe'

-- Create the certificate encrypted by the master key
CREATE CERTIFICATE MyCertificate
WITH SUBJECT = 'Backup Cert', EXPIRY_DATE = '20201031';

Di Object Explorer, di simpul Database , klik SQLTestDBkanan , arahkan ke Tugas, lalu pilih Cadangkan....
Pada halaman Opsi Media, di bagian Timpa media pilih Cadangkan ke set media baru, dan hapus semua set cadangan yang ada.
Pada halaman Opsi Cadangan di bagian Enkripsi pilih kotak centang Enkripsi cadangan .
Dari daftar drop-down Algoritma, pilih AES 256.
Dari daftar drop-down Sertifikat atau Kunci asimetris pilih MyCertificate.
Pilih OK.

Encrypted backup

D. Mencadangkan ke Azure Blob Storage

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

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

Setelah menyambungkan ke instans microsoft SQL Server Database Engine yang sesuai, di Object Explorer, perluas pohon server.
Perluas Database, klik SQLTestDBkanan , arahkan ke Tugas, lalu pilih Cadangkan....
Pada halaman Umum di bagian Tujuan pilih URL dari daftar drop-down Cadangkan ke: .
Pilih Tambahkan dan kotak dialog Pilih Tujuan Cadangan akan terbuka.
Jika sebelumnya Anda telah mendaftarkan kontainer penyimpanan Azure yang ingin Anda gunakan dengan SQL Server Management Studio, pilih kontainer tersebut. Jika tidak, pilih Kontainer baru untuk mendaftarkan kontainer baru.
Dalam kotak dialog Koneksi ke Langganan Microsoft, masuk ke akun Anda.
Di kotak teks drop-down Pilih Akun Penyimpanan, pilih akun penyimpanan Anda.
Dalam kotak teks drop-down Pilih Kontainer Blob, pilih kontainer blob Anda.
Dalam kotak kalender drop-down Kedaluwarsa Kebijakan Akses Bersama, pilih tanggal kedaluwarsa untuk kebijakan akses bersama yang Anda buat dalam contoh ini.
Pilih Buat Kredensial untuk menghasilkan tanda tangan dan kredensial akses bersama di SQL Server Management Studio.
Pilih OK tutup kotak dialog Koneksi ke Langganan Microsoft.
Dalam kotak teks File Cadangan, ubah nama file cadangan (opsional).
Pilih OK untuk menutup kotak dialog Pilih tujuan cadangan.
Pilih OK untuk memulai pencadangan.
Ketika pencadangan berhasil diselesaikan, pilih OK untuk menutup kotak dialog SQL Server Management Studio.

Menggunakan T-SQL

Buat cadangan database lengkap dengan menjalankan BACKUP DATABASE pernyataan untuk membuat cadangan database lengkap, menentukan:

Nama database yang akan dicadangkan.
Perangkat cadangan tempat pencadangan database lengkap ditulis.

Sintaks Transact-SQL dasar untuk pencadangan database lengkap adalah:

DATABASE CADANGAN KE backup_device [ ,...n ] [ WITH with_options [ ,...o ] ] ;

Opsi	Deskripsi
database	Adalah database yang akan dicadangkan.
backup_device [ , ...n ]	Menentukan daftar dari 1 hingga 64 perangkat cadangan yang akan digunakan untuk operasi pencadangan. Anda dapat menentukan perangkat cadangan fisik, atau Anda dapat menentukan perangkat cadangan logis yang sesuai, jika sudah ditentukan. Untuk menentukan perangkat cadangan fisik, gunakan opsi DISK atau TAPE: { DISK \| TAPE } =physical_backup_device_name Untuk informasi selengkapnya, lihat Perangkat Cadangan (SQL Server).
DENGAN with_options [ , ...o ]	Digunakan untuk menentukan satu atau beberapa opsi, o. Untuk informasi tentang beberapa dasar dengan opsi, lihat langkah 2.

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

Opsi WITH kumpulan cadangan dasar:

{ KOMPRESI | NO_COMPRESSION }: Hanya di SQL Server 2008 (10.0.x) Enterprise dan yang lebih baru, menentukan apakah kompresi cadangan dilakukan pada cadangan ini, menggantikan default tingkat server.
ENKRIPSI (ALGORITMA, SERTIFIKAT SERVER | ASYMMETRIC KEY): Hanya di SQL Server 2014 atau yang lebih baru, tentukan algoritma enkripsi yang akan digunakan, dan kunci Sertifikat atau Asimetris yang akan digunakan untuk mengamankan enkripsi.
DESKRIPSI= { 'text'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 NAMA tidak ditentukan, nama tersebut kosong.

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

Untuk memformat media cadangan, gunakan opsi FORMAT :

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

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

Penting

Berhati-hatilah saat Anda menggunakan klausul BACKUP FORMAT pernyataan karena ini menghancurkan cadangan apa pun yang sebelumnya disimpan di media cadangan.

Contoh

Untuk contoh berikut, buat database pengujian dengan 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 NOT NULL DEFAULT GETDATE()
)
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

J. Mencadangkan ke perangkat disk

Contoh berikut mencadangkan database lengkap SQLTestDB ke disk, dengan menggunakan FORMAT untuk membuat set 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 mencadangkan database lengkap SQLTestDB ke pita, menambahkan cadangan ke cadangan sebelumnya.

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

C. Mencadangkan ke perangkat pita logis

Contoh berikut membuat perangkat cadangan logis untuk tape drive. Contoh kemudian mencadangkan database SQLTestDB lengkap ke perangkat tersebut.

-- Create a logical backup device,
-- SQLTestDB_Bak_Tape, for tape device \\.\tape0.
USE master;
GO
EXEC 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

Menggunakan PowerShell

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

Catatan

Contoh-contoh ini memerlukan modul SqlServer. Untuk menentukan apakah diinstal, jalankan Get-Module -Name SqlServer. Untuk menginstal, jalankan Install-Module -Name SqlServer di sesi administrator PowerShell.

Untuk informasi selengkapnya, lihat Penyedia PowerShell SQL Server.

Penting

Jika Anda membuka jendela PowerShell dari dalam SQL Server Management Studio untuk menyambungkan ke penginstalan SQL Server, Anda dapat menghilangkan bagian kredensial karena kredensial Anda di SSMS secara otomatis digunakan untuk membuat koneksi antara PowerShell dan instans SQL Server Anda.

Contoh

J. 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 Azure Blob Storage. Kebijakan akses tersimpan telah dibuat dengan hak baca, tulis, dan daftar. Info masuk SQL Server, https://<myStorageAccount>.blob.core.windows.net/<myContainer>, dibuat menggunakan Tanda Tangan Akses Bersama yang terkait dengan Kebijakan Akses Tersimpan. Perintah PowerShell menggunakan parameter BackupFile 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

Membuat Pencadangan Database Lengkap

Pembatasan dan batasan

Rekomendasi

Keamanan

Izin

Menggunakan SQL Server Management Studio

Informasi tambahan

Contoh

J. Pencadangan penuh ke disk ke lokasi default

B. Pencadangan penuh ke disk ke lokasi non-default

C. Membuat cadangan terenkripsi

D. Mencadangkan ke Azure Blob Storage

Menggunakan T-SQL

Contoh

J. Mencadangkan ke perangkat disk

B. Mencadangkan ke perangkat pita

C. Mencadangkan ke perangkat pita logis

Menggunakan PowerShell

Contoh

J. Pencadangan penuh (lokal)

B. Pencadangan penuh ke Azure

Tugas terkait

Saran dan Komentar

Saran dan Komentar

Sumber Daya Tambahan:

Membuat Pencadangan Database Lengkap

Pembatasan dan batasan

Rekomendasi

Keamanan

Izin

Menggunakan SQL Server Management Studio

Informasi tambahan

Contoh

J. Pencadangan penuh ke disk ke lokasi default

B. Pencadangan penuh ke disk ke lokasi non-default

C. Membuat cadangan terenkripsi

D. Mencadangkan ke Azure Blob Storage

Menggunakan T-SQL

Contoh

J. Mencadangkan ke perangkat disk

B. Mencadangkan ke perangkat pita

C. Mencadangkan ke perangkat pita logis

Menggunakan PowerShell

Contoh

J. Pencadangan penuh (lokal)

B. Pencadangan penuh ke Azure

Tugas terkait

Konten terkait

Saran dan Komentar

Saran dan Komentar

Sumber Daya Tambahan: