MrVault
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!
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!
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.
Using dynamic SQL, you could include the folder name and a date and time in the backup file name.
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.
EXEC (@sql) <or> EXEC sp_executeSQL @sql
to run the sql string.
ASKER
Sorry but I'm limited in my tsql experience. I know select statements and joins
Sorry, didn't have time earlier to provide fuller code.
This may not be 100% but should be very close at least :-) .
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)
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.
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.
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.
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.
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.
DB1-Full-11-03-2011-file2.
DB1-Full-11-03-2011-file3.
DB1-Full-11-04-2011-file1.
DB1-Full-11-04-2011-file2.
DB1-Full-11-04-2011-file3.
F:\Backups\Server1\DB2\
DB2-Full-11-03-2011-file1.
DB2-Full-11-03-2011-file2.
DB2-Full-11-03-2011-file3.
DB2-Full-11-04-2011-file1.
DB2-Full-11-04-2011-file2.
DB2-Full-11-04-2011-file3.
I already have a script to delete databases older than X days and it only runs if that day's backup succeeded.
You could use undocummented ms_foreachdb like in examples attached:
http://wiki.lessthandot.com/index.php/Sp_MSforeachDB
http://wiki.lessthandot.com/index.php/Sp_MSforeachDB
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
But don't forget if you have db's with recovery model FULL you also need a transaction log backup!
ASKER
hi. haven't forgotten. just have to take care of some small fires first. will provide update.
small fires?
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.
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.
Just add the three backup devices:
exec sp_addumpdevice @devtype = 'disk' , @logicalname = 'DB1_bak1' ,@physicalname = 'E:\SQL_BACKUP\DB1_BAK1.ba
exec sp_addumpdevice @devtype = 'disk' , @logicalname = 'DB1_bak2' ,@physicalname = 'F:\SQL_BACKUP\DB1_BAK2.ba
exec sp_addumpdevice @devtype = 'disk' , @logicalname = 'DB1_bak3' ,@physicalname = 'G:\SQL_BACKUP\DB1_BAK3.ba
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