Home > Uncategorized > How to Backup Database in Multiple Files

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

 

 

About these ads
Categories: Uncategorized
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: