SQL 2008 Auditing - SQL Server Audit (2008 New)

此方式使用 Event Notification
不能保護資料，僅能進行通知
看不到詳細的指令

DLL Trigger 可以保護資料，但是會影響performance
SQL Profiler 可以看到詳細的指令內容(值的變更)，但是會影響Performance


先建立 server audit (DBSYS->Security->Audit)
然後可以在 server level 建立 server audit specification
(DBSYS->Security->Server Audit Specifications)
或 db level 建立 db audit specification
(DBSYS->DB->Security->Database Audit Sepcifications)


記得要啟用 Server Audit and Audit Specification


之後可以在 Server Audit 上按右鍵看event
或去當初設定的event存放位子(Window Event / file)查看

SQL 2008 encryption - Database Transparent Data Encryption (2008 New)

-- 4-32

use master

-- create server level master key
create master key encryption by
password='password'
go

--drop master key

-- create server level cert
-- master->security->certificates
create certificate NorthwindCert
with subject = 'test'

--drop certificate NorthwindCert

use Nwind
go

--drop database Northwind

-- create db enc key with cert NorthwindCert
--會出現要你備份cert的資訊
--到此 database Nwind 已經不能直接resotre 到別的機器上了
create database encryption key
with algorithm=AES_128
encryption by server certificate NorthwindCert

--drop database encryption key
--db->右鍵->task->manage database encryption

alter database Nwind
set encryption on

alter database Nwind
set encryption off

--下面的指令:
-- encryption_state
-- 1: encryption off
-- 2: encryptioning
-- 3: encryption on
select * from sys.dm_database_encryption_keys
where database_id =DB_ID('Nwind')

backup database Nwind to disk='c:\backups\nw.bak' with format
backup log Nwind to disk='c:\backups\nw.bak'



--備份憑證
use master
go
backup certificate NorthwindCert
to file = 'c:\backups\bbb.cer' with private key (file='C:\backups\MyServerCertKey\aaa.key',encryption by password='password')


--BACKUP CERTIFICATE NorthwindCert TO FILE = 'c:\backups\test.cer';
--GO


--change connection to I2

--沒有匯入key以前...restore db會失敗
drop master key
create master key encryption by password='password2'

backup certificate NorthwindCert
to file = 'c:\backups\bbb.cer' with private key (file='C:\backups\MyServerCertKey\aaa.key',encryption by password='password')



--drop certificate NorthwindCert

CREATE CERTIFICATE NorthwindCert
FROM FILE = 'c:\backups\bbb.cer'
WITH PRIVATE KEY (FILE = 'C:\backups\MyServerCertKey\aaa.key',
DECRYPTION BY PASSWORD = 'password');
GO

--沒匯入憑證前
--Cannot find server certificate with thumbprint '0xAB32127B86A7D9B354F823F7B2AF307DD8BCE623'.

restore database Nwind from disk ='c:\backups\nw.bak' with
move 'Nwind_P' to 'c:\backups\nw.mdf',
move 'Nwind_A' to 'c:\backups\nw_a.ndf',
move 'Nwind_B' to 'c:\backups\nw_b.ndf',
move 'Nwind_C' to 'c:\backups\nw_c.ndf',
move 'Nwind_log' to 'c:\backups\nw_log.ldf',norecovery,file=1
,replace


restore log Nwind from disk ='c:\backups\nw.bak' with
move 'Nwind_P' to 'c:\backups\nw.mdf',
move 'Nwind_A' to 'c:\backups\nw_a.ndf',
move 'Nwind_B' to 'c:\backups\nw_b.ndf',
move 'Nwind_C' to 'c:\backups\nw_c.ndf',
move 'Nwind_log' to 'c:\backups\nw_log.ldf',recovery,file=2
,replace

SQL 2008 encryption - Encryption Data

-- Create EmployeeReviews table and grant permissions
USE AdventureWorks

IF EXISTS (SELECT * FROM sys.tables t JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.[Name] = 'EmployeeReview' AND s.[Name] = 'HumanResources')
DROP TABLE HumanResources.EmployeeReview

