May 16, 2013 at 10:01 am
Hello Pros,
I manage 100 + SQL servers and wanted to find a way to backup all databases to multiple .bak files (striping) regardless of the DB size. Below is the closes one I got on google. can anyone shed some light\share script that can accomplish this. Thanks in advance for your help.
DECLARE @sql VARCHAR(max);
SELECT @sql = COALESCE(@sql,'')+
'BACKUP DATABASE '+db.name+' '+CHAR(13)+
'TO DISK = ''c:\'+db.name+'_1.bak'','+CHAR(13)+
'DISK = ''c:\'+db.name+'_2.bak'','+CHAR(13)+
'DISK = ''c:\'+db.name+'_3.bak'','+CHAR(13)+
'DISK = ''c:\'+db.name+'_5.bak''with INIT, compression'+CHAR(13)+CHAR(13)
FROM sys.databases db
WHERE name NOT IN ('tempdb','pubs','AdventureWorks','AdventureWorksDW')
exec(@sql)
---PRINT(@sql)
May 16, 2013 at 11:37 am
You will only see value in striping a backup when you have multiple drives available - and separate IO paths to those drives. Striping a backup to the same location could actually slow your backups down.
What is the problem you are trying to resolve?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 20, 2013 at 10:23 am
Jeffrey Williams 3188 (5/16/2013)
You will only see value in striping a backup when you have multiple drives available - and separate IO paths to those drives. Striping a backup to the same location could actually slow your backups down.
That's not true when using native compression. Each file backs up with a single thread, so to make use of multi-threading for the compression, you need multiple backup files. The single-threaded nature of backup compression is almost always the bottleneck in modern servers, so even if writing to a single LUN/volume there are serious performance gains from multiple files.
I'd recommend looking at Ola's excellent backup scripts, which have a parameter for splitting to multiple files:
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply