差异备份,日志备份还原

时间:2022-01-20 05:04:04

--差异备份,日志备份还原 

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'

),