[MS-SQL] BAK 파일을 새로운 DATABASE로 복원하기(attach_db_from_backup)

MDF 파일만 있을 경우에는 sp_attach_single_file_db 프로시저를 이용해서 아주 간편하고 쉽게 DB를 복원할 수 있다.

그러나 Backup(BAK) 파일의 경우에는 복원하는데 약간 불편함을 감수해야 한다.

아래의 프로시저를 이용하면 이러한 불편함을 약간이나마 해소할 수 있다.

_Attach_DB_From_Backup.sql

_Attach_DB_From_Backup_For_2008R2.sql

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[_Attach_DB_From_Backup]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP procedure [dbo].[_Attach_DB_From_Backup]
GO
  
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
  
  
CREATE PROCEDURE [dbo]._Attach_DB_From_Backup
  @strRestoreDBName varchar(128),
  @strBackupFilePath  varchar(255)
AS
  
-- master DB 체크
IF DB_NAME() <> 'master'
BEGIN
  RAISERROR( 'current database name was not "master". You should be change "master" database', 16, 1 )
  RETURN
END
  
-- 생성할 DB가 존재하는지 체크
IF EXISTS (SELECT * FROM  master..sysdatabases WHERE name = @strRestoreDBName )
BEGIN
  RAISERROR( 'It was already existed database name. Try to execute procedure another DB name', 16, 1 )
  RETURN
END
  
-- 실행 시간 측정
declare @StartTime datetime
SET @StartTime = GetDate()
  
-- 동적 실행 쿼리로 쓰일 변수
declare @strQuery nvarchar(2048)
  
-- DB 생성
SET @strQuery = 'CREATE DATABASE ' + @strRestoreDBName
EXEC sp_executesql @strQuery
  
  
-- 임시 테이블 생성 - (Backup 파일에서 정보 추출)
CREATE TABLE #FileListInfo
(
  LogicalName nvarchar(128)  NULL,
  PhysicalName  nvarchar(260) NULL,
  Type    char(1)   NULL,
  FileGroupName nvarchar(128)  NULL,
  [Size]    numeric(20,0) NULL,
  [MaxSize] numeric(20,0)  NULL
  [FileID]    bigint    NULL,
  [CreateLSN]    numeric(25,0)  NULL,
  [DropLSN]    numeric(25,0)  NULL,
  [UniqueID]    uniqueidentifier NULL,
  [ReadOnlyLSN]   numeric(25,0)  NULL,
  [ReadWriteLSN]   numeric(25,0)  NULL,
  [BackupSizeInBytes]  bigint    NULL,
  [SourceBlockSize]  int     NULL,
  [FileGroupID]   int     NULL,
  [LogGroupGUID]   uniqueidentifier NULL,
  [DifferentialBaseLSN] numeric(25,0)  NULL,
  [DifferentialBaseGUID] uniqueidentifier NULL,
  [IsReadOnly]   bit     NULL,
  [IsPresent]    bit     NULL
)
  
-- 임시 테이블 생성 - (생성된 DB에서 정보(MDF, LDF 파일 경로) 추출)
CREATE TABLE #CreatedDBInfo
(
  name    nvarchar(128) NULL,
  filename  nvarchar(260) NULL
)
  
-- Backup 파일로 부터 정보 추출 -> #FileListInfo
SET @strQuery = N'RESTORE FILELISTONLY FROM DISK = ''' + @strBackupFilePath + ''''
INSERT INTO #FileListInfo EXEC sp_executesql @strQuery
  
-- 생성된 DB에서 정보 추출 -> #CreatedDBInfo
SET @strQuery = 'select name, filename from ' + @strRestoreDBName + '.[dbo].sysfiles'
INSERT INTO #CreatedDBInfo EXEC sp_executesql @strQuery
  
  
-- Backup 파일로 부터 LogicalName(Data, Log)를 가져온다.
declare @LogicalName_Data nvarchar(128)
declare @LogicalName_Log  nvarchar(128)
  
SELECT @LogicalName_Data = LogicalName FROM #FileListInfo where Type = 'D'
IF( @@error <> 0 OR @@rowcount = 0 OR @LogicalName_Data IS NULL )
begin
  RAISERROR( 'LogicalName for Data is not exist', 16, 1 )
  DROP TABLE #FileListInfo
  DROP TABLE #CreatedDBInfo
  
  SET @strQuery = 'DROP DATABASE ' + @strRestoreDBName
  EXEC sp_executesql @strQuery
  RETURN
end
SELECT @LogicalName_Log  = LogicalName FROM #FileListInfo where Type = 'L'
IF( @@error <> 0 OR @@rowcount = 0 OR @LogicalName_Log IS NULL )
begin
  RAISERROR( 'LogicalName for Log is not exist', 16, 1 )
  DROP TABLE #FileListInfo
  DROP TABLE #CreatedDBInfo
  
  SET @strQuery = 'DROP DATABASE ' + @strRestoreDBName
  EXEC sp_executesql @strQuery
  RETURN
end
  
  
-- 생성된 DB에서 MDF, LDF 파일 경로를 가져온다.
declare @strFileName_Data nvarchar(260)
declare @strFileName_Log  nvarchar(260)
  
  
SELECT @strFileName_Data = filename FROM #CreatedDBInfo where filename like '%MDF%'
IF( @@error <> 0 OR @@rowcount = 0 OR @strFileName_Data IS NULL )
begin
  RAISERROR( '#CreatedDBInfo has not MDF file path', 16, 1 )
  DROP TABLE #FileListInfo
  DROP TABLE #CreatedDBInfo
  
  SET @strQuery = 'DROP DATABASE ' + @strRestoreDBName
  EXEC sp_executesql @strQuery
  RETURN
end
SELECT @strFileName_Log  = filename FROM #CreatedDBInfo where filename like '%LDF%'
IF( @@error <> 0 OR @@rowcount = 0 OR @strFileName_Log IS NULL )
begin
  RAISERROR( '#CreatedDBInfo has not LDF file path', 16, 1 )
  DROP TABLE #FileListInfo
  DROP TABLE #CreatedDBInfo
  
  SET @strQuery = 'DROP DATABASE ' + @strRestoreDBName
  EXEC sp_executesql @strQuery
  RETURN
end
  
-- Backup 파일로 부터 DB를 복원 한다.
SET @strQuery = 'RESTORE DATABASE ' + RTRIM(@strRestoreDBName) + ' FROM DISK = ''' + RTRIM(@strBackupFilePath)
    + ''' WiTH REPLACE, MOVE ''' + RTRIM(@LogicalName_Data) + ''' TO ''' + RTRIM(@strFileName_Data) + ''', '
    + ' MOVE ''' + RTRIM(@LogicalName_Log) + ''' TO ''' + RTRIM(@strFileName_Log) + ''''
  
EXEC sp_executesql @strQuery
  
-- 임시 테이블 Drop
DROP TABLE #FileListInfo
DROP TABLE #CreatedDBInfo
  
SELECT 'Elapsed time : ' + convert( varchar(128), GetDate() - @StartTime, 114 )
  
SET NOCOUNT OFF
  
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO 

참조 사이트
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=1247&lngWId=5
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=36961
http://msdn.microsoft.com/en-us/library/ms173778.aspx