Breaking News
Loading...
31/10/2013

Mirrored Backup and Restore and Split File Backup

Here is a useful knowledge a DBA should know. If we perform backup a large database, we can split it into multiple files and store them in multiple places.

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

 
Toggle Footer