SQL Server Database Full Backup

  • Hi, All,

    We have configured Full Backup in Tape, Our Database Size is 400 GB (Compressed). It take 4-5 Hrs for full backup, but some time it takes so much time like 20-25 hrs and continuous running, can any body help me, what i have to do to find exact problem.

    I have chekk Even Viewer and SQL ServerLog, no problems exist in both.

    Plz suggest me the best solution bcz its very urgent for me.

    Regards

    Dharmendra

  • When are you scheduling it for, i.e. is anything else happening at the same time?

    Are you backing up across a network, and if so are there any network issues at the time?

    Do you need to take full backups every time? Could you use a mixture of full and differential, or something?

    (I hope an expert will be along in a minute :-).)

  • Have you considered backing up to disk and moving the backup to tape later?

    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
  • GilaMonster (8/19/2013)


    Have you considered backing up to disk and moving the backup to tape later?

    During a backup, your disk I/O is going to be high. You want to minimize the amount of time this takes so your database can still perform while the backup is running. Therefore, backing up to disk and then from disk to tape is the right approach because hard drives have greater throughput than any tape drive I've ever seen.

    You'll need a drive (physical drive, SAN space, etc.) to receive your database backups. You'll want this to be a separate physical disk from your data drive and log drive. Once the database backup is done, you can backup your backup files from there to tape. Because the backup files are on a separate physical drive, it won't impact disk I/O like backing up directly to tape would.

    Another related question is about your transaction log backups. Are you taking them throughout the day or just doing the full backup once per day? Taking transaction log backups will keep your log files smaller by marking log entries as backed up and available for use. Smaller files mean less to back up. It'll also be better if you need to recover from a disaster because then you won't loose everything since the last full backup.

    Speaking of disaster recovery, please be sure to test your recovery plan before you need it. The whole point of backups is to be able to recover, so you don't want your first time doing it to be the time when time is critical.

    HTH

  • Hi,

    As per my backup plan, we take full backup weekly, differential backup on daily basis and Transactional backup for 15 mints.

    My differential backup takes 15 mint for 15 GB in compressed mode on 17-8-13 but after this on 18-08-13 it had taken 5 hrs for 40 gb and so on til now.

    can any one help me how can i find the exact problems behind it.

    its very serious issue plz help me or advice me what step i have to follow..

    thanks

    Dharmendra

  • No others job schedule on that time.

    my backup directly goes to Tape with third party tool hdtp.

    there is not network issue, the bandwidth of network is fine.

  • Have you tried applying a server side trace to see what is happening with the Database during the backup period, and maybe running perfmon counters to check throughput of the data across the network.

    Might also be worth checking the availability of the Tape device during this time to make sure it is not cycling the tape. Possibly try running a ping every 2-3 minutes between the server and the tape device to make sure times don't suddenly dropoff during your backup.

    The only real option is to look at each part of the process and apply relevant logging to it until you find out where the problem exists then deal with it.

Viewing 7 posts - 1 through 6 (of 6 total)

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