We help IT Professionals succeed at work.

SQL Backup Job to Multiple Files in Unique folders per Database

When you use the GUI to create a SQL DB Backup job you can choose to backup to multiple files or you can choose to backup to a specific folder. If you choose the latter then you can also choose to create the backups in a separate folder for each database. So there'd be a folder called dbname1 with dbname1's backup(s) in it, and one for dbname2, etc.

However I'd like to do both. We found a sweet spot in that our backups run 40% faster if we back up to 3 files at once. However we have multiple databases so we'd like to do each to it's own folder. One top of that, if you choose to a folder, it puts a date line at the end of each file name so that you can have full backups for each day. However if you choose one or more files you are choosing the filenames yourself and thus it appears you can only have those files. Not previous days. You can append or overwrite.

Anyone know how to split it into multiple files (3 in this case) and then have multiple days back?

I'd rather not have to have separate maintenance plans or jobs depending on the day of the week, etc. It gets very complicated then once you have multiple databases, as we do.

Thanks!
Comment
Watch Question

lcohanDatabase Analyst
CERTIFIED EXPERT

Commented:
I use four backup files per DB on 4 different LUNs but same idea - speed up the native backup buy using parallel IO.

Just add the three backup devices:

exec sp_addumpdevice @devtype = 'disk' , @logicalname = 'DB1_bak1'            ,@physicalname = 'E:\SQL_BACKUP\DB1_BAK1.bak'
exec sp_addumpdevice @devtype = 'disk' , @logicalname = 'DB1_bak2'            ,@physicalname = 'F:\SQL_BACKUP\DB1_BAK2.bak'
exec sp_addumpdevice @devtype = 'disk' , @logicalname = 'DB1_bak3'            ,@physicalname = 'G:\SQL_BACKUP\DB1_BAK3.bak'

and backup on those like below:

BACKUP DATABASE [DB1] TO  [DB1_bak1],  [DB1_bak2],  [DB1_bak3]  WITH  NOFORMAT, INIT,  NAME = N'DB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

Author

Commented:
Does that create a new filename for each day? How do you have 3 full backups to restore to if they back up to the same filename each time. Or do you append it inside each file and thus each file gets bigger and bigger? We like separate files for each day so that we can see the size easily, move them around faster, etc. Our file sizes are somewhere between 70 and 400GB compressed.
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Using dynamic SQL, you could include the folder name and a date and time in the backup file name.
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
So, just generate the entire BACKUP DATABASE command into a single SQL string, then use dynamic SQL:
EXEC (@sql) <or> EXEC sp_executeSQL @sql
to run the sql string.

Author

Commented:
Sorry but I'm limited in my tsql experience. I know select statements and joins
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Sorry, didn't have time earlier to provide fuller code.

This may not be 100% but should be very close at least :-) .

DECLARE @sql varchar(8000)
DECLARE @dbName varchar(128)
DECLARE @backupPath varchar(100)
DECLARE @backupFile varchar(260)

SET @dbName = '<name_of_db_to_backup>
SET @backupPath ='@:\backup\path\' + @dbName
SET @backupFile = @dbName + '_#_' + CONVERT(CHAR(8), @currentDate, 112) + '_' + REPLACE(CONVERT(CHAR(5), @currentDate, 8), ':', '') + '.bak'

SET @sql = 'BACKUP DATABASE [' + @dbName + '] ' + 'TO ' +
    --backup files (add dbName to path so each db goes into its own folder)
    'DISK = ''' + @backupPath + '\' + REPLACE(REPLACE(@backupFile, '#', '1'), 'd', '') + '''' +
    'DISK = ''' + @backupPath + '\' + REPLACE(REPLACE(@backupFile, '#', '2'), 'e', '') + ''',' +
    'DISK = ''' + @backupPath + '\' + REPLACE(REPLACE(@backupFile, '#', '3'), 'f', '') + ''''

EXEC(@sql)

Open in new window

Commented:
ScottPletcher's sollution is for one database, you should put a loop for all db's around it.

But by programing it yourself don't forget that in a maintenance plan you can also have other things such as transaction log backup , delete old bu-files.
lcohanDatabase Analyst
CERTIFIED EXPERT

