T-SQL 备份和还原

核心提示： Code--Create test databaseCREATE DATABASE db GO--Create test table on database named dbCREATE TABLE db.dbo.T (ID INT PRIMARY KEY);GO--Create full ba

Code

--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

Code

/*
Author:Terry.Sai.M.J 浪客
Location:BeiJing
DateTime:GETDATE()
Description:使用同一个媒体簇来保存不同数据库的备份
*/
CREATE DATABASE db;
GO

CREATE DATABASE db1;
GO

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

INSERT INTO db.dbo.T DEFAULT VALUES

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

INSERT INTO db1.dbo.T DEFAULT VALUES

--备份db,先清空媒体标头等其他媒体信息
BACKUP DATABASE db TO DISK='c:\1.bak' WITH FORMAT;

--备份数据库db1,使用NOFORMAT,保存现在备份1.bak的媒体信息.
BACKUP DATABASE db1 TO DISK='c:\1.bak' WITH NOFORMAT;

--指定FILE=1还原,因为db先备份
RESTORE DATABASE db FROM DISK='c:\1.bak' WITH RECOVERY,REPLACE,FILE=1;


--指定FILE=2还原,因为db1后备份
RESTORE DATABASE db1 FROM DISK='c:\1.bak' WITH RECOVERY,REPLACE,FILE=2;

--测试
SELECT * FROM db.dbo.T
UNION ALL
SELECT * FROM db1.dbo.T
GO

--删除实例数据库
DROP DATABASE db,db1
GO

其他还有很多小细节东西,大家自己去查看相应的资料吧.

本文示例源代码或素材下载