Database Backup query

Question

Database Backup query

rajeevtenneti

SSC Veteran

Points: 279
More actions
December 2, 2008 at 2:37 am

#196757

Hi All ,
Is it possible to take a backup of a big database in chunks of 10gb each instead of a single big file of around 50-60 GB ? We have currently a tape backup configured and at times due to network issues we have to start the tape backup all over again ! instead if we can do it on 5 or 6 stripes it will be easier for us to use and take the tape backup .
Kindly reply if you aware of anything of this sort ,Any suggesstions on Compression are also welcome ! (compression in MSSQL )
we are currently using mssql2000 !!
Thank You ,
Rajeev.

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

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

SQL_EXPAT SSCrazy Points: 2708 More actions · Answer 1

you can strip your backups across multiple files but this won't do it in chunks like you want. The best way to stop network failures affecting your backups is to backup to a local disk first and then copy to tape. Try a compression utility like Litespeed to compress your backups. How big are your backups and how long do they currently take?

thanks

SQL_EXPAT

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

Can you backup top the local disk and then copy the backup to take.

we are currently using mssql2000 !!

Please post SQL 2000-related questions in the SQL 2000 forums. If you post in the 2005 forums, you're very likelt to get 2005-specific solutions.

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

rajeevtenneti SSC Veteran Points: 279 More actions · Answer 3

Dear Gilamonster and cranfield

Thank you for replies.Rightnow we were using a tool for backing up the database

mbactrack from BMC. Backup of a 43GB(95% full) occupies 9 Gb on local disk !

Unfortunately it was decided to move on to MSSQL backups and we tested on

a development dtabase a db of 40Gb with 38GB data takes 37GB on disk !!

Regarding the network issue.We faced issues with network when doing tape backup

from local disk .Issue with tape backup is that when it is trying to takethe bkp of

this big chunk at times is getting disconnected ,reasons not yet know to us.

So I thought if it is possible to take bkp on three to four files of 10Gb each it could

help even when tapebkp fails ! We need not have to start the tape backupall over again .

Instead we can identify the files not backed up and start the tape bkp again.

Thank you for helping

Best Regards,

Rajeev

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

rajeevtenneti (12/2/2008)
Unfortunately it was decided to move on to MSSQL backups and we tested on
a development dtabase a db of 40Gb with 38GB data takes 37GB on disk !!

Yes, it will. SQL backups aren't compressed and so will take the full space of the data in the DB.

So I thought if it is possible to take bkp on three to four files of 10Gb each it could
help even when tapebkp fails ! We need not have to start the tape backupall over again .
Instead we can identify the files not backed up and start the tape bkp again.

There's no easy way to do that. If the database has 4 filegroups (of 10GB each) you can do filegroup backups, but that's overly complex for this problem. You can stripe a SQL backup over multiple files, but it writes those files in parallel, not serially.

Either get more space on the drive (9GB free is not much), get a new drive that the backups will fit onto, or go back to using the 3rd party tool with backup compression.

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

SQL_EXPAT SSCrazy Points: 2708 More actions · Answer 5

Rajeev

Like I said before you can stripe your backup across multiple smaller files. If this helps with your backup to tape then thats great but you really should have your network seen to. Network outages are bad news -this is your real problem that needs to be addressed.

We backup terrabytes of data to disk and then to tape everyday with no problems.

cheers

thanks

SQL_EXPAT

rajeevtenneti SSC Veteran Points: 279 More actions · Answer 6

Yes the issue with the network should be resolved.

Can you tell me how we can stripe the backups ?Reg time (this 45GB database takes 45 minutes to complete and bkp size is 9 GB )

i am familiar with sybase we use " stripe on " to take it on stripes ,but that doesent seem to work in MSSQL 🙁

rajeevtenneti SSC Veteran Points: 279 More actions · Answer 7

You say you have terabytes bkd up to tape ,are these of mssql files? I am

interested in knowing if the MSSQL supports db's of Terabyte size !

and how many of them it can support effectively per server !

Atul DBA Hall of Fame Points: 3610 More actions · Answer 8

Hi Rajeev,

The best you can achive by using the third party tool. Some of them are as LiteSpeed by Quest Software and SQLBackup by Radegate.

I am not promoting any thirdparty tool here but your requirement can be met by those tools easily.

Regards

Atul

rajeevtenneti SSC Veteran Points: 279 More actions · Answer 9

Hi Atul,

Thank you for reply.You mean these third party tools enable striping of backup ?

We were already using a third party tool (mbacktrack from BMC ) But management doesent want to go for extending the licence anymore is this striping possible with MSSQL backup anyway ?

Atul DBA Hall of Fame Points: 3610 More actions · Answer 10

Hi Rajeev,

The LiteSpeed compress your database backup file by 75-95 % which means if your database file is about 50GB in large, the compressed backup file will be only of less than 5GB. I am not sure as how much BMC tool is compressing the data file.

Data stripping is a not a gool solution as if any file got corrupt, the whole data file set will be useless. I will not suggest you to go for data stripping rather use better compression backup product.

Thanks

Atul

rajeevtenneti SSC Veteran Points: 279 More actions · Answer 11

rajeevtenneti

SSC Veteran

Points: 279

December 2, 2008 at 5:30 am

#906156

okie I will take your advice.Thank you. 🙂

SQL_EXPAT SSCrazy Points: 2708 More actions · Answer 12

backup database database_27 to disk = 'c:\file1.bak', disk = 'c:\file2.bak',disk = 'c:\file3.bak'

thanks

SQL_EXPAT

SQL_EXPAT SSCrazy Points: 2708 More actions · Answer 13

Litespeed backups. MSSQL supports multi-terabyte databases. Petabytes actually.

thanks

SQL_EXPAT

philcart SSC-Forever Points: 47794 More actions · Answer 14

I'd like to put in a plug for Hyperbac (www.hyperbac.com). It's made by the original developers of Litespeed.

Massive advantage is you just use any normal backup method within SQL Server (Maintenance Plan, Management Studio or T-SQL query) and the backups are compressed.

No complicated syntax to learn, no extra extended stored procedures to install, no process running within the SQL memory space stealing memory from the data cache.

--------------------
Colt 45 - the original point and click interface