Membuat cadangan database lengkap

Berlaku untuk:SQL Server

Artikel ini menjelaskan cara membuat cadangan database lengkap di SQL Server dengan menggunakan SQL Server Management Studio, 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.

Keterbatasan

• 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 pencadangan database lengkap dengan menggunakan prosedur sistem tersimpan 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 terakumulasi dengan cepat, yang mengakibatkan log kesalahan besar, yang membuat menemukan pesan lain 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 Mengatur bendera pelacakan dengan DBCC TRACEON.

Keamanan

TRUSTWORTHY diatur ke OFF pada cadangan database. Untuk informasi tentang cara mengatur TRUSTWORTHY ke ON, lihat OPSI UBAH KUMPULAN DATABASE.

Dimulai dengan 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 izin secara default diberikan kepada anggota-anggota dari sysadmin fixed server role dan db_owner serta db_backupoperator peran database tetap.

Masalah kepemilikan dan izin pada file fisik perangkat cadangan dapat mengganggu operasi pencadangan. Layanan SQL Server harus membaca dari 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 Transact-SQL BACKUP yang sesuai dengan memilih tombol Skrip lalu memilih tujuan skrip.

1. Setelah menyambungkan ke instans mesin database SQL Server yang sesuai, di Object Explorer, perluas pohon server.

2. Perluas Database, dan pilih database pengguna atau perluas Database Sistem dan pilih database sistem.

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

4. Dalam dialog Cadangkan Database , database yang Anda pilih 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 log diferensial atau transaksi.

6. Di bawah Komponen cadangan, pilih Database.

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

   Anda dapat menggunakan daftar 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 tujuan tersebut lalu pilih Hapus. Untuk melihat konten tujuan pencadangan yang sudah ada, pilih lalu pilih Konten.

8. (Opsional) Tinjau pengaturan lain yang tersedia pada halaman Opsi Media dan Opsi Cadangan .

   Untuk informasi selengkapnya tentang berbagai opsi pencadangan, lihat Mencadangkan Database (Halaman Umum), Database Cadangan (halaman Opsi Media), dan Database Cadangan (Halaman Opsi Cadangan).

9. Pilih OK untuk memulai proses pencadangan.

10. Ketika pencadangan berhasil diselesaikan, pilih OK untuk menutup 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. Cadangan khusus salinan tidak tersedia untuk jenis cadangan Diferensial .

• Opsi Timpa media dinonaktifkan pada Opsi Media ketika Anda mencadangkan ke suatu 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

Sebuah. Pencadangan penuh ke disk ke lokasi default

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

1. Setelah menyambungkan ke instans mesin database SQL Server yang sesuai, di Object Explorer, perluas pohon server.

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

3. Pilih OK.

4. Ketika pencadangan berhasil diselesaikan, pilih OK untuk menutup dialog SQL Server Management Studio.

B. Pencadangan penuh ke disk ke lokasi nondefault

Dalam contoh ini, SQLTestDB database dicadangkan ke disk di lokasi yang Anda pilih.

1. Setelah menyambungkan ke instans mesin database SQL Server yang sesuai, di Object Explorer, perluas pohon server.

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

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

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

5. Pilih Tambahkan. Dialog Pilih Tujuan Pencadangan 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 pencadangan.

8. Ketika pencadangan berhasil diselesaikan, pilih OK untuk menutup dialog SQL Server Management Studio.

C. Membuat cadangan terenkripsi

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

1. Setelah menyambungkan ke instans mesin database SQL Server yang sesuai, di Object Explorer, 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 dalam 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 set media baru, dan hapus semua set 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. Contohnya ditulis dengan asumsi bahwa Anda sudah memiliki akun penyimpanan dengan kontainer blob. Contoh membuat tanda tangan akses bersama. Contoh gagal jika kontainer memiliki tanda tangan akses bersama yang ada.

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

1. Setelah menyambungkan ke instans mesin database SQL Server yang sesuai, di Object Explorer, perluas pohon server.

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

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

4. Pilih Tambahkan. Dialog Pilih Tujuan Pencadangan terbuka.

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

6. Dalam dialog Sambungkan 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. Dalam kotak kalender Kedaluwarsa Kebijakan Akses Bersama , pilih tanggal kedaluwarsa untuk kebijakan akses bersama yang Anda buat dalam contoh ini.

10. Pilih Buat Kredensial untuk menghasilkan tanda tangan dan kredensial akses bersama di SQL Server Management Studio.

11. Pilih OK tutup dialog Sambungkan ke Langganan Microsoft .

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

13. Pilih OK untuk menutup dialog Pilih tujuan cadangan .

14. Pilih OK untuk memulai proses pencadangan.

15. Ketika pencadangan berhasil diselesaikan, pilih OK untuk menutup dialog SQL Server Management Studio.

Catatan

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

Gunakan Transact-SQL

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

• 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>	Database yang akan dicadangkan.
<backup_device> [ , ...n ]	Menentukan daftar antara 1 dan 64 perangkat cadangan yang akan digunakan untuk operasi pencadangan. Anda dapat menentukan perangkat cadangan fisik, atau Anda dapat menentukan perangkat pencadangan logis yang sesuai, jika sudah ditentukan. Untuk menentukan perangkat cadangan fisik, gunakan DISK opsi atau TAPE : { DISK | TAPE } =physical_backup_device_name Untuk informasi selengkapnya, lihat Perangkat Cadangan (SQL Server).
WITH <with_options> [ , ...o ]	Digunakan untuk menentukan satu atau beberapa opsi, o. Informasi tentang beberapa opsi dasar WITH 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 set WITH cadangan dasar:

• { KOMPRESI | NO_COMPRESSION }. Di SQL Server 2008 (10.0.x) Enterprise dan yang lebih baru saja, menentukan apakah kompresi cadangan dilakukan pada cadangan, mengambil alih default tingkat server.
• ENKRIPSI (ALGORITMA, SERTIFIKAT SERVER | KUNCI ASIMETRIS). Hanya di SQL Server 2014 atau yang lebih baru, menentukan algoritma enkripsi yang akan digunakan, dan sertifikat atau kunci asimetris yang akan 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 NAME tidak ditentukan, 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 } ]

FORMAT Gunakan klausa 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 Anda menggunakan FORMAT klausul BACKUP pernyataan karena opsi 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

Sebuah. Cadangkan ke perangkat disk

Contoh berikut mencadangkan database lengkap SQLTestDB ke disk. Ini 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. Ini menambahkan cadangan ke cadangan 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 kemudian mencadangkan database lengkap SQLTestDB 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

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

Catatan

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

Untuk informasi selengkapnya, lihat Provider PowerShell SQL Server.

Penting

Jika Anda membuka jendela PowerShell dari dalam SQL Server Management Studio (SSMS) untuk menyambungkan ke instans 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

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 telah dibuat dengan hak baca, tulis, dan daftar. Info masuk 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 Directory
• Memulihkan cadangan database di bawah model pemulihan sederhana (Transact-SQL)
• 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)
• sp_addumpdevice (T-SQL)
• BACKUP (Transact-SQL)
• Mencadangkan Database (Halaman Umum)
• Pencadangan Database (Halaman Opsi Cadangan)
• Cadangan diferensial (SQL Server)
• Pencadangan database lengkap (SQL Server)