반응형
- /**********************************************************************************************
-- Title : [2k5] 스냅샷을 이용한 데이터, 오브젝트, DB 복원
-- Reference : hanbitbook.co.lkr
-- Key word : snapshot 스냅샷 스냅숏
**********************************************************************************************/
use master;
go
--스냅샷 생성
create database adv_sh1
on (name = adventureworks_data,
filename = 'd:\adv_sh1.mdf')
as snapshot of adventureworks;
go
--원본 데이터 삭제
use adventureworks;
go
delete from humanresources.employeepayhistory where employeeid > 100;
go
*/
--스냅샷으로 부터 원본 데이터 복구
*/
use adventureworks;
go
insert into humanresources.employeepayhistory
select * from adv_sh1.humanresources.employeepayhistory
where employeeid > 100;
go
--원본 테이블 삭제
use adventureworks;
go
drop table humanresources.employeepayhistory;
go
/*
--스냅샷으로 부터 원본 테이블 복구
*/
use adventureworks;
/* 테이블 생성 */
create table [humanresources].[employeepayhistory](
[employeeid] [int] not null,
[ratechangedate] [datetime] not null,
[rate] [money] not null,
[payfrequency] [tinyint] not null,
[modifieddate] [datetime] not null constraint [df_employeepayhistory_modifieddate] default (getdate()),
constraint [pk_employeepayhistory_employeeid_ratechangedate] primary key clustered
(
[employeeid] asc,
[ratechangedate] asc
)with (pad_index = off, statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on, allow_page_locks = on) on [primary]
) on [primary];
go
insert into humanresources.employeepayhistory
select * from adv_sh1.humanresources.employeepayhistory;
go
/* 제약 생성 */
alter table [humanresources].[employeepayhistory]
with check
add constraint [fk_employeepayhistory_employee_employeeid] foreign key([employeeid])
references [humanresources].[employee] ([employeeid]);
alter table [humanresources].[employeepayhistory]
check constraint [fk_employeepayhistory_employee_employeeid];
alter table [humanresources].[employeepayhistory]
with check
add constraint [ck_employeepayhistory_payfrequency]
check (([payfrequency]=(2) or [payfrequency]=(1)));
alter table [humanresources].[employeepayhistory]
check constraint [ck_employeepayhistory_payfrequency];
alter table [humanresources].[employeepayhistory]
with check
add constraint [ck_employeepayhistory_rate]
check (([rate]>=(6.50) and [rate]<=(200.00)));
alter table [humanresources].[employeepayhistory]
check constraint [ck_employeepayhistory_rate];
/*
--스냅샷에서 restore문을 활용한 복원
*/
use adventureworks;
go
drop table sales.customeraddress;
go
use master;
go
restore database adventureworks
from database_snapshot='adv_sh1'
with replace;
-- 확인
select * from adventureworks.sales.customeraddress;
반응형