SQL 2008 Recovery 2 - 某一時點

-- SP_WHO
-- 查詢user pid, 可以用 kill pid 把user 踢掉
-- pid < 50 的是系統

--1.建立備份裝置 mybackup(backup device)

EXEC master.dbo.sp_addumpdevice @devtype = N'disk', @logicalname = N'mybackup', @physicalname = N'C:\backups\mybackup.bak'

--2.檢查 Northwind資料庫是否為 Full Recovery mode

--3.執行完整資料庫備份
BACKUP DATABASE [Northwind] TO [mybackup] WITH FORMAT, INIT, NAME = N'Northwind-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

--4.建立資料表(log中有此資料表)

use Northwind
go
create table table2 (a int , b int)
go

--5.下錯 SQL指令,把 orders資料表中的ShipCity欄位改掉

select * from orders

update Orders set ShipCity ='Lyon',ShippedDate =GETDATE()

--6.記錄更改時間,ShippedDate
--2010-10-02 14:14:34.753
--這個就是要回復的時間點

--7.修改資料庫為 single-user
--管理介面->資料庫右鍵->property->option->restrict access->single_user

ALTER DATABASE [Northwind] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

--8.tail log backup
a.Northwind右鍵-->task-->backup
Database:Northwind
backup type:log
option-->backup the tail of ....

BACKUP LOG [Northwind] TO [mybackup] WITH NO_TRUNCATE , NOFORMAT, NOINIT, NAME = N'Northwind-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, NORECOVERY , STATS = 10

b.檢查資料庫狀態


c.選 Northwind,右鍵-->task-->restore-->database
選擇 to a point in time,將之前時間的前一秒填入


RESTORE DATABASE [Northwind] FROM DISK = N'C:\backups\mybackup.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
GO
RESTORE LOG [Northwind] FROM DISK = N'C:\backups\mybackup.bak' WITH FILE = 2, NOUNLOAD, STATS = 10, STOPAT = N'2009-07-22T13:11:55'
GO

d.回復資料庫為多使用者