[MSSQL] Backup Script File

USE [msdb]

GO

/****** Object:  StoredProcedure [dbo].[MNG_BackupDatabase]    Script Date: 04/16/2011 16:34:31 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

/*   

 Database Backup 용procedure

         

 [DBA] 05:00 - Full Backup

 [DBA] 05:00 - Diff Backup

 [DBA] 04:00 - Log Backup

*/   

ALTER proc [dbo].[MNG_BackupDatabase]

       @bakType char(1) = 'F',

       @folderdir varchar(50) = 'Y:\BackupFiles'

as   

   

set nocount on    

   

declare @strFilename nvarchar(200)   

declare @strName nvarchar(200)   

declare @DBName varchar(30)   

declare @bakDir varchar(200)

 

-- 필요에따라삭제 

-- EXECUTE master.dbo.xp_delete_file 0,@folderdir,N'bak', @delDate,1 

-- EXECUTE master.dbo.xp_delete_file 0,@folderdir,N'trn', @delDate,1 

 

/* Full Backup */   

if @bakType = 'F'

begin

       declare curDBName cursor fast_forward for    

             select name from sys.databases where database_id > 4 and is_in_standby = 0 order by name -- (recovery_model_desc = 'FULL')

  

       open curDBName   

   

    fetch next from curDBName   

        into @DBName   

   

       while @@fetch_status = 0    

       begin   

             set @strName = N'[' + @DBName + '] ' + convert(varchar(30), getdate(), 21)    

             set @strFilename = @folderdir + '\' + @DBName + '\' + @DBName + '_' + left(replace(replace(replace(convert(char(19), getdate(), 120), '-', ''), ':', ''), ' ', ''), 12) + '_F.bak'

             set @bakDir = @folderdir + '\' + @DBName

            

             EXECUTE master.dbo.xp_create_subdir @bakDir

            

             BACKUP DATABASE @DBName TO  DISK = @strFilename WITH NOFORMAT, COMPRESSION, NOINIT,  NAME = @strName, SKIP, REWIND, NOUNLOAD,  STATS = 10   

 

             fetch next from curDBName   

              into @DBName 

       end

              

       close curDBName   

       deallocate curDBName 

end

else if @bakType = 'D'

begin

       declare curDBName cursor fast_forward for    

             select name from sys.databases where database_id > 4 and is_in_standby = 0 order by name -- (recovery_model_desc = 'Any')

  

       open curDBName   

   

    fetch next from curDBName   

        into @DBName   

   

       while @@fetch_status = 0    

       begin   

             set @strName = N'[' + @DBName + '] ' + convert(varchar(30), getdate(), 21)    

             set @strFilename = @folderdir + '\' + @DBName + '\' + @DBName + '_' + left(replace(replace(replace(convert(char(19), getdate(), 120), '-', ''), ':', ''), ' ', ''), 12) + '_D.bak'

  

             set @bakDir = @folderdir + '\' + @DBName

            

             EXECUTE master.dbo.xp_create_subdir @bakDir

            

             BACKUP DATABASE @DBName TO  DISK = @strFilename WITH DIFFERENTIAL, COMPRESSION, NOFORMAT, NOINIT,  NAME = @strName, SKIP, REWIND, NOUNLOAD,  STATS = 10   

      

             fetch next from curDBName   

              into @DBName  

       end

      

       close curDBName   

       deallocate curDBName 

end

else if @bakType = 'L'

begin

       declare curDBName cursor fast_forward for    

             select name from sys.databases where database_id > 4 and is_in_standby = 0 and recovery_model = 1 order by name -- (recovery_model_desc = 'Any')

  

       open curDBName   

   

    fetch next from curDBName   

        into @DBName   

   

       while @@fetch_status = 0    

       begin   

             set @strName = N'[' + @DBName + '] ' + convert(varchar(30), getdate(), 21)    

             set @strFilename = @folderdir + '\' + @DBName + '\' + @DBName + '_' + left(replace(replace(replace(convert(char(19), getdate(), 120), '-', ''), ':', ''), ' ', ''), 12) + '_L.trn'

  

             set @bakDir = @folderdir + '\' + @DBName

            

             EXECUTE master.dbo.xp_create_subdir @bakDir

            

             BACKUP LOG @DBName TO  DISK = @strFilename WITH NOFORMAT, NOINIT, COMPRESSION, NAME = @strName, SKIP, NOREWIND, NOUNLOAD,  STATS = 10

      

             fetch next from curDBName   

              into @DBName  

       end

      

       close curDBName   

       deallocate curDBName 

end

BackupScript.sql