SQL 2008 Recovery

--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 table1 (a int , b int)
go

--5.backup log(log備份且 table1被清除)

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

--6.新增資料(log中有 1 1 )

insert table1 values (1,1)

--7.backup log(log備份且 1 1被清除)

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

--8.新增資料(log中有 2 2 )

insert table1 values (2,2)

--破壞資料庫

1.stop sql service
2.rename C:\TSQL2K8DB\Northwind.mdf --> C:\TSQL2K8DB\Northwind.xxx
3.copy C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA 到上層目錄
4.start sql service
5.sql log查詢壞掉的檔案
6.檢查資料庫狀態

--7.tail log backup
a.device右鍵-->database-->
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

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, NORECOVERY, NOUNLOAD, STATS = 10
GO
RESTORE LOG [Northwind] FROM DISK = N'C:\backups\mybackup.bak' WITH FILE = 3, NORECOVERY, NOUNLOAD, STATS = 10
GO
RESTORE LOG [Northwind] FROM DISK = N'C:\backups\mybackup.bak' WITH FILE = 4, NOUNLOAD, STATS = 10
GO