Backing up database larger than 50GB

  • Johannsky

    SSC Enthusiast

    Points: 144

    Hello,

    Another issue i encounter today, does SQL server 2005 limits the data transfer on up to 50GB only when backing up on a network.

    Thanks again.

  • Jeffrey Williams 3188

    SSC Guru

    Points: 88258

    Johannsky (7/19/2008)


    Hello,

    Another issue i encounter today, does SQL server 2005 limits the data transfer on up to 50GB only when backing up on a network.

    Thanks again.

    We had a similar issue when backing up over a network. This is caused by the network configuration and has nothing to do with SQL Server itself. You can reproduce the issue by trying to copy a +50GB file from/to the network location outside of SQL.

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

  • Perry Whittle

    SSC Guru

    Points: 233804

    what backup product are you using?

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

    [font="Tahoma"]"Ya can't make an omelette without breaking just a few eggs"[/font] 😉

  • Johannsky

    SSC Enthusiast

    Points: 144

    what backup product are you using?

    I am using the default backup tool of SQL Server 2005. I run the backup using job.

  • Vidhya Sagar

    SSCrazy Eights

    Points: 8216

    [font="Verdana"]When you try to take the backup of 50GB db on network share the time taken will be high and if a network glitch happens then the backup will get stopped. Hence its better to take the backup in local drive and copy it to the network drive using robocopy. This way you can easily copy it.[/font]

  • Perry Whittle

    SSC Guru

    Points: 233804

    Johannsky (7/20/2008)


    I am using the default backup tool of SQL Server 2005. I run the backup using job.

    having you considered something like Redgate SQLBackup or Quest Litespeed?

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

    [font="Tahoma"]"Ya can't make an omelette without breaking just a few eggs"[/font] 😉

  • Johannsky

    SSC Enthusiast

    Points: 144

    Hi,

    Thank you for all positive feedback.

    I will consider all your comments and suggestions.

    But, is it a bug or something on SQL Server 2005?

    Thanks

  • GSquared

    SSC Guru

    Points: 260824

    It's not a bug in SQL Server. It's a network issue.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • ALIF-662928

    SSCertifiable

    Points: 6054

    We backup our db of size 60Gb directly to a remote network drive, it takes time compare to the local drive, create a maintainace plan of your backup and i hope it will work for you.

  • Johannsky

    SSC Enthusiast

    Points: 144

    This might be a network issue.

    Thanks for your help.

  • Gail Shaw

    SSC Guru

    Points: 1004474

    Hae you consided backing up to disk then copying the backup file across the network?

    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
  • jburkman

    Right there with Babe

    Points: 768

    It may be the IRPStackSize bug. You can google it, but essentially you need to alter/set a registry setting. The max value is 50, but I'm finding that even with that we are hitting the wall with our 45+ gig backups. For this reason we are switching to Redgate SQL backup. Here's my solution notes below that I cribbed from some web source:

    These error messages can indicate the IRPStackSize bug on the other machine (the server that has the share). On that computer open the event log and check for event ID 2011. If this is present, it is a strong indication for this particular bug.

    To repair it, you have to reset the IRPStackSize parameter back to its default value of 15 on the server where the share is.

    HKEY_LOCAL_MACHINE

    \SYSTEM

    \CurrentControlSet

    \Services

    \lanmanserver

    \parameters

    Check for the presence of a value named IRPStackSize. If it doesn't exist, create it as type DWORD. With base set to decimal, enter the value 15.

    Reboot the computer.

    If a job fails because of this, you get nothing helpful for an error message. But if you directly try dragging the 'monguous file from A to B, you'll get an error message near the end about "server space".

  • Philip Barry

    SSCrazy

    Points: 2592

    There is a free product by Idera called 'SQLsafe Freeware Edition' that allows compressed backups over the network. You can download it from http://www.idera.com/Products/SQLsafefree/Default.aspx

    Maybe worth a look...

    Regards,

    Phil

Viewing 13 posts - 1 through 13 (of 13 total)

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