블로그 "공감"

Snapshot은 처음생성시에는 단순히 원본 DB를 바라보는 정도의 역할을 하지만 (그래도 용량은 동일)
원본 DB가 Update되는 시점에 Update 이전의 데이터를 Snapshot에 저장해 시점 Data를 저장한다.

다음은 생성예제... (SSMS에는 없는 기능인듯)

create database CreditR_Snapshot
on ( name = CreditData,
       filename = 'D:\10.MSSql2005\CreditR_SH.mdf'
     ) as snapshot of CreditR

use CreditR_Snapshot

select * from member

use Credit

backup database credit to disk = 'E:\backup\Credit_Full.bak'

select *
 into ct_test
from Category

select * from Category

backup database credit to disk = 'E:\backup\Credit_Full.bak'

select *
 into ct_test
 from category
select getdate() -- 2009-11-10 23:49:40.373

update ct_test set category_code = convert(varchar(2), category_no)
select getdate() -- 2009-11-10 23:50:03.547

select * from ct_test

insert into ct_test values ( 'aaa', 11)

select getdate() --2009-11-10 23:50:53.297

insert into ct_test values ('bbb', 12)
select getdate() -- 2009-11-10 23:51:27.140

backup log credit to disk = 'E:\backup\Credit_Log.bak'
select getdate() -- 2009-11-10 23:51:38.863

-- 문제발생
bakup log credit to disk = 'E:\backup\Credit_RLog.bak' with name = '비상로그 백업', no_truncate

restore database CreditR from disk = 'E:\backup\Credit_Full.bak' with norecovery

restore log CreditR from Disk = 'E:\backup\Credit_Log.bak' with stopat='2009-11-10 23:50:53:297'

테이블 파티셔닝... 그냥..

use master

if object_id('multiTest') is not null drop database multiTest

create database multiTEST
on primary
( name = 'multiTest_1',
 filename = 'e:\200. multibase\multiTest_1.mdf',
 size = 30mb,
 filegrowth = 5mb),
( name = 'multiTest_2',
 filename = 'e:\200. multibase\multiTest_2.ndf',
 size = 30mb,
 filegrowth = 5mb)
log on
( name = 'multiTest_log',
 filename = 'e:\200. multibase\mutlTest.log',
 size = 10mb,
 filegrowth = 3mb)

alter database multiTEST
 add filegroup FG2

alter database multiTest add file
( name = 'multiTEST_FG2',
 filename = 'E:\200. MultiBase\multiTest_FG2.mdf',
 size = 10mb,
 filegrowth = 3mb
) to filegroup FG2

sp_helpdb multiTest

create table Persons
(
 prs_id int not null primary key,
 prs_name varchar(30)
) on  'primary'

create table Department
(
 dpt_id int not null primary key,
 dpt_name varchar(30)
) on 'FG2'

sp_helpdb multiTest
sp_helpindex persons

alter database multiTest set offline
use multiTest
alter database multiTest set online

alter table Persons column add dpt_id int

sp_helpindex department

declare @int int
set @int = 10001

while @int < 3000 * 10
begin
 insert department values(@int, replicate('p', 10))

 set @int = @int + 1
end

select * from persons
select * from department

sp_helpdb multiTest

sp_help persons

select * from $partition('FG2', 1)

sp_helpfile multiTest_Fg2

alter database multiTest add file
( name = 'multiTest_FG3',
 filename = 'E:\200. MultiBase\multiTest_FG3.mdf',
 size = 10mb,
 filegrowth = 3mb
) to filegroup FG2

alter database multiTest add file
( name = 'multiTEST_FG2',
 filename = 'E:\200. MultiBase\multiTest_FG2.mdf',
 size = 10mb,
 filegrowth = 3mb
) to filegroup FG2

alter database multiTest add filegroup FG3

alter database multiTest add file
( name = 'multiTEST_FG3_1',
 filename = 'E:\200. MultiBase\multiTest_FG3_1.mdf',
 size = 10mb,
 filegrowth = 3mb
) to filegroup FG3

alter database multiTest add file
( name = 'multiTEST_FG3_2',
 filename = 'E:\200. MultiBase\multiTest_FG3_2.mdf',
 size = 10mb,
 filegrowth = 3mb
) to filegroup FG3

alter database multiTest add filegroup FG4

alter database multiTest add file
( name = 'multiTEST_FG4_1',
 filename = 'E:\200. MultiBase\multiTest_FG4_1.mdf',
 size = 10mb,
 filegrowth = 3mb
) to filegroup FG4

alter database multiTest add file
( name = 'multiTEST_FG4_2',
 filename = 'E:\200. MultiBase\multiTest_FG4_2.mdf',
 size = 10mb,
 filegrowth = 3mb
) to filegroup FG4

drop partition function DepartmentPF

begin tran
 create partition function DepartmentPF(int) as range left for values (10000, 20000)
 create partition scheme DepartmentPS as partition DepartmentPF to ([primary], FG3, FG4)
commit tran

sp_helpdb multiTest

alter table Department on DepartmentPS

create table

sp_helpdb tempdb

alter database tempdb add file
( name = 'tempdev2',
 filename = 'E:\200. MultiBase\tempdb_2.ndf',
 size = 30mb,
 filegrowth = 5mb
) to filegroup [primary]

alter database tempdb add file
( name = 'tempdev3',
 filename = 'E:\200. MultiBase\tempdb_3.ndf',
 size = 30mb,
 filegrowth = 5mb
) to filegroup [primary]