DB 백업 및 리스토어 명령어

 

/********************************************
   BACKUP DATABASE 백업
 ********************************************/

USE [MASTER]
      
BACKUP DATABASE SMART
             TO DISK='D:\DB_Backup\SMART\SMART20200603\SMART_backup_2020_06_03_000002_2015088.bak'

 

/********************************************
   DATABASE 분리_연결하기
 ********************************************/

USE [MASTER]

-- sp_helpdb
exec  sp_helpdb SMART

-- 분리하기
EXEC sp_detach_db  N'SMART'                  /* db명을 적는다 */ 


-- 연결하기
EXEC sp_attach_db @dbname  = N'SMART'                        /* db명을 적는다 */ 
                , @filename1 = N'D:\DBMS\20200603\SMART_20200603.mdf'     /* .mdf 파일경로 */ 
                , @filename2 = N'D:\DBMS\20200603\SMART_20200603_log.LDF' /* .ldf 파일경로 */ 





/********************************************
   DATABASE RESTORE 백업_복구하기
 ********************************************/

USE [MASTER]

-- 백업파일 구조 확인 
RESTORE FILELISTONLY FROM DISK = 'D:\DB_Backup\SMART\SMART20200603\SMART_backup_2020_06_03_000002_2015088.bak'
      
      
-- RESTORE DATABASE
RESTORE DATABASE SMART
      FROM DISK = 'D:\DB_Backup\SMART\SMART20200603\SMART_backup_2020_06_03_000002_2015088.bak'
      WITH MOVE 'SMART'     TO 'D:\DBMS\SMART_20200603\SMART_20200603.mdf',
           MOVE 'SMART_log' TO 'D:\DBMS\SMART_20200603\SMART_20200603_log.LDF'