Sql Backup Daily

DECLARE @path VARCHAR(256) 
DECLARE @DBName varchar(255)
DECLARE @DATABASES_Fetch int
SET @path = 'E:\BANK_MS_SQL_Daily\'  +CONVERT(VARCHAR(20),GETDATE(),112)+'_'+ replace(convert(varchar, getdate(),108),':','-')+'\'
EXEC master.sys.xp_create_subdir @path
DECLARE DATABASES_CURSOR CURSOR FOR
   select
       DATABASE_NAME   = db_name(s_mf.database_id)
   from
       sys.master_files s_mf
   where
      -- ONLINE
       s_mf.state = 0 
      -- Only look at databases to which we have access
   and has_dbaccess(db_name(s_mf.database_id)) = 1 
       -- Not master, tempdb or model
   and db_name(s_mf.database_id) not in ('Master','tempdb','model','HOST_ME_MMSTOOLS_LOG')  
   group by s_mf.database_id
   order by 1
OPEN DATABASES_CURSOR
FETCH NEXT FROM DATABASES_CURSOR INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
   declare @DBFileName varchar(256)    
   set @DBFileName =  replace(replace(@DBName,':','_'),'\','_') + '_' + CONVERT(VARCHAR(20),GETDATE(),112) + '.bak'
   exec ('BACKUP DATABASE [' + @DBName + '] TO  DISK = N+ @path + 
       @DBFileName +  WITH NOFORMAT, INIT,  NAME = N + 
       @DBName + '-Full Database Backup, SKIP, NOREWIND, NOUNLOAD,  STATS = 100')
   FETCH NEXT FROM DATABASES_CURSOR INTO @DBName
END
CLOSE DATABASES_CURSOR
DEALLOCATE DATABASES_CURSOR
Sql Backup Daily

Navigation menu
