The advantage to doing this is that the backup process can run using multiple threads and therefore finish faster as well as having much smaller files that can be moved across the network or copied to a CD or DVD. Another advantage to writing to multiple files is if you have multiple disk arrays you can write your backup files to different arrays and therefore get better I/O throughput.
For example:
BACKUP DATABASE AdventureWorks TO DISK = 'C:\Backup\MultiFile\AdventureWorks1.bak', DISK = 'C:\Backup\MultiFile\AdventureWorks2.bak', DISK = 'C:\Backup\MultiFile\AdventureWorks3.bak' GO
And while performing a backup, we could make a copy of backup to different location.
For example:
BACKUP DATABASE AdventureWorks TO DISK = 'C:\Backup\MultiFile\AdventureWorks1.bak', DISK = 'C:\Backup\MultiFile\AdventureWorks2.bak', DISK = 'C:\Backup\MultiFile\AdventureWorks3.bak' MIRROR TO DISK = 'C:\Backup\MirrorFile\AdventureWorks1.bak', DISK = 'C:\Backup\MirrorFile\AdventureWorks2.bak', DISK = 'C:\Backup\MirrorFile\AdventureWorks3.bak' WITH FORMAT GO
Refer:
http://blog.sqlauthority.com/2009/09/02/sql-server-mirrored-backup-and-restore-and-split-file-backup-2/
http://www.mssqltips.com/sqlservertip/935/backup-to-multiple-files-for-faster-and-smaller-sql-server-files/
0 comments:
Post a Comment