差異備份,日志備份還原

?

IF?DB_ID('db')?IS?NOT?NULL

DROP?DATABASE?db

GO

?

CREATE?DATABASE?db

GO

?

CREATE?TABLE?db.dbo.T(ID?INT?PRIMARY?KEY?IDENTITY(1,1));

GO

?

BACKUP?DATABASE?db?TO?DISK='d:/1.bak'?WITH?FORMAT

GO

?

INSERT?INTO?db.dbo.T?DEFAULT?VALUES

GO

?

BACKUP?DATABASE?db?TO?DISK='d:/2.bak'?WITH?FORMAT,DIFFERENTIAL

GO

?

INSERT?INTO?db.dbo.T?DEFAULT?VALUES

GO

?

BACKUP?LOG?db?TO?DISK='d:/3.bak'?WITH?FORMAT

GO

?

DROP?DATABASE?db;

?

RESTORE?DATABASE?db?FROM?DISK='d:/1.bak'?WITH?RECOVERY,REPLACE;

GO

?

SELECT?COUNT(*)?FROM?db.dbo.T;

GO

?

DROP?DATABASE?db;

GO

?

RESTORE?DATABASE?db?FROM?DISK='d:/1.bak'?WITH?NORECOVERY,REPLACE;

GO

?

RESTORE?DATABASE?db?FROM?DISK='d:/2.bak'?WITH?RECOVERY;

GO

?

SELECT?COUNT(*)?FROM?db.dbo.T;

?

DROP?DATABASE?db;

?

RESTORE?DATABASE?db?FROM?DISK='d:/1.bak'?WITH?NORECOVERY,REPLACE;

GO

?

RESTORE?DATABASE?db?FROM?DISK='d:/2.bak'?WITH?NORECOVERY;

GO

?

RESTORE?LOG?db?FROM?DISK='d:/3.bak'?WITH?RECOVERY;

GO

?

SELECT?COUNT(*)?FROM?db.dbo.T;

?

DROP?DATABASE?db;

?

使用日志恢復數據庫

?

IF?DB_ID('db')?IS?NOT?NULL

DROP?DATABASE?db;

GO

?

CREATE?DATABASE?db;

GO

?

CREATE?TABLE?db.dbo.T(ID?INT?PRIMARY?KEY?IDENTITY(1,1));

GO

?

BACKUP?DATABASE?db?TO?DISK='d:/1.bak'?WITH?FORMAT;

GO

?

INSERT?INTO?db.dbo.T?DEFAULT?VALUES;

GO

?

BACKUP?LOG?db?TO?DISK='d:/2.bak'?WITH?FORMAT;

GO

?

INSERT?INTO?db.dbo.T?DEFAULT?VALUES;

GO

?

BACKUP?LOG?db?TO?DISK='d:/3.bak'?WITH?FORMAT;

GO

?

RESTORE?DATABASE?db?FROM?DISK='d:/1.bak'?WITH?RECOVERY,REPLACE;

GO

?

SELECT?*?FROM?db.dbo.T

GO

?

RESTORE?DATABASE?db?FROM?DISK='d:/1.bak'?WITH?NORECOVERY,REPLACE;

GO

?

RESTORE?LOG?db?FROM?DISK='d:/2.bak'?WITH?RECOVERY;

GO

?

SELECT?*?FROM?db.dbo.T

GO

?

RESTORE?DATABASE?db?FROM?DISK='d:/1.bak'?WITH?NORECOVERY,REPLACE;

GO

?

RESTORE?LOG?db?FROM?DISK='d:/2.bak'?WITH?NORECOVERY;

GO

?

RESTORE?LOG?db?FROM?DISK='d:/3.bak'?WITH?RECOVERY;

GO

?

SELECT?*?FROM?db.dbo.T

GO

DROP?DATABASE?db;

?

?

恢復到特定的備份日期

?

?

IF?DB_ID('db')?IS?NOT?NULL

DROP?DATABASE?db;

GO

?

CREATE?DATABASE?db;

GO

?

CREATE?TABLE?db.dbo.T(ID?INT?PRIMARY?KEY?IDENTITY(1,1));

GO

?

BACKUP?DATABASE?db?TO?DISK='d:/1.bak'?WITH?FORMAT;

GO

?

INSERT?INTO?db.dbo.T?DEFAULT?VALUES;

GO

?

WAITFOR?DELAY?'00:00:01';

?

DECLARE?@Datetime?BINARY(128);

SET?@Datetime=CAST(GETDATE()?AS?BINARY(128));

SET?CONTEXT_INFO?@Datetime

GO

?

INSERT?INTO?db.dbo.T?DEFAULT?VALUES;

GO

?

BACKUP?LOG?db?TO?DISK='d:/2.bak'?WITH?FORMAT;

GO

?

RESTORE?DATABASE?db?FROM?DISK='d:/1.bak'?WITH?NORECOVERY,REPLACE;

GO

?

DECLARE?@Now?DATETIME;

SET?@Now=DATEADD(SECOND,-1,(SELECT?CAST(CONTEXT_INFO()?AS?DATETIME)));

?

RESTORE?LOG?db?FROM?DISK='d:/2.bak'?WITH?RECOVERY,STOPAT=@Now;

GO

?

SELECT?*?FROM?db.dbo.T

?

DROP?DATABASE?db;

GO

?

?

還原到標識事務

--Create?test?database

CREATE?DATABASE?db?

GO

?

--Create?test?table?on?database?named?db

CREATE?TABLE?db.dbo.T?(ID?INT?PRIMARY?KEY);

GO

?

--Create?full?backup?to?disk?'F:/Documents?and?Settings/Administrator/桌面/1.bak'

BACKUP?DATABASE?db?TO?DISK='F:/Documents?and?Settings/Administrator/桌面/1.bak'?WITH?FORMAT;

GO

?

--Begin?a?marked?transaction?"Tran1"

BEGIN?TRAN?Tran1?WITH?MARK

?

INSERT?INTO?db.dbo.T?SELECT?1

?

COMMIT?TRAN?Tran1

?

--Backup?the?transaction?log?to?disk?'F:/Documents?and?Settings/Administrator/桌面/2.bak'

BACKUP?LOG?db?TO?DISK='F:/Documents?and?Settings/Administrator/桌面/2.bak'?WITH?FORMAT

GO

?

DROP?DATABASE?db;

GO

?

RESTORE?DATABASE?db?FROM?DISK='F:/Documents?and?Settings/Administrator/桌面/1.bak'?WITH?NORECOVERY;

GO

?

RESTORE?LOG?db?FROM?DISK='F:/Documents?and?Settings/Administrator/桌面/2.bak'?WITH?STOPBEFOREMARK='Tran1';

GO

?

SELECT?COUNT(*)?FROM?db.dbo.T

?

?

DROP?DATABASE?db;

GO

?

RESTORE?DATABASE?db?FROM?DISK='F:/Documents?and?Settings/Administrator/桌面/1.bak'?WITH?NORECOVERY;

GO

?

RESTORE?LOG?db?FROM?DISK='F:/Documents?and?Settings/Administrator/桌面/2.bak'?WITH?STOPATMARK='Tran1';

GO

?

SELECT?COUNT(*)?FROM?db.dbo.T

?

DROP?DATABASE?db

?

?

數據庫的在線還原（讀寫文件組完整恢復模式）

?

IF?DB_ID('db')?IS?NOT?NULL

DROP?DATABASE?db;

GO

?

CREATE?DATABASE?db

ON?PRIMARY

(

NAME=db_data,

FILENAME='c:/1.mdf'

),

FILEGROUP?FG

(

NAME=db_data_fg,

FILENAME='c:/2.ndf'

)

LOG?ON

(

NAME=db_log,

FILENAME='c:/1.ldf'

)

GO

?

--在主文件組下創建表T

CREATE?TABLE?db.dbo.T(ID?INT?PRIMARY?KEY)?ON?[PRIMARY];

?

--插入記錄

INSERT?INTO?db.dbo.T?SELECT?1

GO

?

--主文件組在線,那么就視為數據庫在線

--創建文件db_data_fg備份

BACKUP?DATABASE?db?FILE='db_data_fg'?TO?DISK='c:/1.bak'?WITH?FORMAT

?

--進行在先還原,指定NORECOVERY?以后,就只能按順序進行前滾,同時文件離線還原狀態.

RESTORE?DATABASE?db?FILE='db_data_fg'?FROM?DISK='c:/1.bak'?WITH?NORECOVERY

?

--創建表失敗,因為離線

CREATE?TABLE?db.dbo.T1(ID?INT?PRIMARY?KEY)?ON?fg;

?

--主文件組不受影響,因為只是FG離線還原,只有企業版才支持哈.這種情況下生產環境中,不至于數據庫全部掛了.

SELECT?*?FROM?db.dbo.T

?

--備份日志,使用COPY_ONLY,因為是要獲取的日志是離線狀態時候的,COPY_ONLY僅復制備份是在SQL?Server?2005?中引入的，用于在執行特殊目的的備份（例如在聯機文件還原前備份日志）時使用

--如果是只讀文件的話,明顯不會出現差異數據,所以不需要日志備份了

--簡單恢復模式的話,日志都不能備份,所以也是一樣

--順便提一句,如果數據庫是文件損壞,而且損壞時候都在線,要使用NO_TRUNCATE獲取日志

BACKUP?LOG?db?TO?DISK='c:/2.bak'?WITH?FORMAT,COPY_ONLY;

?

--恢復LOG

RESTORE?LOG?db?FROM?DISK='c:/2.bak'?WITH?RECOVERY

?

--FG終于在線了,恭喜下,創建一個T1表

CREATE?TABLE?db.dbo.T1(ID?INT?PRIMARY?KEY)?ON?fg;

GO

?

DROP?DATABASE?db;

?

?

數據庫的段落還原（完整恢復模式）

?

IF?DB_ID('db')?IS?NOT?NULL

DROP?DATABASE?db

GO

?

--創建包含多個文件組的數據庫db

CREATE?DATABASE?db

ON?PRIMARY?

(

NAME=db_data,

FILENAME='c:/db_data.mdf'

),

FILEGROUP?A

(

NAME=db_data_a,

FILENAME='c:/db_data_a.ndf'

),

FILEGROUP?B

(

NAME=db_data_b,

FILENAME='c:/db_data_b.ndf'