How to Backup Database in Multiple Files

As the subject goes lot of DBA think why to do database backup in multiple files; what’s the use and in what scenario this will be applicable; well if you have a huge database and the backup file is huge (ex: +500 GB) and you have to copy the same on different location for Replication, Database Mirroring, Log-shipping and copy the same over tape.

Copying the backup file to a network location will take too much of time where in copy multiple files of single backup will be much faster (Robocopy will also take much time in copying single file on to the network location where in it will take much less time in copying multiple files as Robocopy will user Multiple threads for Multiple files)

 

Here we will learn how to do database backup in multiple files using GUI or T-SQL

 

 

Backup File created at the Location provided

 

T-SQL Code for backing up database in multiple files

BACKUP DATABASE [e k] TO DISK = N’I:\DBBackup\e k_03June2011_1.BAK’,     DISK = N’I:\DBBackup\e k_03June2011_2.BAK’,     DISK = N’I:\DBBackup\e k_03June2011_3.BAK’,     DISK = N’I:\DBBackup\e k_03June2011_4.BAK’,     DISK = N’I:\DBBackup\e k_03June2011_5.BAK’,     DISK = N’I:\DBBackup\e k_03June2011_6.BAK’,     DISK = N’I:\DBBackup\e k_03June2011_7.BAK’,     DISK = N’I:\DBBackup\e k_03June2011_8.BAK’,     DISK = N’I:\DBBackup\e k_03June2011_9.BAK’     WITH NOFORMAT, NOINIT,     NAME = N’e k-Full Database Backup’,     SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO

 

 

While restoring the database from backup provide all the backup filename with location

RESTORE DATABASE [e k] FROM DISK = N’I:\DBBackups\e k_03June2011_1.bak’, DISK = N’I:\DBBackups\e k_03June2011_2.bak’, DISK = N’I:\DBBackups\e k_03June2011_3.bak’, DISK = N’I:\DBBackups\e k_03June2011_4.bak’, DISK = N’I:\DBBackups\e k_03June2011_5.bak’, DISK = N’I:\DBBackups\e k_03June2011_6.bak’, DISK = N’I:\DBBackups\e k_03June2011_7.bak’, DISK = N’I:\DBBackups\e k_03June2011_8.bak’, DISK = N’I:\DBBackups\e k_03June2011_9.bak’, WITH FILE = 1, MOVE N’e kData’ TO N’H:\Mount3\e k.mdf’, MOVE N’e kdata1′ TO N’H:\Mount4\e k_1.ndf’, MOVE N’e kdata2′ TO N’H:\Mount5\e k_2.ndf’, MOVE N’e kdata3′ TO N’H:\Mount6\e k_3.ndf’, MOVE N’e kdata4′ TO N’H:\Mount7\e k_4.ndf’, MOVE N’e kdata5′ TO N’H:\Mount8\e k_5.ndf’, MOVE N’e kdata6′ TO N’H:\Mount9\e k_6.ndf’, MOVE N’e kdata7′ TO N’H:\Mount10\e k_7.ndf’, MOVE N’e kdata8′ TO N’H:\Mount3\e k_8.ndf’, MOVE N’e kdata9′ TO N’H:\Mount4\e k_9.ndf’, MOVE N’e kLog’ TO N’O:\e k_Log.ldf’, NORECOVERY, NOUNLOAD, STATS = 1 GO