[스크립트] 백업 및 복구 스크립트

2022. 1. 6. 22:32

이관작업 (https://sqlseverjoa.tistory.com/30) 간 사용된 스크립트

Full 복원

1_RestoreDatabase.bat

DECLARE @backupFileName varchar(1000)
SET @backupFileName = '$(BackupFileName)' 
 

DECLARE @HeaderInfo TABLE
(
	BackupName nvarchar(128),
	BackupDescription nvarchar(255),
	BackupType smallint,
	ExpirationDate datetime,
	Compressed bit,
	Position smallint,
	DeviceType tinyint, 
	UserName nvarchar(128),
	ServerName nvarchar(128),
	DatabaseName nvarchar(128),
	DatabaseVersion int,
	DatabaseCreationDate datetime,
	BackupSize numeric(20, 0),
	FirstLSN numeric(25, 0),
	LastLSN numeric(25, 0),
	CheckpointLSN numeric(25, 0),
	DatabaseBackupLSN numeric(25, 0),
	BackupStartDate datetime,
	BackupFinishDate datetime,
	SortOrder smallint,
	[CodePage] smallint,
	UnicodeLocaleId int,
	UnicodeComparisonStyle int,
	CompatibilityLevel tinyint,
	SoftwareVendorId int,
	SoftwareVersionMajor int,
	SoftwareVersionMinor int,
	SoftwareVersionBuild int,
	MachineName nvarchar(128),
	Flags int,
	BindingId uniqueidentifier,
	RecoveryForkId uniqueidentifier,
	Collation nvarchar(128),
	FamilyGUID uniqueidentifier,
	HasBulkLoggedData bit,
	IsSnapshot bit,
	IsReadOnly bit,
	IsSingleUser bit,
	HasBackupChecksums bit,
	IsDamaged bit,
	BeginsLogChain bit,
	HasIncompleteMetaData bit,
	IsForceOffline bit,
	IsCopyOnly bit,
	FirstRecoveryForkID uniqueidentifier,
	ForkPointLSN numeric(25, 0),
	RecoveryModel nvarchar(60),
	DifferentialBaseLSN numeric(25, 0),
	DifferentialBaseGUID uniqueidentifier,
	BackupTypeDescription nvarchar(60),
	BackupSetGUID uniqueidentifier,
	CompressedBackupSize bigint,
	Containment tinyint,
	KeyAlgorithm nvarchar(32),
	EncryptorThumbprint varbinary(20),
	EncryptorType nvarchar(32)
)

DECLARE @databaseName  varchar(1000)
DECLARE @restoreDatabaseName  varchar(1000)

INSERT @HeaderInfo
EXEC(N'RESTORE HEADERONLY FROM DISK = ''' + @backupFileName  + '''')

SELECT @databaseName = DatabaseName 
FROM @HeaderInfo

SET @restoreDatabaseName = @databaseName;

 
DECLARE @SQL nvarchar(max) = 'RESTORE DATABASE ' + @restoreDatabaseName + ' FROM DISK = ''' + @backupFileName + ''' WITH ' + char(10);
DECLARE @fileList VARCHAR(5000) = '';

DECLARE @DataPath VARCHAR(1000) = 'E:\DBData\';
DECLARE @LogPath VARCHAR(1000) = 'D:\DBLog\';
DECLARE @IdxPath VARCHAR(1000) = 'E:\DBIndex\';
DECLARE @DataPath1 VARCHAR(1000) = 'DBData_ENC\';
DECLARE @LogPath1 VARCHAR(1000) = 'DBLog_ENC\';
DECLARE @IdxPath1 VARCHAR(1000) = 'DBIndex_ENC\';
DECLARE @DataPath2 VARCHAR(1000) = 'DBData\';
DECLARE @LogPath2 VARCHAR(1000) = 'DBLog\';
DECLARE @IdxPath2 VARCHAR(1000) = 'DBIndex\';


DECLARE @fileListTable TABLE
(
	LogicalName          nvarchar(128),
	PhysicalName         nvarchar(260),
	[Type]               char(1),
	FileGroupName        nvarchar(128),
	Size                 numeric(20,0),
	MaxSize              numeric(20,0),
	FileID               bigint,
	CreateLSN            numeric(25,0),
	DropLSN              numeric(25,0),
	UniqueID             uniqueidentifier,
	ReadOnlyLSN          numeric(25,0),
	ReadWriteLSN         numeric(25,0),
	BackupSizeInBytes    bigint,
	SourceBlockSize      int,
	FileGroupID          int,
	LogGroupGUID         uniqueidentifier,
	DifferentialBaseLSN  numeric(25,0),
	DifferentialBaseGUID uniqueidentifier,
	IsReadOnl            bit,
	IsPresent            bit,
	TDEThumbprint        varbinary(32), -- remove this column if using SQL 2005
	snapshoturl			nvarchar(360) -- in sql 2017
)
INSERT INTO @fileListTable EXEC('RESTORE FILELISTONLY FROM DISK = ''' +@backupFileName + '''');
 
 -- STUFF() 함수 - 특정순서부터 특정 순자의 문자를 특정 문자열로 대체
--                형식 : STUFF(문자변수, 대체할 문자를 대입할 위치, 대체할 문자수, 대체할 문자변수)
SELECT @fileList = STUFF((
	SELECT 
	--  LogicalName
	--,	PhysicalName
	--,	FileGroupName
		' MOVE ''' + LogicalName + ''' TO ''' +
		CASE 
			when CHARINDEX(@DataPath1, PhysicalName) <> 0 and CHARINDEX(@restoreDatabaseName, PhysicalName) <> 0    then STUFF(PhysicalName,1,CHARINDEX(@databaseName, PhysicalName)-1,@DataPath)
			when CHARINDEX(@LogPath1, PhysicalName) <> 0  and CHARINDEX(@restoreDatabaseName, PhysicalName) <> 0   then STUFF(PhysicalName,1,CHARINDEX(@databaseName, PhysicalName)-1,@LogPath)
			when CHARINDEX(@IdxPath1, PhysicalName) <> 0  and CHARINDEX(@restoreDatabaseName, PhysicalName) <> 0   then STUFF(PhysicalName,1,CHARINDEX(@databaseName, PhysicalName)-1,@IdxPath)
			when CHARINDEX(@DataPath2, PhysicalName) <> 0 and CHARINDEX(@restoreDatabaseName, PhysicalName) <> 0    then STUFF(PhysicalName,1,CHARINDEX(@databaseName, PhysicalName)-1,@DataPath)
			when CHARINDEX(@LogPath2, PhysicalName) <> 0  and CHARINDEX(@restoreDatabaseName, PhysicalName) <> 0   then STUFF(PhysicalName,1,CHARINDEX(@databaseName, PhysicalName)-1,@LogPath)
			when CHARINDEX(@IdxPath2, PhysicalName) <> 0  and CHARINDEX(@restoreDatabaseName, PhysicalName) <> 0   then STUFF(PhysicalName,1,CHARINDEX(@databaseName, PhysicalName)-1,@IdxPath)
			when CHARINDEX('index', PhysicalName) <> 0    then @idxpath+right(physicalName,charindex('\',reverse(physicalName)))
			when CHARINDEX('log', PhysicalName) <> 0    then @logpath+right(physicalName,charindex('\',reverse(physicalName))) 
			else   @datapath+right(physicalName,charindex('\',reverse(physicalName))) 
		END
		+ ''',' + CHAR(10)
	FROM @fileListTable
	FOR XML PATH('')
),1,1,'') FROM @fileListTable
;

 
SET @SQL = @SQL + @fileList+ ' norecovery' ;
	
-- 
PRINT @SQL  
exec sp_executesql @SQL;

SQL_RestoreDatabase.sql

DECLARE @backupFileName varchar(1000)
SET @backupFileName = '$(BackupFileName)' 
 

DECLARE @HeaderInfo TABLE
(
	BackupName nvarchar(128),
	BackupDescription nvarchar(255),
	BackupType smallint,
	ExpirationDate datetime,
	Compressed bit,
	Position smallint,
	DeviceType tinyint, 
	UserName nvarchar(128),
	ServerName nvarchar(128),
	DatabaseName nvarchar(128),
	DatabaseVersion int,
	DatabaseCreationDate datetime,
	BackupSize numeric(20, 0),
	FirstLSN numeric(25, 0),
	LastLSN numeric(25, 0),
	CheckpointLSN numeric(25, 0),
	DatabaseBackupLSN numeric(25, 0),
	BackupStartDate datetime,
	BackupFinishDate datetime,
	SortOrder smallint,
	[CodePage] smallint,
	UnicodeLocaleId int,
	UnicodeComparisonStyle int,
	CompatibilityLevel tinyint,
	SoftwareVendorId int,
	SoftwareVersionMajor int,
	SoftwareVersionMinor int,
	SoftwareVersionBuild int,
	MachineName nvarchar(128),
	Flags int,
	BindingId uniqueidentifier,
	RecoveryForkId uniqueidentifier,
	Collation nvarchar(128),
	FamilyGUID uniqueidentifier,
	HasBulkLoggedData bit,
	IsSnapshot bit,
	IsReadOnly bit,
	IsSingleUser bit,
	HasBackupChecksums bit,
	IsDamaged bit,
	BeginsLogChain bit,
	HasIncompleteMetaData bit,
	IsForceOffline bit,
	IsCopyOnly bit,
	FirstRecoveryForkID uniqueidentifier,
	ForkPointLSN numeric(25, 0),
	RecoveryModel nvarchar(60),
	DifferentialBaseLSN numeric(25, 0),
	DifferentialBaseGUID uniqueidentifier,
	BackupTypeDescription nvarchar(60),
	BackupSetGUID uniqueidentifier,
	CompressedBackupSize bigint,
	Containment tinyint,
	KeyAlgorithm nvarchar(32),
	EncryptorThumbprint varbinary(20),
	EncryptorType nvarchar(32)
)

DECLARE @databaseName  varchar(1000)
DECLARE @restoreDatabaseName  varchar(1000)

INSERT @HeaderInfo
EXEC(N'RESTORE HEADERONLY FROM DISK = ''' + @backupFileName  + '''')

SELECT @databaseName = DatabaseName 
FROM @HeaderInfo

SET @restoreDatabaseName = @databaseName;

 
DECLARE @SQL nvarchar(max) = 'RESTORE DATABASE ' + @restoreDatabaseName + ' FROM DISK = ''' + @backupFileName + ''' WITH ' + char(10);
DECLARE @fileList VARCHAR(5000) = '';

DECLARE @DataPath VARCHAR(1000) = 'E:\DBData\';
DECLARE @LogPath VARCHAR(1000) = 'D:\DBLog\';
DECLARE @IdxPath VARCHAR(1000) = 'E:\DBIndex\';
DECLARE @DataPath1 VARCHAR(1000) = 'DBData_ENC\';
DECLARE @LogPath1 VARCHAR(1000) = 'DBLog_ENC\';
DECLARE @IdxPath1 VARCHAR(1000) = 'DBIndex_ENC\';
DECLARE @DataPath2 VARCHAR(1000) = 'DBData\';
DECLARE @LogPath2 VARCHAR(1000) = 'DBLog\';
DECLARE @IdxPath2 VARCHAR(1000) = 'DBIndex\';


DECLARE @fileListTable TABLE
(
	LogicalName          nvarchar(128),
	PhysicalName         nvarchar(260),
	[Type]               char(1),
	FileGroupName        nvarchar(128),
	Size                 numeric(20,0),
	MaxSize              numeric(20,0),
	FileID               bigint,
	CreateLSN            numeric(25,0),
	DropLSN              numeric(25,0),
	UniqueID             uniqueidentifier,
	ReadOnlyLSN          numeric(25,0),
	ReadWriteLSN         numeric(25,0),
	BackupSizeInBytes    bigint,
	SourceBlockSize      int,
	FileGroupID          int,
	LogGroupGUID         uniqueidentifier,
	DifferentialBaseLSN  numeric(25,0),
	DifferentialBaseGUID uniqueidentifier,
	IsReadOnl            bit,
	IsPresent            bit,
	TDEThumbprint        varbinary(32), -- remove this column if using SQL 2005
	snapshoturl			nvarchar(360) -- in sql 2017
)
INSERT INTO @fileListTable EXEC('RESTORE FILELISTONLY FROM DISK = ''' +@backupFileName + '''');
 
 -- STUFF() 함수 - 특정순서부터 특정 순자의 문자를 특정 문자열로 대체
--                형식 : STUFF(문자변수, 대체할 문자를 대입할 위치, 대체할 문자수, 대체할 문자변수)
SELECT @fileList = STUFF((
	SELECT 
	--  LogicalName
	--,	PhysicalName
	--,	FileGroupName
		' MOVE ''' + LogicalName + ''' TO ''' +
		CASE 
			when CHARINDEX(@DataPath1, PhysicalName) <> 0 and CHARINDEX(@restoreDatabaseName, PhysicalName) <> 0    then STUFF(PhysicalName,1,CHARINDEX(@databaseName, PhysicalName)-1,@DataPath)
			when CHARINDEX(@LogPath1, PhysicalName) <> 0  and CHARINDEX(@restoreDatabaseName, PhysicalName) <> 0   then STUFF(PhysicalName,1,CHARINDEX(@databaseName, PhysicalName)-1,@LogPath)
			when CHARINDEX(@IdxPath1, PhysicalName) <> 0  and CHARINDEX(@restoreDatabaseName, PhysicalName) <> 0   then STUFF(PhysicalName,1,CHARINDEX(@databaseName, PhysicalName)-1,@IdxPath)
			when CHARINDEX(@DataPath2, PhysicalName) <> 0 and CHARINDEX(@restoreDatabaseName, PhysicalName) <> 0    then STUFF(PhysicalName,1,CHARINDEX(@databaseName, PhysicalName)-1,@DataPath)
			when CHARINDEX(@LogPath2, PhysicalName) <> 0  and CHARINDEX(@restoreDatabaseName, PhysicalName) <> 0   then STUFF(PhysicalName,1,CHARINDEX(@databaseName, PhysicalName)-1,@LogPath)
			when CHARINDEX(@IdxPath2, PhysicalName) <> 0  and CHARINDEX(@restoreDatabaseName, PhysicalName) <> 0   then STUFF(PhysicalName,1,CHARINDEX(@databaseName, PhysicalName)-1,@IdxPath)
			when CHARINDEX('index', PhysicalName) <> 0    then @idxpath+right(physicalName,charindex('\',reverse(physicalName)))
			when CHARINDEX('log', PhysicalName) <> 0    then @logpath+right(physicalName,charindex('\',reverse(physicalName))) 
			else   @datapath+right(physicalName,charindex('\',reverse(physicalName))) 
		END
		+ ''',' + CHAR(10)
	FROM @fileListTable
	FOR XML PATH('')
),1,1,'') FROM @fileListTable
;

 
SET @SQL = @SQL + @fileList+ ' norecovery' ;
	
-- 
PRINT @SQL  
exec sp_executesql @SQL;

TRN 복원

InsertHeaderInfo.bat

@echo off
  
set BackupFilePath=trnpath
E:
cd %BackupFilePath%
 
 
dir /s/b trnpath /on > trnlist.txt

 sqlcmd -S 주서버  -Q "truncate table  msdb.dbo.tblBackupHeader;"  
 
:: Insert Trn Header Info
for %%f in (*.trn) do (
echo %%~dpnxf >> trnlist.txt
sqlcmd -S 주서버 -v BackupFileName="%%~dpnxf" -i "SQL_InsertHeaderInfo.sql"  
)

SQL_InsertHeaderInfo.sql

declare @BackupFile varchar(1000)
set @BackupFile = '$(BackupFileName)'
 
declare @sql varchar(8000)
  


set @sql = 'restore headeronly from disk = '''+ @BackupFile +''''
insert into msdb.dbo.tblBackupHeader
exec(@sql)

RestoreTRN.bat

@echo off
 
sqlcmd -S 주서버 -i "SQL_RestoreTRN.sql"

SQL_RestoreTRN.sql

declare @targetDB varchar(100) = ''
declare @trnPath nvarchar(1000) = 'trnpath'
declare @targetLsn varchar(256)
declare @sql nvarchar(max) =''
declare @seqno int
select @seqno=max(seqno) from msdb..dblist 


while ( @seqno >0)
begin
    set @sql ='';

	select @targetDB=dbname from msdb..dblist  where seqno=@seqno and istarget =1
     	
     if (@@rowcount =1)
     begin
	select @targetLsn =max(b.last_lsn) from
	msdb..restorehistory a
	inner join msdb..backupset B on a.backup_set_id =b.backup_set_id
	where a.destination_database_name =@targetDB
 
 
	select @sql = @sql + 'restore log '+ @targetDB + ' from disk = '+''''
	+ @trnPath +''+ backupname +'.trn''' + ' with norecovery;'+char(10)+char(13)
	FROM [msdb].[dbo].[tblBackupHeader]
	where lastLSN >@targetLsn and databasename= @targetDB
	order by lastLSN asc
  
	print (@sql) 
	exec (@sql)
       end
 set @seqno=@seqno-1
 end

원격 FULL/trn 백업

Backup_FullTrn.bat

@echo off
  
sqlcmd -S 주서버 -i "SQL_Backup_FullTrn.sql"

SQL_Backup_FullTrn.sql (보조서버 --> 주서버)

declare @bakPath nvarchar(1000) = 'ForMig\'
declare @targetServ varchar(256)  

declare @targetDB varchar(100) = ''
declare @sql nvarchar(max) =''
declare @sql2 nvarchar(max) =''
declare @seqno int
select @seqno=max(seqno) from msdb..dblist 


select @targetServ= @@SERVERNAME
 
if (@targetServ = '주서버1' or @targetServ = '주서버2' )
begin
	while ( @seqno >0)
	begin
		set @sql =''
		set @sql2 =''

		select @targetDB=dbname from msdb..dblist  where seqno=@seqno and istarget =1
		if (@@rowcount =1)
		begin
			set @sql = 'BACKUP DATABASE ['+@targetDB+']  TO  DISK = N''' +'\\'+replace(@targetServ,'주서버','보조서버')+ '\'+@bakPath+'Full\' +@targetDB+'_Full.bak''  WITH NOFORMAT, NOINIT,  NAME = N'''+@targetDB+'_Full'''+', SKIP, NOREWIND, NOUNLOAD,  STATS = 10'
			set @sql2= 'BACKUP LOG ['+@targetDB+']  TO  DISK = N''' +'\\'+replace(@targetServ,'주서버','보조서버')+ '\'+@bakPath+'Log\' +@targetDB+'_Log.trn''  WITH NOFORMAT, NOINIT,  NAME = N'''+@targetDB+'_Log'''+', SKIP, NOREWIND, NOUNLOAD,  STATS = 10'

	
			print (@sql) 
			print (@sql2) 
			exec (@sql)
			exec (@sql2)
		end
		set @seqno=@seqno-1
	 end
END
else
begin
	print 'Wrong'
end

저작자표시 비영리 변경금지

'SQL Server > 스크립트' 카테고리의 다른 글

[스크립트] Adhoc/Proc 실시간or 통계 조회 (0)	2022.03.23
[스크립트] Audit Trace (0)	2022.03.16
[스크립트] DBtoDB 참조관계 조회 (0)	2022.01.05
[스크립트] DB 배치 정보 확인 (0)	2022.01.05
[스크립트]미러링/오류로그/배치/세션유입 체크 (0)	2021.06.12

일상및기술기록

[스크립트] 백업 및 복구 스크립트

Full 복원

TRN 복원

원격 FULL/trn 백업

'SQL Server > 스크립트' 카테고리의 다른 글

+ Recent posts

티스토리툴바