How to backup a database into a number of smaller files ?

[kusanagihk]

To all,

How to backup a database into a number of smaller files ?
For example, can I can fully backup a DB of 10 MB into 10 files (each 1
MB)???

The problem I've met is that the DB backup file is too large, over 4
GB, and even Winzip can't compress it (after compressing, around 80 %
of compression rate is possible)

Thanks!

From Jason (Kusanagihk)

[Simon Hayes]

See BACKUP in Books Online - you can backup to multiple devices or
files:

backup database MyDB to disk = 'c:\bak\MyDB.ba k1', disk =
'c:\bak\MyDB.ba k2'

But you can't control exactly what goes into each file, so you may find
they're not all the same size.

Simon

[kusanagihk]

To Simon,

Thanks for your reply; I've tried the command and it worked. But I
think 1 of the file is the Data file, while the other is the
transaction log file (since this command only accepts 2 "disk =
'c:\XXX\01.bak' " parameter.....)

And so the first file must be much bigger in size; while the other is
smaller .....

Maybe due to the OS design, I don't really expect I could pack a DB
like the way it should be under Unix based systems.....

I can't find even 3rd party tools on splitting the backup file

From Jason (Kusanagihk)

[Greg D. Moore \(Strider\)]

<ku********@gma il.com> wrote in message
news:11******** *************@f 14g2000cwb.goog legroups.com...

To Simon,

Thanks for your reply; I've tried the command and it worked. But I
think 1 of the file is the Data file, while the other is the
transaction log file (since this command only accepts 2 "disk =
'c:\XXX\01.bak' " parameter.....)

That's inaccurate. It accepts up to 64 filenames.

backup database northwind to disk='\\FOO\sha re1\nw_1.bak',
disk='\\FOO\sha re1\nw_2.bak', disk='\\FOO\sha re1\nw_3.bak'

Will back up the northwind database to the three files named and attempt to
do so in equal parts.

You could do this up to 64 files...

And so the first file must be much bigger in size; while the other is
smaller .....

Maybe due to the OS design, I don't really expect I could pack a DB
like the way it should be under Unix based systems.....

I can't find even 3rd party tools on splitting the backup file

From Jason (Kusanagihk)

[kusanagihk]

To Greg.

But when I try to key the command above; it just throw out an error
stating "it needs 2 arguments but you have provided 3 arguments....."

It is the same when I give it only 1 argument (ie. only 1 disk = XXX)

From Jason (Kusanagihk)

[Simon Hayes]

Can you post the exact command you executed and the full error message?
There's no error in sysmessages which has the format that you describe
(although some are similar), so I'm not sure exactly what you're
seeing.

Simon

[Greg D. Moore \(Strider\)]

<ku********@gma il.com> wrote in message
news:11******** **************@ z14g2000cwz.goo glegroups.com.. .

To Greg.

But when I try to key the command above; it just throw out an error
stating "it needs 2 arguments but you have provided 3 arguments....."

You must be doing something wrong since I cut and pasted that from a test I
performed that worked.

(only thing I did was change the name of the server I backed up to.)

It is the same when I give it only 1 argument (ie. only 1 disk = XXX)

From Jason (Kusanagihk)

[julian_muir]

I have used Winzip 9.0 to compress 20 GB files (the zip compression that
comes with windows has a much lower limit)
<ku********@gma il.com> wrote in message
news:11******** **************@ l41g2000cwc.goo glegroups.com.. .

To all,

How to backup a database into a number of smaller files ?
For example, can I can fully backup a DB of 10 MB into 10 files (each 1
MB)???

The problem I've met is that the DB backup file is too large, over 4
GB, and even Winzip can't compress it (after compressing, around 80 %
of compression rate is possible)

Thanks!

From Jason (Kusanagihk)

[kusanagihk]

To Julian

Oh, my winzip is only version 7.X, maybe that's why I can't zip a db
file over 4 GB...

From Jason (Kusanagihk)

[kusanagihk]

To all,

I don't know what I've done earlier, but now I could use the command
"backup database to disk = 'xxx.bak', disk='yyy.bak' ...."

but the matter is, I could not find the backuped file in my PC; eg.
I've quoted it to backup the db to c:\temp\001.bak and 002.bak; but I
can't find these 2 files throughout the PC.....

nothing was wrong as I've been given a message stating it is working
fine

Processed 320 pages for database 'northwind', file 'Northwind' on file
3.
Processed 1 pages for database 'northwind', file 'Northwind_log' on
file 3.
BACKUP DATABASE successfully processed 321 pages in 0.283 seconds
(9.266 MB/sec).

The command I've used is :

backup database northwind
to disk='c:\temp\n w01.bak', disk='c:\temp\n w02.bak',
disk='c:\temp\n w03.bak'

Anything wrong again ?

From Jason (Kusanagihk)