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 TO DISK DISK DISK DISK DISK DISK DISK DISK WITH NAME = SKIP, GO |
While restoring the database from backup provide all the backup filename with location
|
RESTORE DATABASE [e k] FROM DISK = N’I:DBBackupse k_03June2011_1.bak’, DISK = N’I:DBBackupse k_03June2011_2.bak’, DISK = N’I:DBBackupse k_03June2011_3.bak’, DISK = N’I:DBBackupse k_03June2011_4.bak’, DISK = N’I:DBBackupse k_03June2011_5.bak’, DISK = N’I:DBBackupse k_03June2011_6.bak’, DISK = N’I:DBBackupse k_03June2011_7.bak’, DISK = N’I:DBBackupse k_03June2011_8.bak’, DISK = N’I:DBBackupse k_03June2011_9.bak’, WITH FILE = 1, MOVE N’e kData’ TO N’H:Mount3e k.mdf’, MOVE N’e kdata1′ TO N’H:Mount4e k_1.ndf’, MOVE N’e kdata2′ TO N’H:Mount5e k_2.ndf’, MOVE N’e kdata3′ TO N’H:Mount6e k_3.ndf’, MOVE N’e kdata4′ TO N’H:Mount7e k_4.ndf’, MOVE N’e kdata5′ TO N’H:Mount8e k_5.ndf’, MOVE N’e kdata6′ TO N’H:Mount9e k_6.ndf’, MOVE N’e kdata7′ TO N’H:Mount10e k_7.ndf’, MOVE N’e kdata8′ TO N’H:Mount3e k_8.ndf’, MOVE N’e kdata9′ TO N’H:Mount4e k_9.ndf’, MOVE N’e kLog’ TO N’O:e k_Log.ldf’, NORECOVERY, NOUNLOAD, STATS = 1 GO |