Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Database backup striping Expand / Collapse
Author
Message
Posted Thursday, May 16, 2013 10:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, October 25, 2014 1:04 AM
Points: 9, Visits: 85
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)
Post #1453645
Posted Thursday, May 16, 2013 11:37 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, October 2, 2014 12:09 PM
Points: 4,358, Visits: 9,538
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
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #1453707
Posted Monday, May 20, 2013 10:23 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 8:09 AM
Points: 1,191, Visits: 9,888
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:

http://ola.hallengren.com/sql-server-backup.html
Post #1454621
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse