Expert Advise

I am having a very large database and the day to day transactions are also huge. I do daily full backups but it slows down the server and hence as a result i am planning to implement other backup strategy for faster and efficient backup and restore. I am thinking of file group backups but practically i have never implemented this and hence would like to know the consequences and drawbacks if any. Also if anyone could suggest an better approach i welcome. I would also seek an advise on how to implement file group backups in vldbs.

Thanks

How big is your database?

How frequently it is getting updated?
What standby method you are following, is it log shipping or standby servers on a Disaster Recoveyr location?

I feel Backup strategy for small or big databases will be same as long as you are maintaining the standby servers for your business continuity.

http://www.simple-talk.com/sql/backup-and-recovery/sql-server-2005-backups/ fyi.

Also visit my blog for the patterns in providing high availability & business continuity.

|||

Thanks for the article. My database is around 200 GB and it updates every minute as it is a database of call centre. Further before i joined as DBA, the company was using daily full backup but it consumed a lot of time and resources. So i am planning to implement the following scenerio;

Full backup : Once week (sunday)

Differential backup : daily after office hours

Transaction Log backup : every 2 hours.

Kindly give me your valuable feedback for this scenerio. Is there anything else i need to do. Further i do shrinking of log file every 2-3 days manually depending upon growth. Should i continue doing this?

|||

In terms of: . Further i do shrinking of log file every 2-3 days manually depending upon growth. Should i continue doing this?

No...you shouldn't continute to do that. Regularly shrinking the log isn't a good idea. The logs will just continue to expand and shrinking, expanding, shrinking, expanding wastes a lot of resources. Increase the frequency of your log backups if needed but let the logs have the space they need to support whatever operations happen between log backups. Also, shrinking the logs leads to fragmentation at the file level.

If you are experiencing bottlenecks when backing up, you should use Performance Monitor and investigate if you have any problems with disks or network throughput if backing up to a network location. The following article has some guidelines for backup performance as well as some tips on what to monitor if experiencing bottlenecks when doing backups:

http://www.sql-server-performance.com/backup_restore_tuning.asp

-Sue