[2k5] 스냅샷을 이용한 데이터, 오브젝트, DB 복원

1. /**********************************************************************************************
   -- 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;