https://www.datavail.com/blog/how-to-stripe-your-backups-into-multiple-files/
-- Stripe the full backup files for the selected databases with date and timestamp appended to the backup files
-- ************************************************************************************************************
-- Copyright © 2016 by JP Chen of DatAvail Corporation
-- This script is free for non-commercial purposes with no warranties.
-- ************************************************************************************************************
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 = 'W:\MSSQL12.SQL1\MSSQL\DATA\UserDB\Test\' -- 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), ':', ''), '-', ''), ' ', '') + '_1.bak'',
DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + '_2.bak'',
DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + '_3.bak'',
DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + '_4.bak'',
DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + '_5.bak'',
DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + '_6.bak'',
DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + '_7.bak'',
DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + '_8.bak'',
DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + '_9.bak'',
DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + '_10.bak'',
DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + '_11.bak'',
DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + '_12.bak'',
DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + '_13.bak'',
DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + '_14.bak'',
DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + '_15.bak'',
DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + '_16.bak'',
DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + '_17.bak'',
DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + '_18.bak'',
DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + '_19.bak'',
DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + '_20.bak'''
-- 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