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:DBBackupe k_03June2011_1.BAK’,

    DISK
=
N’I:DBBackupe k_03June2011_2.BAK’,

    DISK
=
N’I:DBBackupe k_03June2011_3.BAK’,

    DISK
=
N’I:DBBackupe k_03June2011_4.BAK’,

    DISK
=
N’I:DBBackupe k_03June2011_5.BAK’,

    DISK
=
N’I:DBBackupe k_03June2011_6.BAK’,

    DISK
=
N’I:DBBackupe k_03June2011_7.BAK’,

    DISK
=
N’I:DBBackupe k_03June2011_8.BAK’,

    DISK
=
N’I:DBBackupe 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: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

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *