為求對於備份與還原機制有更深入的認識,自己動手設計的實驗筆記 🧪
說明
實驗流程
- DB1 Backup to c.bak (2901 KB)
- DB1 Update
- DB2 Backup to c.bak (5736 KB)
- DB1 Restore FROM Database [Result : Work]
- DB1 Update
- DB1 Restore FROM c.bak [Result : Work]
- DB2 Update
- DB2 Restore FROM Database [Result : Fail, mdf cannot be overwritten.]
- DB2 Restore FROM c.bak With Specifi File [Result : Work]
- DB1 Backup to c.bak (8699 KB)
- DB2 Update
- DB2 Restore FROM c.bak [Result : Work]
- Truncate DB2
- DB2 Update
- DB2 Differential Backup to c.bak (9870 KB)
- DB2 Update2
- DB2 Update3
- DB2 Differential Backup to c.bak (11042 KB)
- DB2 Update4
- DB2 Update5
- DB2 Differential Backup to d.bak (1173 KB)
- DB2 Update Useless Data
- DB2 Restore FROM c.bak, d.bak [Result : Work]
- DB2 Update Useless Data
- DB2 Restore FROM c.bak, Restore to Step 12 + Step 15 [Result : Work]
SQL Script
USE master;
ALTER DATABASE [DB1] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE [DB1];
ALTER DATABASE [DB2] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE [DB2];
CREATE DATABASE DB1;
CREATE DATABASE DB2;
USE DB1;
CREATE TABLE dbo.T1
(
C1 nchar(10) NULL,
C2 nchar(10) NULL
) ON [PRIMARY]
TRUNCATE Table dbo.T1;
INSERT INTO dbo.T1 VALUES (1, 2);
INSERT INTO dbo.T1 VALUES (3, 4);
/* Step 1 */
BACKUP DATABASE [DB1] TO DISK = N'C:\Users\BackupPath\\c.bak'
WITH NOFORMAT, NOINIT,
NAME = N'DB1-完整 資料庫 備份', SKIP, NOREWIND, NOUNLOAD, STATS = 10
USE DB2;
CREATE TABLE dbo.T2
(
C1 nchar(10) NULL,
C2 nchar(10) NULL
) ON [PRIMARY]
TRUNCATE Table dbo.T2;
INSERT INTO dbo.T2 VALUES (22, 33);
INSERT INTO dbo.T2 VALUES (55, 66);
/* Step 3 */
BACKUP DATABASE [DB2] TO DISK = N'C:\Users\BackupPath\\c.bak'
WITH NOFORMAT, NOINIT,
NAME = N'DB2-完整 資料庫 備份', SKIP, NOREWIND, NOUNLOAD, STATS = 10
USE DB1;
SELECT * FROM T1;
INSERT INTO dbo.T1 VALUES (0, 1);
INSERT INTO dbo.T1 VALUES (1, 0);
/* Step 6 */
USE [master];
ALTER DATABASE [DB1] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE [DB1] FROM DISK = N'C:\Users\BackupPath\\c.bak'
WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 5;
ALTER DATABASE [DB1] SET MULTI_USER;
USE DB2;
INSERT INTO dbo.T2 VALUES (0, 2);
INSERT INTO dbo.T2 VALUES (2, 0);
SELECT * FROM T2;
ALTER DATABASE [DB2] SET MULTI_USER;
USE MASTER;
ALTER DATABASE [DB2] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
/* Step 8 */
/* GUI Restoring Fail With Wrong Backup Set
RESTORE DATABASE [DB2] FROM DISK = N'C:\Users\BackupPath\\c.bak'
WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 5
*/
/* Step 9 */
RESTORE DATABASE [DB2] FROM DISK = N'C:\Users\BackupPath\c.bak'
WITH FILE = 2, NOUNLOAD, REPLACE, STATS = 5;
ALTER DATABASE [DB2] SET MULTI_USER;
USE DB2;
SELECT * FROM dbo.T2;
TRUNCATE TABLE dbo.T2;
INSERT INTO dbo.T2 VALUES (11, 11);
/* Step 15 */
BACKUP DATABASE [DB2] TO DISK = N'C:\Users\BackupPath\\c.bak' WITH DIFFERENTIAL ,
NOFORMAT, NOINIT,
NAME = N'DB2-資料庫差異備份', SKIP, NOREWIND, NOUNLOAD, STATS = 10
INSERT INTO dbo.T2 VALUES (22, 22);
INSERT INTO dbo.T2 VALUES (33, 33);
/* Step 18 */
BACKUP DATABASE [DB2] TO DISK = N'C:\Users\BackupPath\\c.bak' WITH DIFFERENTIAL ,
NOFORMAT, NOINIT,
NAME = N'DB2-資料庫差異備份', SKIP, NOREWIND, NOUNLOAD, STATS = 10
/* Step 21 */
INSERT INTO dbo.T2 VALUES (44, 44);
INSERT INTO dbo.T2 VALUES (55, 55);
BACKUP DATABASE [DB2] TO DISK = N'C:\Users\BackupPath\\d.bak' WITH DIFFERENTIAL ,
NOFORMAT, NOINIT,
NAME = N'DB2-資料庫差異備份', SKIP, NOREWIND, NOUNLOAD, STATS = 10
/* Step 25 */
USE MASTER;
ALTER DATABASE [DB2] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE [DB2] FROM DISK = N'C:\Users\BackupPath\c.bak'
WITH FILE = 2, NORECOVERY, NOUNLOAD, REPLACE, STATS = 5
RESTORE DATABASE [DB2] FROM DISK = N'C:\Users\BackupPath\c.bak'
WITH FILE = 4, NOUNLOAD, REPLACE, STATS = 5;
ALTER DATABASE [DB2] SET MULTI_USER;
Conclusion
使用 SSMS 進行備份與還原功能容易被便利的 GUI 介面混淆觀念,藉由透過 SQL Profiler 紀錄 GUI 對應的操作,從而將 GUI 操作還原為 T-SQL 指令後,不論在學習或者問題排除上都有更順利的進展。
藉由本次實驗得到以下發現:
- 如何穿越在各項差異備份之間進行還原
- 不同的資料庫可以共用 bak file
- bak file 會將不同的備份資料累積為其檔案內容
- 共用 bak file 不是一個理想的主意,會讓使用 GUI 預設還原操作選用錯誤備份組 (Backup Set) 並發生問題
- 卸離資料庫 (將資料庫從DBMS卸離,需要重新附加才能使用) 與 離線資料庫 (另資料庫成為離線模式,重新上線即可使用)的差別;兩者都可以用於驅逐使用者用途 😁