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


SQL Server Database Full Backup


SQL Server Database Full Backup

Author
Message
D.K.Mishra
D.K.Mishra
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 130
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
Beatrix Kiddo
Beatrix Kiddo
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2736 Visits: 4391
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 :-).)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87984 Visits: 45276
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


Ed Wagner
Ed Wagner
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16723 Visits: 10079
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
D.K.Mishra
D.K.Mishra
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 130
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
D.K.Mishra
D.K.Mishra
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 130
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.
RandomEvent
RandomEvent
Say Hey Kid
Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)Say Hey Kid (680 reputation)

Group: General Forum Members
Points: 680 Visits: 414
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.
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