Commented:
We take a full backup daily with overwrite by running T-SQL code like I posted above from SQL job and T-logs every four hours. Upon completion the backup folders are copied to tape incremental - not sure how our IT set it up but is working because we tested the restore process (as part of our disater recovery) with speciffic PITR on another server and it was perfect.

Author

Commented:
Unfortunately we don't have the option to backup to tape and thus only keep one copy locally.

That being said, how do we loop this around each databases. Essentially I want it to look like this in the folder structure:

F:\Backups\Server1\DB1\
      DB1-Full-11-03-2011-file1.bak
      DB1-Full-11-03-2011-file2.bak
      DB1-Full-11-03-2011-file3.bak
      DB1-Full-11-04-2011-file1.bak
      DB1-Full-11-04-2011-file2.bak
      DB1-Full-11-04-2011-file3.bak
F:\Backups\Server1\DB2\
      DB2-Full-11-03-2011-file1.bak
      DB2-Full-11-03-2011-file2.bak
      DB2-Full-11-03-2011-file3.bak
      DB2-Full-11-04-2011-file1.bak
      DB2-Full-11-04-2011-file2.bak
      DB2-Full-11-04-2011-file3.bak

I already have a script to delete databases older than X days and it only runs if that day's backup succeeded.
lcohanDatabase Analyst
CERTIFIED EXPERT

Commented:
You could use undocummented ms_foreachdb like in examples attached:

http://wiki.lessthandot.com/index.php/Sp_MSforeachDB
Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
Please look at the output from the SQL below.  I have actually run this code so I know that it produces reasonable output.

I used underscores instead of dashes in the filename, for readability, and used yyyy-mm-dd instead of mm-dd-yyyy for sorting reasons.
EXEC sp_msforeachdb '
IF ''?'' IN (''tempdb'') --<<-- add other dbs to exclude if/as needed
    RETURN 
DECLARE @dbName varchar(128)
DECLARE @backupDate datetime
DECLARE @backupDrive1 char(1)
DECLARE @backupDrive2 char(1)
DECLARE @backupDrive3 char(1)
DECLARE @backupPath varchar(100)
DECLARE @backupFile varchar(260)
DECLARE @sql varchar(8000)

SET @dbName = ''?''
SET @backupDate = GETDATE()
SET @backupDrive1 = ''F''
SET @backupDrive2 = ''F'' 
SET @backupDrive3 = ''F'' 
SET @backupPath =''F:\Backups\Server1\'' + @dbName --chg drv letter to @ to allow diff backup drives
SET @backupFile = @dbName + ''_Full_'' + CONVERT(CHAR(8), @backupDate, 112) + 
    ''_'' + /* REPLACE(CONVERT(CHAR(5), @backupDate, 8), '':'', '''') + ''_'' + */ ''File#.bak''

--SELECT @dbName, @backupPath, @backupFile, @backupDate

SET @sql = ''BACKUP DATABASE ['' + @dbName + ''] TO '' +
    --backup files (add dbName to path so each db goes into its own folder)
    ''DISK = '''''' + REPLACE(@backupPath, ''@'', @backupDrive1) + ''\'' + REPLACE(@backupFile, ''#'', ''1'') + '''''','' +
    ''DISK = '''''' + REPLACE(@backupPath, ''@'', @backupDrive2) + ''\'' + REPLACE(@backupFile, ''#'', ''2'') + '''''','' +
    ''DISK = '''''' + REPLACE(@backupPath, ''@'', @backupDrive3) + ''\'' + REPLACE(@backupFile, ''#'', ''3'') + ''''''''

PRINT @sql
--EXEC(@sql) --uncomment after reviewing
'

Open in new window

Commented:
But don't forget if you have db's with recovery model FULL you also need a transaction log backup!

Author

Commented:
hi. haven't forgotten. just have to take care of some small fires first. will provide update.

Commented:
small fires?

Author

Commented:
some small. some that could have spread. :)

I will award points for now and re-open for discussion once I have more time to devote to the solution. Hope that's OK.