SQL Server 백업/복원을 쿼리로 하자

SSMS 에서 가능하지만 스크립트로 하면 쉽고 편하기도 하다.

-- 백업

select 'BACKUP DATABASE '+name+' TO DISK =''T:\MSSQL\BACKUP\Move_Backup\'
+ 'Last_Full_'+name+'_'+ convert(varchar(8),getdate(),112) +'.BAK''' +
' WITH COMPRESSION, STATS = 10 '
from master.dbo.sysdatabases
where name not in ('TestDB', 'msdb', 'tempdb','model', 'master', 'ReportServer', 'ReportServerTempDB')

select
'BACKUP DATABASE '+name+' TO DISK =''L:\Move_Backup\'
+ 'Last_Full_'+name+'_'+ convert(varchar(8),getdate(),112) +'.BAK''' +
' WITH COMPRESSION, STATS = 10 '        
from sys.databases
where name not in ('TestDB', 'msdb', 'tempdb','model', 'master')

select
'BACKUP LOG '+name+' TO DISK =''L:\Move_Backup\'
+ 'Last_Trn_'+name+'_'+ convert(varchar(8),getdate(),112) +'.trn''' +
' WITH COMPRESSION, STATS = 10 '        
from sys.databases
where name not in ('TestDB', 'msdb', 'tempdb','model', 'master')

-- 복원

RESTORE DATABASE [데이터베이스이름] FROM DISK=N'L:\[백업경로]\[백업파일].bak'
WITH
move '데이터파일1' to 'L:\[데이터파일1].mdf'
, move '로그파일1' to 'L:\[로그파일1].ldf'
, move '데이터파일2' to 'L:\[데이터파일2].ndf'
, recovery, REPLACE, stats=10;

RESTORE LOG [데이터베이스이름] FROM DISK = N'L:\[백업경로]\[백업파일].trn'
WITH STATS = 10

SELECT name, filename FROM dbo.sysfiles A

-- 백업파일 내용 확인하기

-- list file names in backup set
RESTORE filelistonly        FROM DISK = N'C:\SQLDATA\tddb.bak';

-- list backups within device
RESTORE headeronly                FROM DISK = N'C:\SQLDATA\tddb.bak';

-- verify backup set is valid
RESTORE verifyonly                FROM DISK = N'C:\SQLDATA\tddb.bak'

/*
Attempting to restore this backup may encounter storage space problems. Subsequent messages will provide details.
The path specified by "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TDDB.mdf" is not in a valid directory.
Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TDDB_log.ldf" failed with the operating system error 3(The system cannot find the path specified.).
The backup set on file 1 is valid.
*/