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

SQL Server Database Full Backup Expand / Collapse
Author
Message
Posted Monday, August 19, 2013 5:33 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 4:14 AM
Points: 17, Visits: 112
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
Post #1485735
Posted Monday, August 19, 2013 5:57 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 8:18 AM
Points: 476, Visits: 1,754
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 .)
Post #1485741
Posted Monday, August 19, 2013 6:30 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 @ 11:19 AM
Points: 42,806, Visits: 35,927
Have you considered backing up to disk and moving the backup to tape later?


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 #1485760
Posted Tuesday, August 20, 2013 6:18 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 10:08 AM
Points: 4,188, Visits: 3,226
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



Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1486215
Posted Thursday, August 22, 2013 5:51 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 4:14 AM
Points: 17, Visits: 112
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
Post #1487186
Posted Thursday, August 22, 2013 5:53 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 4:14 AM
Points: 17, Visits: 112
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.

Post #1487187
Posted Thursday, August 22, 2013 6:14 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 5:20 AM
Points: 519, Visits: 164
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.
Post #1487196
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse