Sql Backup Daily
Jump to navigation
Jump to search
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