Query one:
DECLARE @DatabaseName VARCHAR(100) = 'VIT_GLOBAL_TOOLBOX'
DECLARE @BackupPath VARCHAR(1000) = 'H:\DIFF\LATNATIVE\'
DECLARE @BackupCmd NVARCHAR(MAX)
DECLARE @FileNumber INT = 1
WHILE @FileNumber <= 10 -- Number of backup files to create
BEGIN
DECLARE @BackupFile VARCHAR(1000) = @BackupPath + @DatabaseName + '_Diff_Backup_' + CONVERT(VARCHAR(20), GETDATE(), 112) + '_' + REPLACE(CONVERT(VARCHAR(20), GETDATE(), 108), ':', '') + '_Part' + CAST(@FileNumber AS VARCHAR(2)) + '.bak'
SET @BackupCmd = 'BACKUP DATABASE ' + @DatabaseName + ' ' +
'TO DISK = ''' + @BackupFile + ''' ' +
'WITH DIFFERENTIAL';
PRINT @BackupCmd
EXEC (@BackupCmd)
SET @FileNumber += 1
END
Query two:
DECLARE DBFullBackups_Cursor CURSOR
FOR
-- Pick the databases you wish to run full backups
SELECT db.name
FROM sys.databases db
WHERE db.name IN ('VIT_GLOBAL_TOOLBOX')
OPEN DBFullBackups_Cursor
DECLARE @db VARCHAR(125);
DECLARE @BackupPath VARCHAR(525);
DECLARE @BackupCmd VARCHAR(8000);
DECLARE @DeleteDate DATETIME = DATEADD(hh,-22,GETDATE());
-- Check to make sure the path exists
SET @BackupPath = 'H:\DIFF\LATNATIVE\' -- specify your own backup folder
FETCH NEXT
FROM DBFullBackups_Cursor
INTO @db
WHILE (@@FETCH_STATUS <> - 1)
BEGIN
-- Backup command
SET @BackupCmd = 'BACKUP DATABASE [' + @db + '] TO
DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + 'Diff_1.bak'',
DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + 'Diff_2.bak'',
DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + 'Diff_4.bak'',
DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + 'Diff_5.bak'''
+
'WITH DIFFERENTIAL';
-- Print the backup command
PRINT @BackupCmd
-- Run the backup command
EXEC (@BackupCmd)
-- Fetch the next database
FETCH NEXT
FROM DBFullBackups_Cursor
INTO @db
END
EXEC master.sys.xp_delete_file 0,@BackupPath,'bak',@DeleteDate,0;
-- Close and deallocate the cursor
CLOSE DBFullBackups_Cursor
DEALLOCATE DBFullBackups_Cursor
GO
Post a Comment