SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database Backup query


Database Backup query

Author
Message
rajeevtenneti
rajeevtenneti
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 23
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.
SQL_EXPAT
SQL_EXPAT
SSChasing Mays
SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)

Group: General Forum Members
Points: 632 Visits: 383
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)

Group: General Forum Members
Points: 220625 Visits: 46279
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
rajeevtenneti
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 23
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)

Group: General Forum Members
Points: 220625 Visits: 46279
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
SQL_EXPAT
SSChasing Mays
SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)

Group: General Forum Members
Points: 632 Visits: 383
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
rajeevtenneti
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 23
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 Sad
rajeevtenneti
rajeevtenneti
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 23
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 Kumar
Atul Kumar
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1348 Visits: 617
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
rajeevtenneti
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 23
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 ?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search