Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Database Backup query Expand / Collapse
Author
Message
Posted Tuesday, December 2, 2008 2:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 7, 2012 2:14 PM
Points: 7, 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.
Post #611896
Posted Tuesday, December 2, 2008 2:46 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 27, 2012 9:09 PM
Points: 136, 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
Post #611902
Posted Tuesday, December 2, 2008 2:46 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:10 PM
Points: 42,986, Visits: 36,141
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 2008, MVP
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

Post #611903
Posted Tuesday, December 2, 2008 3:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 7, 2012 2:14 PM
Points: 7, 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


Post #611914
Posted Tuesday, December 2, 2008 3:15 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:10 PM
Points: 42,986, Visits: 36,141
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 2008, MVP
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

Post #611922
Posted Tuesday, December 2, 2008 3:17 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 27, 2012 9:09 PM
Points: 136, 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
Post #611924
Posted Tuesday, December 2, 2008 3:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 7, 2012 2:14 PM
Points: 7, 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
Post #611931
Posted Tuesday, December 2, 2008 3:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 7, 2012 2:14 PM
Points: 7, 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 !
Post #611933
Posted Tuesday, December 2, 2008 4:30 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 27, 2013 6:00 AM
Points: 806, Visits: 413
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
Post #611946
Posted Tuesday, December 2, 2008 4:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 7, 2012 2:14 PM
Points: 7, 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 ?
Post #611954
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse