MS-SQL DB 백업 스크립트
귀찮은 디비 백업 스크립트로 간단히 백업
--SQL 에서 폴더 생성 가능하게 셋팅
--SP_CONFIGURE XP_CMDSHELL ,1
--RECONFIGURE
--날짜에 맞게 폴더 생성
DECLARE @COMMAND NVARCHAR(100)
declare @folderName NVARCHAR(100)
set @folderName = N'D:\백업\DB백업\' + CONVERT(VARCHAR(10),GETDATE(), 121)
SET @COMMAND=N'MD ' + @folderName
EXEC MASTER..XP_CMDSHELL @COMMAND
GO
--적당히 쓸 변수들
DECLARE @QUERY NVARCHAR(MAX)
DECLARE @PARAMS NVARCHAR(100)
DECLARE @DBNAME2 NVARCHAR(100)
DECLARE @PATH2 NVARCHAR(200)
DECLARE @ROWCOUNT INT;
DECLARE @COUNT INT;
--백업할 디비명들 저장
DECLARE @TABLE TABLE(TABLENAME NVARCHAR(100))
INSERT INTO @TABLE VALUES ('디비명');
....
..
.
SET @COUNT =1
SELECT @ROWCOUNT = COUNT( TABLENAME) FROM @TABLE
-- 테이블변수에서 디비명 읽어와서 백업
WHILE @COUNT<@ROWCOUNT +1
BEGIN
SELECT TOP 1 @DBNAME2 = TABLENAME FROM @TABLE
WHERE TABLENAME IN (
SELECT TABLENAME
FROM(
SELECT TABLENAME, ROW_NUMBER() OVER (ORDER BY TABLENAME) NUM FROM @TABLE
) t1 WHERE NUM = @COUNT
)
--D:\백업\DB백업\2010-03-19\ACDDYN_2010-03-19.BAK
SELECT @PATH2 = N'D:\백업\DB백업\' + CONVERT(VARCHAR(10),GETDATE(), 121) +'\'+ @DBNAME2 +N'_' +CONVERT(VARCHAR(10),GETDATE(), 121)+ N'.BAK'
SET @QUERY = N'BACKUP DATABASE @DBNAME TO DISK=@PATH'
SET @PARAMS = N' @DBNAME NVARCHAR(100),@PATH NVARCHAR(200)'
EXEC sp_executesql @QUERY,@PARAMS, @DBNAME =@DBNAME2, @PATH = @PATH2
SET @COUNT = @COUNT +1;
END
끝.