[Fortify] SSC Database Migration (Microsoft SQL)

/ Fortify / 글쓴이 
######################################################################
#
# Microsoft SQL Fortify SSC DB 복원
# db_ssc_2020 -> db_ssc_2540 으로 마이그레이션 예제
#
######################################################################

1. DB 백업
sqlcmd -S TCP:127.0.0.1 -U SA -P <PASSWORD> -Q "BACKUP DATABASE [db_ssc_2020] TO DISK = 'C:\db_backup\db_ssc_2020_full.bak' WITH INIT, FORMAT;"
sqlcmd -S TCP:127.0.0.1 -U SA -P <PASSWORD> -Q "BACKUP LOG [db_ssc_2020] TO DISK = 'C:\db_backup\db_ssc_2020_log.trn' WITH INIT;"

2. 새로 설치할 버전의 신규 Database 생성 (db_ssc_2540)
CREATE DATABASE [db_ssc_2540]
 CONTAINMENT = NONE
 COLLATE Korean_Wansung_CS_AS
GO
ALTER DATABASE [db_ssc_2540] SET COMPATIBILITY_LEVEL = 120
GO
ALTER DATABASE [db_ssc_2540] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [db_ssc_2540] SET ANSI_NULLS OFF
GO
ALTER DATABASE [db_ssc_2540] SET ANSI_PADDING OFF
GO
ALTER DATABASE [db_ssc_2540] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [db_ssc_2540] SET ARITHABORT OFF
GO
ALTER DATABASE [db_ssc_2540] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [db_ssc_2540] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [db_ssc_2540] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [db_ssc_2540] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [db_ssc_2540] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [db_ssc_2540] SET CURSOR_DEFAULT  GLOBAL
GO
ALTER DATABASE [db_ssc_2540] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [db_ssc_2540] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [db_ssc_2540] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [db_ssc_2540] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [db_ssc_2540] SET DISABLE_BROKER
GO
ALTER DATABASE [db_ssc_2540] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [db_ssc_2540] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [db_ssc_2540] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [db_ssc_2540] SET READ_COMMITTED_SNAPSHOT ON
GO
ALTER DATABASE [db_ssc_2540] SET READ_WRITE
GO
ALTER DATABASE [db_ssc_2540] SET RECOVERY FULL
GO
ALTER DATABASE [db_ssc_2540] SET MULTI_USER
GO
ALTER DATABASE [db_ssc_2540] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [db_ssc_2540] SET TARGET_RECOVERY_TIME = 0 SECONDS
GO
ALTER DATABASE [db_ssc_2540] SET DELAYED_DURABILITY = DISABLED
GO
ALTER DATABASE [db_ssc_2540] SET ALLOW_SNAPSHOT_ISOLATION ON
GO
USE [db_ssc_2540]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [db_ssc_2540] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO
SET ANSI_NULL_DFLT_ON ON
GO

3. DB를 오프라인 상태로 전환
sqlcmd -S TCP:127.0.0.1 -U SA -P <PASSWORD> -Q "ALTER DATABASE [db_ssc_2020] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; ALTER DATABASE [db_ssc_2020] SET OFFLINE;"
sqlcmd -S TCP:127.0.0.1 -U SA -P <PASSWORD> -Q "ALTER DATABASE [db_ssc_2540] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; ALTER DATABASE [db_ssc_2540] SET OFFLINE;"

4. mdf.ldf 디렉토리 확인
예) C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\DATA\db_ssc_2540.mdf
예) C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\DATA\db_ssc_2540_log.ldf

5. mdf, ldf 디렉토리 수정 후 db_ssc_2020→db_ssc_2540으로 DB 복사
sqlcmd -S TCP:127.0.0.1 -U SA -P <PASSWORD> -Q "RESTORE DATABASE [db_ssc_2540] FROM DISK = 'C:\db_backup\db_ssc_2020_full.bak' WITH MOVE 'db_ssc_2020' TO 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\DATA\db_ssc_2540.mdf', MOVE 'db_ssc_2020_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\DATA\db_ssc_2540_log.ldf', REPLACE, RECOVERY;"

6. DB를 온라인 상태로 전환
sqlcmd -S TCP:127.0.0.1 -U SA -P <PASSWORD> -Q "ALTER DATABASE [db_ssc_2020] SET ONLINE; ALTER DATABASE [db_ssc_2020] SET MULTI_USER;"
sqlcmd -S TCP:127.0.0.1 -U SA -P <PASSWORD> -Q "ALTER DATABASE [db_ssc_2540] SET ONLINE; ALTER DATABASE [db_ssc_2540] SET MULTI_USER;"

7. SSC 설치 중 DB 설정 페이지 JDBC 주소 입력
jdbc:sqlserver://127.0.0.1:1433;database=db_ssc_2540;connectionCollation=korean_wansung_cs_as;sendStringParametersAsUnicode=false;encrypt=true;trustServerCertificate=true

8. 마이그레이션 SQL 스크립트 다운로드 후 적용

9. Process Seed 설치까지 진행 후 완료
위로 스크롤