DB 백업 & 복구 스크립트

DB 이관을 위한 백업 & 복구 스크립트

1. FULL 백업

 - 복구모델이 FULL 이 아닌경우는 FULL로 변경하고 전체 백업

declare @path varchar(30) 
set @path = 'path' 

select  
    case when recovery_model_desc <> 'FULL'  
        then 'ALTER DATABASE ' + name + ' SET RECOVERY FULL  
GO  
'       else '' end  
+   'BACKUP DATABASE ' + name + ' TO DISK = N''' + @path  +'\' + name + '.bak'' WITH COMPRESSION , STATS = 10
GO' as '구장비백업스크립트'
,   case when recovery_model_desc <> 'FULL'  
        then 'ALTER DATABASE ' + name + ' SET RECOVERY ' + recovery_model_desc collate Korean_Wansung_CI_AS + '
GO  
'       else '' end as '신규장비복구모드'
from sys.databases
where database_id > 4  
and name not in ('distribution')  
and state_desc = 'ONLINE'  
order by name

 

2 트랜잭션 백업

  - 백업로그 스크립트를 생성하는 것이 아니라 실행하는 것이므로 확인 후 실행할 것
  - SqlAgent를 이용하여 시간단위로 생성할 것

exec sp_MSforeachdb 'use ? 
if ''?'' not in (''master'',''distribution'',''model'',''tempdb'',''msdb'')
begin
    declare @path varchar(100) 
    set @path = ''path\?_'' + replace(replace(replace(convert(varchar(13), getdate(), 20), '' '', ''''), ''-'', ''''), '':'', '''') + ''00.trn'' 

    BACKUP LOG ? TO DISK = @path
end'

 

3. NORECOVERY로 복구

declare @path varchar(30)
set @path = 'path'

select
    case when type = 0 and file_id = 1
        then 'GO
RESTORE DATABASE ' + b.name + ' FROM DISK = N'''+ @path + '' + b.name + '.bak'' WITH  FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
'
        else '' end
+   ', MOVE N''' + a.name + ''' TO N'''
+   case
        when type = 0 and file_id  = 1 then 'E:\Data\' + a.name + '.mdf'''
        when type = 0 and file_id != 1 then 'E:\Data\' + a.name + '.ndf'''
        when type = 1                  then 'L:\Log\'  + a.name + '.ldf'''
    end
from sys.master_files as a
join sys.databases    as b
on a.database_id = b.database_id
where b.database_id > 4
order by b.name, a.type, a.file_id

 

4. 시간단위 트랜잭션 로그 부어주기

declare
    @path varchar(30) 
,   @sdtDate char(8)
set @path = 'path'
set @sdtDate = convert(char(8), getdate(), 112)

select 
    'RESTORE DATABASE ' + name + ' FROM DISK = N'''+ @path + name + '_' + @sdtDate + h +'00.trn'' WITH NORECOVERY 
GO
PRINT ''complete : '  + name + '_' + @sdtDate + h +'00.trn, time : '' + convert(varchar(20), getdate(), 20) + ''''
GO'
from sys.databases as a 
cross join 
( 
    select '00' as h union all select '01' union all select '02' union all select '03' union all select '04' union all select '05' union all select '06' union all
    select '07' as h union all select '08' union all select '09' union all select '10' union all select '11' union all select '12' union all select '13' union all
    select '14' as h union all select '15' union all select '16' union all select '17' union all select '18' union all select '19' union all select '20' union all
    select '21' as h union all select '22' union all select '23' 
) as hh 
where database_id > 4 
and name not in ('distribution') 
and name = 'beacon'
order by a.name, hh.h

 

5. 리커버리 모드, 복구모드 세팅, 호환성수준 세팅, DB 소유자 변경, 통계업데이트

select
    'RESTORE DATABASE ' + name + ' WITH RECOVERY
GO'
,   'ALTER DATABASE ' + name + ' SET RECOVERY SIMPLE
GO'
,   'ALTER DATABASE ' + name + ' SET COMPATIBILITY_LEVEL = 130
GO'
,   'use ' + name + '
GO
exec sp_changedbowner ''' + '소유자' + '''
GO'
,   'use ' + name + '
GO
exec sp_msforeachtable ''update statistics ?''
GO
'
from sys.databases as a
where database_id > 4
and name not in ('distribution')
order by name

 

6. 서버옵션 조정

select 'exec sp_configure ''show advanced options'', 1
go
RECONFIGURE
go
exec sp_configure ''Ad Hoc Distributed Queries'', 1
go
RECONFIGURE
go
exec sp_configure ''max server memory'', ' + convert(varchar(10), @mem) + '
go
RECONFIGURE
go
exec sp_configure ''min server memory'', ' + convert(varchar(10), @mem) + '
go
RECONFIGURE
go'