Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Towards the innovative SQL ideas
Browse by Tags · View All
sql server 125
sql 124
ms sql server 119
ms sql 118
database 109
tsql 81
#SQL Server 78
t-sql 75
#sql 71
sql server general 67

Archive · View All
April 2011 14
July 2011 12
May 2011 12
August 2011 11
June 2011 10
September 2011 8
December 2011 6
November 2011 6
September 2013 5
June 2013 5

SQL Server - Split Database Full Backup to Multiple files

Aug 24 2011 12:00AM by Paresh Prajapati   

As a maintenance activity, DBA need to take care of the database backups and monitoring for the same because database backups are more important in recovery. Production live databases have more size and due to that it is require more time and IO to complete it. You can read my earlier posts Automated All Databases Backups Script and Full Backup database from SSMS

You may know those full database backups are require more time to complete it as well as backup size will be more for the same. So SQL Server have one more features to database backups can split to multiple files. Using this way SQL Server run the multiple thread of database backups for each files and can be completed faster comparatively with less time and IO. 

Let us see the demonstration of the complete database backups and splitted database backups to multiple file and how it will work and advantage for the same. Here we will perform full database backup of ReportServer database.

We can perform the split backup into multiple files with management studio.



How can we do with TSQL?

#1. Complete BackupWe are running below script for the complete backups and we will see the IO and time required to complete the backup and this will into single file only. 
SET STATISTICS IO ON
SET STATISTICS TIME ON

BACKUP DATABASE ReportServer 
TO DISK = 'D:\DBBackups\ReportServer\ReportServer.bak'

SET STATISTICS IO OFF
SET STATISTICS TIME OFF

You can see from image above the IO and time , now please compare those factors with split backups and i will perform from the below script by method #2. 

#2. Split Backup to multiple files: We are running below script for the Split Backup and we will see the IO and time required to complete the backup and this will into multiple file only.
SET STATISTICS IO ON
SET STATISTICS TIME ON

BACKUP DATABASE ReportServer TO 
 DISK = 'D:\DBBackups\ReportServer\ReportServer_Split1.bak'
,DISK = 'D:\DBBackups\ReportServer\ReportServer_Split2.bak'
,DISK = 'D:\DBBackups\ReportServer\ReportServer_Split3.bak'

SET STATISTICS IO OFF
SET STATISTICS TIME OFF

Please go through the below screen also which mention the size of the database backups how they are split. 


Conclusion
Now you have more idea from all of the backup shots as how the split backup can reduce time and IO to perform the database backups.

Tags: sql, sql server, ms sql, ms sql server, t-sql, #SQL Server, #sql, database, sql server general, Maintenance, SQL Scripts, backup, query, Performance,


Paresh Prajapati
6 · 23% · 7465
9 Readers Liked this
Paresh Prajapati Liked this on 8/24/2011 10:53:00 PM
Profile · Blog · Facebook · Twitter
Kirti M.Darji Liked this on 8/25/2011 3:51:00 AM
Profile · Blog · Facebook · Twitter
syedasrarali Liked this on 9/5/2011 6:00:00 AM
Profile · Facebook
Hiren K Liked this on 9/6/2011 4:29:00 AM
Profile
Guru Samy Liked this on 3/1/2012 5:35:00 AM
Profile · Blog
Jacob Sebastian Liked this on 3/1/2012 6:09:00 AM
Profile · Blog · Facebook · Twitter
krunal Liked this on 3/27/2012 3:09:00 AM
Profile · Facebook
Nirav Liked this on 3/28/2012 12:45:00 AM
Profile · Blog · Facebook · Twitter
kavan dhruv Liked this on 2/13/2013 3:27:00 AM
Profile · Facebook · Twitter
9
Liked
 
0
Lifesaver
 
0
Refreshed
 
 



Submit

2  Comments  

  • Sorry Paresh, but i dont agree with this. Time for second method is less because second time backup speed was 23.889 MBs/Sec (possibly server has less pressure at that time) as compared to first one i.e. 20.095 MBs/Sec. I have not tested but possibly it second method could be better when you are splitting between different physical hard drives, instead of single one with same path.

    commented on Sep 20 2012 8:02AM
    aasim abdullah
    23 · 7% · 2373
  • I already knew this but that's the kind of tip I like to see. Nicely done, Paresh! Definitely got a "LIKE" from me.

    commented on Feb 15 2013 8:28PM
    Jeff Moden
    159 · 1% · 305

Your Comment


Sign Up or Login to post a comment.

"SQL Server - Split Database Full Backup to Multiple files" rated 5 out of 5 by 9 readers
SQL Server - Split Database Full Backup to Multiple files , 5.0 out of 5 based on 9 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]