T-SQL 备份和还原

　2009-09-14 00:00:00　来源：WEB开发网　闂備線娼уΛ鎾箯閿燂拷

濠电姭鎷冮崨顓濈捕闂侀潧娲ゅú銊╁焵椤掍胶鈯曢柕鍥╁仧缁辩偤鏁撻敓锟�闂備線娼уΛ鎾箯閿燂拷　　闂備胶枪缁绘鈻嶉弴銏犳瀬闁绘劖顐煎☉妯锋瀻闁归偊鍓涘▔姘舵⒑閸涘⿴娈旀繛灞傚妼閳绘捇骞嬪┑鎰濡炪倖姊婚崢褏鎲撮敓锟�

核心提示： Code/*Author:Terry.Sai.M.J 浪客 Location:BeiJingDateTime:GETDATE()Description:恢复到特定的备份日期*/IF DB_ID('db') IS NOT NULL DROP DATABASE db;GOCREATE

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 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='c:\2.bak' WITH FORMAT; GO RESTORE DATABASE db FROM DISK='c:\1.bak' WITH NORECOVERY,REPLACE; GO DECLARE @Now DATETIME; SET @Now=DATEADD(SECOND,-1,(SELECT CAST(CONTEXT_INFO() AS DATETIME))); --SET @Now=DATEADD(MS,-20,(SELECT CAST(CONTEXT_INFO() AS DATETIME))); RESTORE LOG db FROM DISK='c:\2.bak' WITH RECOVERY,STOPAT=@Now; 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 LOG db TO DISK='c:\2.bak' WITH FORMAT; GO INSERT INTO db.dbo.T DEFAULT VALUES; GO BACKUP LOG db TO DISK='c:\3.bak' WITH FORMAT; GO RESTORE DATABASE db FROM DISK='c:\1.bak' WITH RECOVERY,REPLACE; GO SELECT * FROM db.dbo.T GO RESTORE DATABASE db FROM DISK='c:\1.bak' WITH NORECOVERY,REPLACE; GO RESTORE LOG db FROM DISK='c:\2.bak' WITH RECOVERY; GO SELECT * FROM db.dbo.T GO RESTORE DATABASE db FROM DISK='c:\1.bak' WITH NORECOVERY,REPLACE; GO RESTORE LOG db FROM DISK='c:\2.bak' WITH NORECOVERY; GO RESTORE LOG db FROM DISK='c:\3.bak' WITH RECOVERY; GO SELECT * FROM db.dbo.T GO DROP DATABASE db;