SQL Server Backup Script with TimeStamp

SQL Server를 운영하다보면 백업 스케쥴링에 대해서 고민하게 됩니다. 아래 스크립트는 SQL Server에 생성된 사용자 Database들에 대해서 TimeStamp를 찍어 일단위로 지정된 폴더 경로에 데이터 베이스를 백업하도록 하는 스크립트입니다.

 

set quoted_identifier off go declare @SQL varchar(max) declare @runtime varchar(12) select @runtime = convert(varchar(20),getdate(),112) + right('00' +convert(varchar(2),datepart(hh, getdate())),2) +right('00' +convert(varchar(2),datepart(mi, getdate())),2) DECLARE DBNames CURSOR READ_ONLY FOR select name as DatabaseName from sys.sysdatabases where ([dbid] > 4) and ([name] not like '$') DECLARE @name sysname OPEN DBNames FETCH NEXT FROM DBNames INTO @name WHILE (@@fetch_status <> -1) BEGIN IF (@@fetch_status <> -2) BEGIN --select @SQL = "EXECUTE master.dbo.xp_create_subdir N'M:\Backups\" + @name +"'" --exec (@SQL) select @SQL = "Backup database [" + @name + "] TO DISK = N'E:\SQLServer_Backup\" + @name +"_backup_" + @runtime + ".bak'" + " WITH NOFORMAT, NOINIT,SKIP, REWIND, NOUNLOAD, STATS = 100" exec (@SQL) END FETCH NEXT FROM DBNames INTO @name END CLOSE DBNames DEALLOCATE DBNames GO