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.bak1', disk =
'c:\bak\MyDB.bak2'

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********@gmail.com> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.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\share1\nw_1.bak',
disk='\\FOO\share1\nw_2.bak', disk='\\FOO\share1\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********@gmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.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********@gmail.com> wrote in message
news:11**********************@l41g2000cwc.googlegr oups.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\nw01.bak', disk='c:\temp\nw02.bak',
disk='c:\temp\nw03.bak'

Anything wrong again ?

From Jason (Kusanagihk)

[kusanagihk]

To all,

Got the problem solved, well, the 'c:\temp\nw01.bak' actually refers to
the c:\ of the Server (holding the SQL Server and not my local PC); so
I can find the backup files on that Server instead!

And the error earlier I've quoted
"Server: Msg 3132, Level 16, State 1, Line 1
The media set for database 'northwind' has 3 family members but only 2
are provided. All members must be provided.
"
can be explained when I'm issuing the database backup command under the
enterprise manager (select the db, right-click --> all tasks, backup
db);

for example, if I've issue the backup database (initially) with 3 "to
disk = 'XXX.bak'" parameters, then all the backup later on must have
the 3 files.....

Though I can reset them under the enterprise manager; I wonder how
could I issue the command in resetting the "Media Set" options?

PS. simply I can see the 3 required backup files in the Enterprise
Manager

From Jason (Kusanagihk)

[Greg D. Moore \(Strider\)]

<ku********@gmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

To all,

Got the problem solved, well, the 'c:\temp\nw01.bak' actually refers to
the c:\ of the Server (holding the SQL Server and not my local PC); so
I can find the backup files on that Server instead!
Common mistake. Just have to remember, this is a client/server
architecture, so the client issues commands which occur ON the server.


And the error earlier I've quoted
"Server: Msg 3132, Level 16, State 1, Line 1
The media set for database 'northwind' has 3 family members but only 2
are provided. All members must be provided.
"
can be explained when I'm issuing the database backup command under the
enterprise manager (select the db, right-click --> all tasks, backup
db);

A good reason for learning to use T-SQL and doing it "by hand". :-)

for example, if I've issue the backup database (initially) with 3 "to
disk = 'XXX.bak'" parameters, then all the backup later on must have
the 3 files.....

Though I can reset them under the enterprise manager; I wonder how
could I issue the command in resetting the "Media Set" options?

No idea, I avoid using SQLEM for stuff like this.

PS. simply I can see the 3 required backup files in the Enterprise
Manager

From Jason (Kusanagihk)