讓新手們了解一下備份順序

--1、塔建環境(生成測試數據和備份文件)

/* 測試環境： Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) */ USE master go --創建測試 CREATE DATABASE db GOUSE db GO CREATE TABLE Test(ID INT); --生成備份文件 0.bak BACKUP DATABASE db TO DISK='d:\0.bak' WITH FORMAT GO --1 INSERT test SELECT 1 go --生成備份文件 1.trn BACKUP LOG db TO DISK='d:\1.trn' WITH FORMAT go --2 INSERT test SELECT 2 go --生成備份文件 2.trn BACKUP LOG db TO DISK='d:\2.trn' WITH FORMAT go --3 INSERT test SELECT 3 go --生成備份文件 3.dif BACKUP DATABASE db TO DISK='d:\3.dif' WITH FORMAT,DIFFERENTIAL go --4 INSERT test SELECT 4 go --生成備份文件 4.trn BACKUP LOG db TO DISK='d:\4.trn' WITH FORMAT --5 INSERT test SELECT 5 go --生成備份文件 5.dif BACKUP DATABASE db TO DISK='d:\5.dif' WITH FORMAT,DIFFERENTIAL --6 INSERT test SELECT 6 --生成備份文件 6.trn BACKUP LOG db TO DISK='d:\6.trn' WITH FORMAT--7 INSERT test SELECT 7--生成備份文件 7.trn BACKUP LOG db TO DISK='d:\7.trn' WITH FORMATGO -- SELECT * FROM dbo.Test /* ID 1 2 3 4 5 6 7 */

2、查看備份文件關系

SELECT a.database_name,CASE a.typeWHEN 'D' THEN 'full'WHEN 'I' THEN 'differential'WHEN 'L' THEN 'log'WHEN 'F' THEN 'file / filegroup'WHEN 'G' THEN 'differential file'WHEN 'P' THEN 'partial'WHEN 'Q' THEN 'differential partial'END AS [type] --備份類型。可以是：D = 數據庫 I = 差異數據庫 L = 日志 F = 文件或文件組 G = 差異文件 P = 部分 Q = 差異部分 可以為 NULL。,b.physical_device_name,a.first_lsn,a.last_lsn,a.differential_base_lsn FROM msdb.dbo.backupset aINNER JOIN msdb.dbo.backupmediafamily b ON a.media_set_id = b.media_set_id WHERE a.database_name = 'db' ORDER BY a.backup_start_date,b.family_sequence_number

3、還原順序

USE master go --1. 恢復時使用錯誤的日志順序 --1.1 RESTORE DATABASE db FROM DISK='d:\0.bak' WITH REPLACE;--查看 SELECT * FROM db.dbo.Test /* ID */ go --1.2 RESTORE DATABASE db FROM DISK='d:\0.bak' WITH REPLACE,NORECOVERY RESTORE LOG db FROM DISK='d:\1.trn' --查看 SELECT * FROM db.dbo.Test /* ID 1 */ go --1.3 RESTORE DATABASE db FROM DISK='d:\0.bak' WITH REPLACE,NORECOVERY RESTORE LOG db FROM DISK='d:\1.trn' WITH NORECOVERY RESTORE LOG db FROM DISK='d:\2.trn' --查看 SELECT * FROM db.dbo.Test /* ID 1 2 */ go --1.4 RESTORE DATABASE db FROM DISK='d:\0.bak' WITH REPLACE,NORECOVERY RESTORE DATABASE db FROM DISK='d:\3.dif' --查看 SELECT * FROM db.dbo.Test /* ID 1 2 3 */ go --1.5 --1.5.1 RESTORE DATABASE db FROM DISK='d:\0.bak' WITH REPLACE,NORECOVERY RESTORE DATABASE db FROM DISK='d:\3.dif' WITH NORECOVERY RESTORE LOG db FROM DISK='d:\4.trn' --查看 SELECT * FROM db.dbo.Test /* ID 1 2 3 4 */ GO --1.5.2 RESTORE DATABASE db FROM DISK='d:\0.bak' WITH REPLACE,NORECOVERY RESTORE DATABASE db FROM DISK='d:\1.trn' WITH NORECOVERY RESTORE DATABASE db FROM DISK='d:\2.trn' WITH NORECOVERY RESTORE LOG db FROM DISK='d:\4.trn' --查看 SELECT * FROM db.dbo.Test /* ID 1 2 3 4 */ go --1.6 RESTORE DATABASE db FROM DISK='d:\0.bak' WITH REPLACE,NORECOVERY RESTORE DATABASE db FROM DISK='d:\5.dif' --查看 SELECT * FROM db.dbo.Test /* ID 1 2 3 4 5 */ go --1.7 --1.7.1 RESTORE DATABASE db FROM DISK='d:\0.bak' WITH REPLACE,NORECOVERY RESTORE DATABASE db FROM DISK='d:\5.dif' WITH NORECOVERY RESTORE LOG db FROM DISK='d:\6.trn' --查看 SELECT * FROM db.dbo.Test /* ID 1 2 3 4 5 6 */ go --1.7.2 RESTORE DATABASE db FROM DISK='d:\0.bak' WITH REPLACE,NORECOVERY RESTORE LOG db FROM DISK='d:\1.trn' WITH NORECOVERY RESTORE LOG db FROM DISK='d:\2.trn' WITH NORECOVERY RESTORE LOG db FROM DISK='d:\4.trn' WITH NORECOVERY RESTORE LOG db FROM DISK='d:\6.trn' --查看 SELECT * FROM db.dbo.Test /* ID 1 2 3 4 5 6 */ go --1.8 --1.8.1 RESTORE DATABASE db FROM DISK='d:\0.bak' WITH REPLACE,NORECOVERY RESTORE DATABASE db FROM DISK='d:\5.dif' WITH NORECOVERY RESTORE LOG db FROM DISK='d:\6.trn' WITH NORECOVERY RESTORE LOG db FROM DISK='d:\7.trn' --查看 SELECT * FROM db.dbo.Test /* ID 1 2 3 4 5 6 7 */ go --1.8.2 RESTORE DATABASE db FROM DISK='d:\0.bak' WITH REPLACE,NORECOVERY RESTORE LOG db FROM DISK='d:\1.trn' WITH NORECOVERY RESTORE LOG db FROM DISK='d:\2.trn' WITH NORECOVERY RESTORE LOG db FROM DISK='d:\4.trn' WITH NORECOVERY RESTORE LOG db FROM DISK='d:\6.trn' WITH NORECOVERY RESTORE LOG db FROM DISK='d:\7.trn' --查看 SELECT * FROM db.dbo.Test /* ID 1 2 3 4 5 6 7 */

轉載于:https://www.cnblogs.com/wuxi88/p/5466741.html

總結

以上是生活随笔為你收集整理的数据库备份还原顺序关系(环境:Microsoft SQL Server 2008 R2)的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。