T-SQL 备份和还原

核心提示： Code/*Author:Terry.Sai.M.J 浪客 Location:BeiJingDateTime:GETDATE()Description:使用NO_TRUNCATE 备份损坏的DB*/IF DB_ID('db') IS NOT NULL DROP DATABASE

Code

/*
Author:Terry.Sai.M.J 浪客
Location:BeiJing
DateTime:GETDATE()
Description:使用NO_TRUNCATE 备份损坏的DB
*/

IF DB_ID('db') IS NOT NULL
　　　 DROP DATABASE db;
GO

CREATE DATABASE db
ON PRIMARY
(
　　　 NAME=db_data,
　　　 FILENAME='c:\db_data.mdf'
),
(
　　　 NAME=db_data1,
　　　 FILENAME='c:\db_data1.ndf'
)
LOG ON
(
　　　 NAME=db_log,
　　　 FILENAME='c:\db_log.log'
);
GO

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

BACKUP DATABASE db TO DISK='c:\1.bak' WITH FORMAT;
GO

INSERT INTO db.dbo.T DEFAULT VALUES;
GO

--CMD-> net stop mssqlserver
--删除c:\db_data1.ndf 模拟破坏
--CMD-> net start mssqlserver

BACKUP LOG db TO DISK='c:\2.bak' WITH FORMAT,NO_TRUNCATE
GO

RESTORE DATABASE db FROM DISK='c:\1.bak' WITH NORECOVERY;
GO

RESTORE LOG db FROM DISK='c:\2.bak' WITH RECOVERY;
GO

SELECT * FROM db.dbo.T

DROP DATABASE db;
GO

Code

/*
Author:Terry.Sai.M.J 浪客
Location:BeiJing
DateTime:GETDATE()
Description:差异备份,日志备份还原
*/

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='c:\1.bak' WITH FORMAT
GO

INSERT INTO db.dbo.T DEFAULT VALUES
GO

BACKUP DATABASE db TO DISK='c:\2.bak' WITH FORMAT,DIFFERENTIAL
GO

INSERT INTO db.dbo.T DEFAULT VALUES
GO

BACKUP LOG db TO DISK='c:\3.bak' WITH FORMAT
GO

DROP DATABASE db;

RESTORE DATABASE db FROM DISK='c:\1.bak' WITH RECOVERY,REPLACE;
GO

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

DROP DATABASE db;
GO

RESTORE DATABASE db FROM DISK='c:\1.bak' WITH NORECOVERY,REPLACE;
GO

RESTORE DATABASE db FROM DISK='c:\2.bak' WITH RECOVERY;
GO

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

DROP DATABASE db;

RESTORE DATABASE db FROM DISK='c:\1.bak' WITH NORECOVERY,REPLACE;
GO

RESTORE DATABASE db FROM DISK='c:\2.bak' WITH NORECOVERY;
GO

RESTORE LOG db FROM DISK='c:\3.bak' WITH RECOVERY;
GO

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

DROP DATABASE db;