MSSQL 모든 데이터베이스 인스턴스 논리적 파일이름 가져오기

데이터베이스 마이그레이션을 할 일이 생겨서

데이터베이스의 모든 DB인스턴스이름에 대한 논리적 파일이름을 가져와야 할 일이 생겼다

 

원하는 정답이 될지는 모르겠지만 도움이 되고자 작성해본다

 

진행순서 아래와 같다

1. 모든 DB의 인스턴스이름  가져오기

2. 모든 DB의 .mdf .log 파일 용량만 가져오기

3. 테이블 합병(모든 DB의 논리적 파일이름 추출)

 

 

1 모든 DB의 인스턴스 이름 가져오기

MSSQL에서는 sys.databases 라는 테이블을 조회하면 모든 인스턴스를 조회가능하다

우리는 필요한 디비 인스턴스 이름만 가져오도록한다

 

#DATABASE_NAME 라는 임시테이블을 생성하여 sys.databases 에있는 name 만 순서를 붙여서

테이블 생성(디비 인스턴스 수량에 따라 시간이 약간 소요될 수 있음)

 

CREATE TABLE #DATABASE_NAME --drop table #DATABASE_NAME

(

             no int identity(1,1) NOT NULL, --(시작할 숫자값, 증가할 숫자값)

             name nvarchar(128),            -- Database name.

)

INSERT INTO #DATABASE_NAME select name from master.sys.databases where name not in ('master','model','msdb','tempdb') select @@Identity AS  'no'

GO

 

결과보기

SELECT * FROM #DATABASE_NAME

 

2. 모든 DB의 .mdf .log 파일 용량만 가져오기

MDF LDF 파일의 용량을 가져오기전에 sp_helpfile 테이블의 구조와 동일한 임시 테이블을 만든다

 

--임시 테이블 만들기

CREATE TABLE #DATABASE_NAME_MLDF --drop table #DATABASE_NAME_MLDF

(

--no int identity(1,1) NOT NULL --(시작할 숫자값, 증가할 숫자값)

name                                 sysname  --논리적 파일 이름입니다.

,fileid                                 smallint                --파일의 숫자 식별자입니다. 경우에 반환 되지 않습니다 이름을 된합니다.

,filename               nchar(260)                         --물리적 파일 이름입니다.

,filegroup              nvarchar(15)          --파일이 속한 파일 그룹입니다. NULL = 파일이 로그 파일입니다. 파일 그룹에 속하지 않습니다. sysname

,size                                  nvarchar(15)          --파일 크기(KB)입니다.

,maxsize               nvarchar(15)          --파일이 증가할 수 있는 최대 크기입니다. 이 필드 값이 UNLIMITED이면 디스크가 꽉 찰 때까지 파일이 증가할 수 있음을 의미합니다.

,growth                              nvarchar(15)          --파일의 증가분입니다. 공간이 새로 필요할 때마다 파일에 추가되는 공간의 양을 나타냅니다. 0 = 파일은 고정 크기를 가지며 증가하지 않습니다.

,사용현황              varchar(9)                           --데이터 파일에 대 한 값이 '데이터만' 하 고 값은 로그 파일에 대 한 '로그' 합니다.

)

GO

CREATE TABLE #DATABASE_NAME_MDF --drop table #DATABASE_NAME_MDF

(

no int identity(1,1) NOT NULL --(시작할 숫자값, 증가할 숫자값)

,name                                sysname  --논리적 파일 이름입니다.

,fileid                                 smallint                --파일의 숫자 식별자입니다. 경우에 반환 되지 않습니다 이름을 된합니다.

,filename               nchar(260)                         --물리적 파일 이름입니다.

,filegroup              nvarchar(15)          --파일이 속한 파일 그룹입니다. NULL = 파일이 로그 파일입니다. 파일 그룹에 속하지 않습니다. sysname

,size                                  nvarchar(15)          --파일 크기(KB)입니다.

,maxsize               nvarchar(15)          --파일이 증가할 수 있는 최대 크기입니다. 이 필드 값이 UNLIMITED이면 디스크가 꽉 찰 때까지 파일이 증가할 수 있음을 의미합니다.

,growth                              nvarchar(15)          --파일의 증가분입니다. 공간이 새로 필요할 때마다 파일에 추가되는 공간의 양을 나타냅니다. 0 = 파일은 고정 크기를 가지며 증가하지 않습니다.

,사용현황              varchar(9)                           --데이터 파일에 대 한 값이 '데이터만' 하 고 값은 로그 파일에 대 한 '로그' 합니다.

)

 

GO

 

CREATE TABLE #DATABASE_NAME_LDF --drop table #DATABASE_NAME_LDF

