Backup size should restrict to 4 GB

Question

Backup size should restrict to 4 GB

durai nagarajan

SSCoach

Points: 15778
More actions
June 27, 2011 at 8:03 am

#389843

Hello,
Monthly we have to copy the databases to a DVD and give a copy to the department head.
now the databse have crossed 4 GB.
is there a way to take FULL backupbut to split it to several parts when it reaches 4 GB mark. Baclup and Restore should happen without any other 3rd party tool.
thanks for help in advance.
Regards
Durai Nagarajan

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

You can stripe a backup over multiple devices, but you need to figure out how many to get the size you want.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

If you have a sql 2008 ent or 2008 R2 Standard you could always enable compressions.

However you can't restore there compress and then rebackup because you couldn't restore that on sql 2005.

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 3

does the job automatically backup to a DVD? i was thinking you backup to a file and then use some DVD burning app to span multiple DVD's.

or ask to buy a blu ray burner, i hear they are cheap now

Jeffrey Williams SSC Guru Points: 90293 More actions · Answer 4

Why not use a utility like WinRAR to compress and span (if needed). Most likely, using a compression utility will give you a long time before you have to actually span across DVD's.

I haven't used one recently, but - they usually come with the ability to create a self-extracting executable so the departments can easily just double-click the file on the DVD and expand the archive.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

... because he said no 3rd party app!

I agree tho... it can easily give between 50% and 90% compression.

I also like the blue-ray idea. Not the longest fix to put in place either.

george sibbald SSC Guru Points: 104210 More actions · Answer 6

how about good old winzip, that can hardly be called a third party app.

If the database is not being reindexed, that might be enough to take the backup size back below 4GB (for a while anyway)

---------------------------------------------------------------------

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

george sibbald (6/27/2011)
how about good old winzip, that can hardly be called a third party app.
If the database is not being reindexed, that might be enough to take the backup size back below 4GB (for a while anyway)

ya but how do u reindex when you are already out of room :-D.

george sibbald SSC Guru Points: 104210 More actions · Answer 8

I read it as the backup was too big to fit on a DVD, rather than data files out of space on disk.

---------------------------------------------------------------------

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

If you are going to throw out 3rd party compression programs, how about 7Zip (which is free).

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

george sibbald (6/27/2011)
I read it as the backup was too big to fit on a DVD, rather than data files out of space on disk.

Brainfart! I had another thread in mind (the one trying to circumbvant the 4 GB limit in sql express 2k5).

durai nagarajan SSCoach Points: 15778 More actions · Answer 11

Thanks for the posts guys.

Backup dome automatically through a job and maually write it on DVD.

Blueray option only management can decide on it.

i found out option of specifying 2 or more files name and it is splitting it equally.

is there a way to split first peice to 4GB and remaining on second. when it goes to 8GB above first 2 should be 4 gb and remining in third through any scheduler available in SQL 2005.

Note: windows zip is not zipiing a 4GB file. we cant use other third party tools.

thanks in advance.

Regards
Durai Nagarajan

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

durai nagarajan (6/28/2011)
is there a way to split first peice to 4GB and remaining on second. when it goes to 8GB above first 2 should be 4 gb and remining in third through any scheduler available in SQL 2005.

Not natively in a SQL backup. You can stripe to multiple pieces of the same size, but you need to work out how many that is.

I'm sure winrar has a command-line option, so you could backup, rar to 4GB chunks and burn in a job. Will take some work to get the right command syntax, but I'm pretty sure it's doable.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

durai nagarajan SSCoach Points: 15778 More actions · Answer 13

thanks

informed Sys admin team about it for winzip or win rar. waiting for reply.

just a thought is it not an issue SQL 2005 is giving option to specify file name manually(not auto with size limitations).

will this available atleast in sql 2008?.

Regards
Durai Nagarajan

Steve Jones - SSC Editor SSC Guru Points: 735712 More actions · Answer 14

If I understand your question, you want to know if you can specify the file names for multiple file backups?

This is a part of the BACKUP command. You enter the file names and paths in the command.

http://msdn.microsoft.com/en-us/library/ms186865.aspx