asked on

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!

lcohan

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

MrVault

ASKER

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 Pletcher

Using dynamic SQL, you could include the folder name and a date and time in the backup file name.

Scott Pletcher

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.

MrVault

ASKER

Sorry but I'm limited in my tsql experience. I know select statements and joins

Scott Pletcher

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

jogos

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.

lcohan

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.

MrVault

ASKER

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.

lcohan

You could use undocummented ms_foreachdb like in examples attached:

http://wiki.lessthandot.com/index.php/Sp_MSforeachDB

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jogos

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

MrVault

ASKER

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

jogos

small fires?

MrVault

ASKER

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.