(

no int identity(1,1) NOT NULL --(시작할 숫자값, 증가할 숫자값)

,name                                sysname  --논리적 파일 이름입니다.

,fileid                                 smallint                --파일의 숫자 식별자입니다. 경우에 반환 되지 않습니다 이름을 된합니다.

,filename               nchar(260)                         --물리적 파일 이름입니다.

,filegroup              nvarchar(15)          --파일이 속한 파일 그룹입니다. NULL = 파일이 로그 파일입니다. 파일 그룹에 속하지 않습니다. sysname

,size                                   nvarchar(15)          --파일 크기(KB)입니다.

,maxsize               nvarchar(15)          --파일이 증가할 수 있는 최대 크기입니다. 이 필드 값이 UNLIMITED이면 디스크가 꽉 찰 때까지 파일이 증가할 수 있음을 의미합니다.

,growth                              nvarchar(15)          --파일의 증가분입니다. 공간이 새로 필요할 때마다 파일에 추가되는 공간의 양을 나타냅니다. 0 = 파일은 고정 크기를 가지며 증가하지 않습니다.

,사용현황              varchar(9)                           --데이터 파일에 대 한 값이 '데이터만' 하 고 값은 로그 파일에 대 한 '로그' 합니다.

)

GO

 

생성된 테이블에 커서를 활용하여 값을 집어 넣어준다

 

--커서로 모든 디비값 삽입

 DECLARE @DBName varchar(100)

 

 DECLARE CUR CURSOR FOR   --CUR라는 이름의 커서 선언

 

             --쿼리 조회

             SELECT name AS DBName  FROM master.sys.databases order by name --디비리스트 추출

 

 

             OPEN CUR      --커서 오픈

             FETCH NEXT FROM CUR INTO @DBName --SELECT한 값을 @NAME,@AGE 변수에 넣는다.

             --커서를이용해 한ROW씩 읽음

             WHILE @@FETCH_STATUS = 0

             BEGIN

            

             EXEC ('USE ' +@DBName+ '

                           INSERT INTO #DATABASE_NAME_MLDF

                           EXEC sp_helpfile ' )

 

             --인서트 쿼리 뒤에 select @@Identity을 붙여 주면 insert한 row의 identity를 불러올 수 있다

             insert into #DATABASE_NAME_MDF select * from #DATABASE_NAME_MLDF WHERE 사용현황='data only' and name not in ('master','modeldev','MSDBData','tempdev','temp2','temp3','temp4','temp5','temp6','temp7','temp8') select @@Identity AS  'no'

             insert into #DATABASE_NAME_LDF select * from #DATABASE_NAME_MLDF WHERE 사용현황='log only'  and name not in ('mastlog','modellog','MSDBLog','templog') select @@Identity AS  'no'

             delete from #DATABASE_NAME_MLDF

 

             --WAITFOR DELAY '0:0:1' --DB 갯수가 164개라서 164초 정도 걸린다.

    

             FETCH NEXT FROM CUR INTO @DBName         --다음ROW로 이동

             END

 

             --커서 닫고 초기화

             CLOSE CUR

             DEALLOCATE CUR

 

 

GO

결과보기

결과보기 샘플 쿼리를 확인하여 조회해보고 입맛에 맞게 조금씩 수정해 보면 됩니다

--select * from #DATABASE_NAME_MDF where 사용현황='data only';

--select *,len(size),convert(int,substring(size,0,(len(size)-2))/1024/1024) AS size_int_GB from #DATABASE_NAME_LDF where 사용현황='log only' order by size_int_GB desc

--select *,convert(int,LEFT(size,charindex(' ',size)-1)) AS size_int from #DATABASE_NAME_LDF where 사용현황='log only' order by size desc

GO

 

 

 

3. 테이블 합병

원하는 형식에 맞게 테이블을 병합을 진행합니다

필요한 데이터는 데이터베이스의 MDF,LDF 논리적 파일이름이니 아래의 쿼리를 실행하면 됩니다

(조인문을 수정하여 필요한 데이터를 유동적으로 추출하시면 됩니다.)

 

SELECT a.No, a.name AS DBNAME , b.name AS MDF, c.name AS LDF

INTO #DATABASE_NAME_MURGE -- #DATABASE_NAME_murge 인설트

FROM #DATABASE_NAME a

INNER JOIN #DATABASE_NAME_MDF b ON (a.No=b.No)

INNER JOIN #DATABASE_NAME_LDF c ON (a.No=c.No)

 

결과보기

SELECT * FROM #DATABASE_NAME_MURGE where

 

이상으로 모든 데이터베이스에 대한 MDF,LDF 의 논리적 파일이름 추출하는 법에 대해서 알아보았습니다