SQL Server 備份與還原實驗筆記

2020-12-28

為求對於備份與還原機制有更深入的認識，自己動手設計的實驗筆記 🧪

SQL Server Logo

說明

實驗流程

  1. DB1 Backup to c.bak (2901 KB)
  2. DB1 Update
  3. DB2 Backup to c.bak (5736 KB)
  4. DB1 Restore FROM Database [Result : Work]
  5. DB1 Update
  6. DB1 Restore FROM c.bak [Result : Work]
  7. DB2 Update
  8. DB2 Restore FROM Database [Result : Fail, mdf cannot be overwritten.]
  9. DB2 Restore FROM c.bak With Specifi File [Result : Work]
  10. DB1 Backup to c.bak (8699 KB)
  11. DB2 Update
  12. DB2 Restore FROM c.bak [Result : Work]
  13. Truncate DB2
  14. DB2 Update
  15. DB2 Differential Backup to c.bak (9870 KB)
  16. DB2 Update2
  17. DB2 Update3
  18. DB2 Differential Backup to c.bak (11042 KB)
  19. DB2 Update4
  20. DB2 Update5
  21. DB2 Differential Backup to d.bak (1173 KB)
  22. DB2 Update Useless Data
  23. DB2 Restore FROM c.bak, d.bak [Result : Work]
  24. DB2 Update Useless Data
  25. 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 指令後，不論在學習或者問題排除上都有更順利的進展。

藉由本次實驗得到以下發現：

  1. 如何穿越在各項差異備份之間進行還原
  2. 不同的資料庫可以共用 bak file
  3. bak file 會將不同的備份資料累積為其檔案內容
  4. 共用 bak file 不是一個理想的主意，會讓使用 GUI 預設還原操作選用錯誤備份組 (Backup Set) 並發生問題
  5. 卸離資料庫 (將資料庫從DBMS卸離，需要重新附加才能使用) 與 離線資料庫 (另資料庫成為離線模式，重新上線即可使用)的差別；兩者都可以用於驅逐使用者用途 😁
#Database #SQLServer