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