--comments 是打算加密的欄位
CREATE TABLE HumanResources.EmployeeReview
(EmployeeID int NOT NULL,
ReviewDate datetime DEFAULT GETDATE() NOT NULL,
Comments varbinary(2000) NOT NULL)

--
-- Create database master key
IF NOT EXISTS(SELECT * FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##')
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'Pa$$w0rd'

-- 這邊因為有datebase key，所以不用指定encryption by password
-- Create certificate
CREATE CERTIFICATE HRAppCert
WITH SUBJECT = 'HR certificate'
GO

-- 使用 HRAppCert加密，產生一把symmetric key:HRKey
-- Create symmetric key
CREATE SYMMETRIC KEY HRKey
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE HRAppCert
GO

-- 使用symmetric key:HRKey 加密
-- Insert encrypted data
-- 鑰匙要開關
OPEN SYMMETRIC KEY HRKey DECRYPTION BY CERTIFICATE HRAppCert

INSERT INTO HumanResources.EmployeeReview
VALUES
(1, DEFAULT, EncryptByKey(Key_GUID('HRKey'),'Increasing salary to $35,000'))

CLOSE ALL SYMMETRIC KEYS


-- Read the decrypted data as HRApp
OPEN SYMMETRIC KEY HRKey DECRYPTION BY CERTIFICATE HRAppCert
SELECT CONVERT(varchar,DecryptByKey(Comments)) AS Comments FROM HumanResources.EmployeeReview
CLOSE ALL SYMMETRIC KEYS

SQL 2008 Recovery 6 - snapshot

CREATE DATABASE Northwind_dbss1800 ON
( NAME = Northwind, FILENAME =
'C:\TSQL2K8DB\Northwind_data_1800.ss' )
AS SNAPSHOT OF Northwind;
GO

use Northwind

update [Order Details] set Quantity = Quantity + 1 where OrderID = 10248


select * from [Order Details]


--restore from snapshot
USE master;

RESTORE DATABASE Northwind from
DATABASE_SNAPSHOT = 'Northwind_dbss1800';
GO

SQL 2008 Recovery - recovery master db

如果master db還能進入, 直接使用single user mode啟動
然後使用sqlcmd 進行backup

single-user

C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn

sqlservr.exe -c -m


backup database master to disk ='c:\backups\master1.bak' with format


backup database msdb to disk ='c:\backups\msdb1.bak' with format

backup database model to disk ='c:\backups\model1.bak' with format



restore database master from disk ='c:\backups\master1.bak'

restore database msdb from disk ='c:\backups\msdb1.bak'

restore database model from disk ='c:\backups\model1.bak'


如果master db都壞了
需要進行rebuild

rebuild

1.cd C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release


2.setup.exe /QUIET /INSTANCENAME=mssqlserver /ACTION=rebuilddatabase /SQLSYSADMINACCOUNTS=Administrator /SAPWD=P@ssw0rd

SQL 2008 Recovery 4 - filegroup restore

--file restore sample, page 3-35

--須以 SQLCMD mode 執行這個範例
USE [master]
GO

--DROP DATABASE Nwind

!!md C:\myAdmin\DB
!!md C:\myAdmin\Backup
--因為要做 File 的備份/還原，所以需要建立多個 File
--先建立一個具有多個filegroup的DB,
CREATE DATABASE [Nwind] ON PRIMARY( NAME=N'Nwind_P',FILENAME = N'C:\myAdmin\DB\Nwind_P.mdf' ),
FILEGROUP GroupA( NAME=N'Nwind_A',FILENAME = N'C:\myAdmin\DB\Nwind_A.ndf' ),
FILEGROUP GroupB( NAME=N'Nwind_B',FILENAME = N'C:\myAdmin\DB\Nwind_B.ndf' ),
FILEGROUP GroupC( NAME=N'Nwind_C',FILENAME = N'C:\myAdmin\DB\Nwind_C.ndf' )
GO

USE Nwind
GO
--在各檔案群組各放一個資料表
--不同的table放在不同的filegroup上
CREATE TABLE tbP(c1 INT,c2 nvarchar(10)) ON [PRIMARY]
CREATE TABLE tbA(c1 INT,c2 nvarchar(10)) ON [GroupA]
CREATE TABLE tbB(c1 INT,c2 nvarchar(10)) ON [GroupB]
CREATE TABLE tbC(c1 INT,c2 nvarchar(10)) ON [GroupC]
GO

-- 各資料表所屬的 Filegroup
-- 以下指令可以看 資料表位在哪個檔案群組上
SELECT objs.schemaN N'結構描述', objs.tableN N'資料表', das.name N'檔案群組'
FROM Nwind.sys.data_spaces das INNER JOIN
(
SELECT DISTINCT object_id,data_space_id
FROM Nwind.sys.indexes
WHERE type BETWEEN 0 AND 1
) idx
ON das.data_space_id = idx.data_space_id
INNER JOIN
(
SELECT sys.objects.name 'tableN', object_id, sys.schemas.name 'schemaN'
FROM Nwind.sys.objects INNER JOIN Nwind.sys.schemas
ON sys.objects.schema_id = sys.schemas.schema_id
WHERE type='U'
) objs
ON objs.object_id = idx.object_id
ORDER BY 3

-- 分析各個資料檔案的屬性
-- 各資料檔案的state_desc 都是 ONLINE
SELECT name, physical_name, state_desc, is_read_only , read_only_lsn
FROM Nwind.sys.database_files

-- 分析資料

SELECT * FROM Nwind.dbo.tbP
UNION ALL
SELECT * FROM Nwind.dbo.tbA
UNION ALL
SELECT * FROM Nwind.dbo.tbB
UNION ALL
SELECT * FROM Nwind.dbo.tbC

-- INSERT DATA
-- 資料都存在
INSERT Nwind.dbo.tbP VALUES(1,'P')
INSERT Nwind.dbo.tbA VALUES(1,'A')
INSERT Nwind.dbo.tbB VALUES(1,'B')
INSERT Nwind.dbo.tbC VALUES(1,'C')

-- 執行DB
BACKUP DATABASE Nwind TO DISK=N'C:\myAdmin\Backup\Nwind_PiecemealBackup.bak' WITH INIT

-- 使用 SQLCMD 模式
!!net stop mssqlserver /Y
--exec master.dbo.xp_cmdshell 'net stop mssqlserver /Y'

-- 破壞 C:\myAdmin\DB\Nwind_B.ndf
-- DEL是防呆 本來就應該不存在
!!Del C:\myAdmin\DB\Nwind_B.bad
!!ren "C:\myAdmin\DB\Nwind_B.ndf" *.BAD

!!net start mssqlserver /Y

/*
訊息 945，層級 14，狀態 2，行 1
檔案無法存取、記憶體或磁碟空間不足，因此無法開啟資料庫 'Nwind'。詳細資訊請參閱 SQL Server 錯誤記錄檔。
*/
-- 檢查 SQL Server 記錄檔
/*
2008-09-07 12:02:46.41 spid30s 錯誤: 17207，嚴重性: 16，狀態: 1。
2008-09-07 12:02:46.41 spid30s FileMgr::StartSecondaryDataFiles: Operating system error 2(系統找不到指定的檔案。) occurred while creating or opening file 'C:\myAdmin\DB\Nwind_B.ndf'. Diagnose and correct the operating system error, and retry the operation.
2008-09-07 12:02:46.41 spid30s 錯誤: 5120，嚴重性: 16，狀態: 5。
2008-09-07 12:02:46.41 spid30s 無法開啟實體檔案 "C:\myAdmin\DB\Nwind_B.ndf"。作業系統錯誤 2: "2(系統找不到指定的檔案。)"。
*/
-- 需要重新連接回 SQL Server
-- 分析資料，Error
-- 因為 NWind_B 毀損, 無法正常使用整個DB
SELECT * FROM Nwind.dbo.tbP

-- 檢查資料庫的狀態
-- Nwind 在 RECOVERY_PENDING 狀態
SELECT name, state_desc
FROM sys.databases

-- OFFLINE
-- 把 Nwind_B 設定成offline,這樣其他的filegroup才能正常被使用
ALTER DATABASE Nwind
MODIFY FILE
(name = N'Nwind_B', OFFLINE)
go

-- Database are Online
-- 再把 Nwind online回來(從recovery_pending改回online)
ALTER DATABASE Nwind
SET ONLINE
go

-- 分析各個資料檔案的屬性 -- OFFLINE
SELECT name, physical_name, state_desc, is_read_only , read_only_lsn
FROM Nwind.sys.database_files

-- 分析資料，僅有 tbB 錯誤
SELECT * FROM Nwind.dbo.tbP
GO
SELECT * FROM Nwind.dbo.tbA
GO
SELECT * FROM Nwind.dbo.tbB -- 錯誤
GO
SELECT * FROM Nwind.dbo.tbC
GO

INSERT Nwind.dbo.tbP VALUES(2,'P')
GO
INSERT Nwind.dbo.tbA VALUES(2,'A')
GO
INSERT Nwind.dbo.tbB VALUES(2,'B') --錯誤
GO
INSERT Nwind.dbo.tbC VALUES(2,'C')

/*
訊息 8653，層級 16，狀態 1，行 1
查詢處理器無法產生資料表或檢視 'tbB' 的計劃，因為資料表存在於非線上的檔案群組中。
*/

-- Backup LOG --> tail-log backup
/*
記得先將 Nwind_PiecemealBackup.bak，存放到 C:\myAdmin\Backup 中。
*/
-- 注意,這邊只有 NO_TRUNCATE, 並沒有 NO_RECOVERY
-- (因為我們要讓其他table在我們修復Nwind_B的時候仍能使用, online recovery)
BACKUP LOG Nwind
TO DISK ='C:\myAdmin\Backup\Nwind_PiecemealBackup.bak'
WITH NAME = 'LOG_backupset',
NO_TRUNCATE

-- 以下三個指令都只是查看資訊
--
RESTORE HEADERONLY
FROM DISK ='C:\myAdmin\Backup\Nwind_PiecemealBackup.bak'

-- 這個指令可以看某個bak file裡面,到底是哪些filegroup的備份
RESTORE FILELISTONLY
FROM DISK ='C:\myAdmin\Backup\Nwind_PiecemealBackup.bak'
WITH FILE=1

-- FamilyCount 表示,只有一個備份檔案
-- FamilySequenceNumber 表示這是檔案組中的第一個
RESTORE labelONLY
FROM DISK ='C:\myAdmin\Backup\Nwind_PiecemealBackup.bak'



-- RESTORE DATABASE -- File
-- 將Nwind_B 備份回來
RESTORE DATABASE Nwind
FILE = N'Nwind_B'
FROM DISK ='C:\myAdmin\Backup\Nwind_PiecemealBackup.bak'
WITH RECOVERY

/*
已處理資料庫 'Nwind' 的 16 頁，檔案 1 上的檔案 'Nwind_B'。
向前復原目前的起始點位於記錄序號 (LSN) 21000000032100001。
必須有超過 LSN 21000000038200001 的其他向前復原，才能完成還原順序。
RESTORE DATABASE ... FILE= 已於 0.048 秒內成功處理了 16 頁 (2.604 MB/sec)。
*/

-- 分析各個資料檔案的屬性 -- RESTORING
-- 此時Nwind_B 的 state_desc 變成 RESTORING
SELECT name, physical_name, state_desc, is_read_only , read_only_lsn
FROM Nwind.sys.database_files

--restore tail log
-- file = 2 表示是 右鍵這個backup device->media contents-> position 數字為2的
RESTORE LOG Nwind
FROM DISK ='C:\myAdmin\Backup\Nwind_PiecemealBackup.bak'
WITH FILE = 2,
RECOVERY

-- 分析各個資料檔案的屬性 -- ONLINE
SELECT name, physical_name, state_desc, is_read_only , read_only_lsn
FROM Nwind.sys.database_files

-- 分析資料，可以完整出來所有的資料內容
SELECT * FROM Nwind.dbo.tbP
UNION ALL
SELECT * FROM Nwind.dbo.tbA
UNION ALL
SELECT * FROM Nwind.dbo.tbB
UNION ALL
SELECT * FROM Nwind.dbo.tbC

SQL 2008 Recovery 5 - piecemeal recovery

-- 1. 使用 lab\mod03\PiecemealBackup.sql 建立 AdventureWorksTest 資料庫
-- 2. 將static_data filegroup 設定為read-only (非課本上有的動作)
-- 3. 新增C:\AWBackups
-- 4. 根據L3-26.sql繼續進行

--備份讀寫的檔案群組
-- 注意 read_write_filegroups 參數
backup database AdventureworksTest read_write_filegroups to disk ='c:\AWBackups\AdventureWorksTest_Dynamic_Data.bak'

--查詢資訊
--會發現AW_StaticData的BackupSzieInBytes = 0, isPresent =0
restore filelistonly from disk =
'c:\AWBackups\AdventureWorksTest_Dynamic_Data.bak'

--只備份 static_data的檔案群組
backup database AdventureworksTest filegroup='static_data' to disk ='c:\AWBackups\AdventureWorksTest_Static_Data.bak'

--查詢資訊
--除了 AW_StaticData有備份外 其他都沒有
restore filelistonly from disk =
'c:\AWBackups\AdventureWorksTest_Static_Data.bak'


--修改資料
--注意 , 我們只針對filegroup備份，沒有完整備份
--這個的修改資料會存在 tail_log中
use AdventureWorksTest
update customer set firstname =null


--按lab將 sql server服務停止,並改名
--將 AW_DynamicData.ndf 改名
--將 AW_StaticData.ndf 改名

--啟動後,資料庫無法使用

--備份 tail-log,資料庫成為 restoring狀態
backup log AdventureWorksTest to disk ='c:\AWBackups\AdventureWorksTest_Active_Log.bak' with no_truncate,norecovery

--只restore dynamic_data檔案群組,所以需設定 with partial
restore database AdventureWorksTest filegroup='dynamic_data' from disk ='c:\AWBackups\AdventureWorksTest_Dynamic_Data.bak' with partial,norecovery

--將最後的 log restore回去
restore log AdventureWorksTest from disk ='c:\AWBackups\AdventureWorksTest_Active_log.bak' with recovery

--此時 Customer資料表可看,但 State資料表不可看


--將 static_data檔案群組 restore
restore database AdventureWorksTest filegroup='STATIC_DATA' from disk='c:\AWBackups\AdventureWorksTest_Static_Data.bak' with recovery

--將 log加回
--這部分會失敗，因為STATIC_DATA本來就是唯獨，所以不會有tail-log需要roll forward
restore log AdventureWorksTest from disk ='c:\AWBackups\AdventureWorksTest_Active_Log.bak' with recovery

--全都可以看了

SQL 2008 Recovery 3 - 特定page error

USE master
GO
BACKUP DATABASE AdventureWorks2008
TO DISK = 'C:\Backups\AWDBBackup.bak' with format
GO

--尋找production.location 的page file
--執行完dbcc checkalloc後find production.location
--要注意是要找的是 root 不是 firstiam
--FirstIAM (1:834). Root (1:833). Dpages 57.
USE AdventureWorks2008
GO
DBCC CHECKALLOC
GO
--使用corrupt指令去破壞

--破壞後用以下指令去看有問題的table與page
--此時production.location table已經不能正常使用
--但是以下資訊，在page修復後並不會消失，類似log
use msdb
go
select * from suspect_pages


--restore
--注意，這邊不用再進行tail-log的備份

USE master
GO
RESTORE DATABASE AdventureWorks2008
PAGE = '1:833'
FROM DISK = 'C:\Backups\AWDBBackup.bak'
WITH NORECOVERY
GO

--要備份一次再restore一次，確保LOG的LSN正確
BACKUP LOG AdventureWorks2008
TO DISK = 'C:\Backups\AWLogBackup.bak'
GO

RESTORE LOG AdventureWorks2008
FROM DISK = 'C:\Backups\AWLogBackup.bak'WITH RECOVERY
GO

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.回復資料庫為多使用者

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