If you want to backup your SQL Server databases you normally use the Maintenance Plan Wizard to generate an automated job to do just that. But what if you want to backup across multiple files to improve the backup performance ( on multi CPU servers) AND still included an automatically generated date/time stamp within the file names?

The script below does exactly that. It created file output like this example.

E:\mssql_dailybu\SQLSRV1\DBAAdmin\SQLSRV1-DBAAdmin_20160518_1708_1of4.bak
E:\mssql_dailybu\SQLSRV1\DBAAdmin\SQLSRV1-DBAAdmin_20160518_1708_2of4.bak
E:\mssql_dailybu\SQLSRV1\DBAAdmin\SQLSRV1-DBAAdmin_20160518_1708_3of4.bak
E:\mssql_dailybu\SQLSRV1\DBAAdmin\SQLSRV1-DBAAdmin_20160518_1708_4of4.bak

Simply create a new job, under the ‘SQL Server Agent’ and add the code below. Once created, don’t forget to assign a schedule to it to execute the job each nigh.

-- Backup all databases
-- 4 stripes & file names include dates
-- with compression
-- requires xp_cmdshell to be enabled so it can create any missing directories to store backups
--
DECLARE @Baksql VARCHAR(8000)
DECLARE @BackupFolderBase VARCHAR(100)
DECLARE @BackupFolder VARCHAR(100)
DECLARE @BackupFile VARCHAR(100)
DECLARE @BAK_PATH VARCHAR(4000)
DEclare @BackupDate varchar(100)
Declare @BackupSrv varchar(100)
DECLARE @cmd VARCHAR(100)
DECLARE @result VARCHAR(100)
-- 
-- Setting value of backup date and folder of the backup
SET @BackupDate = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(16),GETDATE(),120),'-',''),':',''),' ','_') -- 20160517_1435
select @BackupSrv = @@servicename
SET @BackupFolderBase = 'E:\mssql_dailybu\' + @BackupSrv +'\'
--
-- Declaring cursor
DECLARE c_bakup CURSOR FAST_FORWARD READ_ONLY FOR 
SELECT NAME FROM SYS.DATABASES 
WHERE state_desc = 'ONLINE' -- Consider databases which are online
AND NAME not like '%tempdb%' -- Exluding system databases#
-- 
-- Opening and fetching next values from sursor
OPEN c_bakup 
FETCH NEXT FROM c_bakup INTO @BackupFile 
-- 
WHILE @@FETCH_STATUS = 0
BEGIN
-- Create backup folder for database - if none exists
SET @BackupFolder= @BackupFolderBase + @BackupFile
--checking if dir exist if not create it
SELECT @cmd = 'dir' + @BackupFolder 
EXEC @result = master.dbo.xp_cmdshell @cmd, NO_OUTPUT
IF @result <> 0
BEGIN
SELECT @cmd = 'mkdir ' + @BackupFolder
EXEC master.dbo.xp_cmdshell @cmd, NO_OUTPUT
END
-- set dump file name
SET @BAK_PATH = @BackupFolder + '\' + @BackupSrv + '-'+ @BackupFile
-- Creating dynamic script for every databases backup
SET @Baksql = 'BACKUP DATABASE ['+@BackupFile+'] TO 
DISK = '''+ @BAK_PATH+'_'+@BackupDate+'_1of4.bak'' 
, DISK = '''+ @BAK_PATH+'_'+@BackupDate+'_2of4.bak'' 
, DISK = '''+ @BAK_PATH+'_'+@BackupDate+'_3of4.bak'' 
, DISK = '''+ @BAK_PATH+'_'+@BackupDate+'_4of4.bak'' WITH COMPRESSION, INIT;'
-- Executing dynamic query
PRINT (@Baksql)
EXEC(@Baksql)
-- Opening and fetching next values from sursor
FETCH NEXT FROM c_bakup INTO @BackupFile 
END
-- Closing and Deallocating cursor
CLOSE c_bakup
DEALLOCATE c_bakup