Methods for Backing Up Databases / Transaction Logs - SQL 2005

Question

Methods for Backing Up Databases / Transaction Logs - SQL 2005

gratefuldba

SSC Veteran

Points: 279
More actions
April 10, 2010 at 10:50 am

#233664

Hi,
Just started working with a SQL 2005 server. SQL Server 2005 Service Pack 3 (build 9.00.4053.00).
There are a few user databases, none larger than 20 GB in size.
Does anyone have an opinion as to the merits and disadvantages of using SQL Server maintenance plans to implement our SQL Server database / transaction log backup strategy ?
Also, any opinions as to any better/preferred methods based on experience would be greatly appreciated.
At this point there is no budget for third party backup products, I'm told we need to stay with Microsoft for now.
Any opinions and experiences anyone is willing to share would be greatly appreciated !
Thanks !

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

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

vidya_pande SSCertifiable Points: 5275 More actions · Answer 1

maintenance plan just helps you to schedule the backups along with other maintenance activities. I dont see any harm if u use maintenance plan for backing up transaction log. I guess you have arrived at the schdule or frequency of the transaciton log backups.

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 2

gratefuldba (4/10/2010)
Does anyone have an opinion as to the merits and disadvantages of using SQL Server maintenance plans to implement our SQL Server database / transaction log backup strategy ?

Easiest way to go, half a dozen clicks and you implement your backup strategy - providing you have planned your backup strategy.

gratefuldba (4/10/2010)
Also, any opinions as to any better/preferred methods based on experience would be greatly appreciated.

I like to have full control of my environment so I personally prefer to write/schedule my own jobs/scripts - same performance, just my personal preference.

gratefuldba (4/10/2010)
At this point there is no budget for third party backup products, I'm told we need to stay with Microsoft for now.

SQL Server native backup is a great tool - never failed me.

For a 20 Gig database this is more than enough, I would start thinking on third party tools only for a very large environment.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

gratefuldba SSC Veteran Points: 279 More actions · Answer 3

gratefuldba

SSC Veteran

Points: 279

April 12, 2010 at 8:32 pm

#1150462

Thanks so much for the helpful advice !

gratefuldba SSC Veteran Points: 279 More actions · Answer 4

gratefuldba

SSC Veteran

Points: 279

April 12, 2010 at 8:33 pm

#1150463

Many thanks for your helpful reply !

Rob Schripsema SSCertifiable Points: 7469 More actions · Answer 5

For a 20 Gig database this is more than enough, I would start thinking on third party tools only for a very large environment.

Curious -- how large before you'd consider something to be a 'very large environment'? A 60 Gb database? A 500Gb database? What about a hundred different 6 Gb databases?

Rob Schripsema
Propack, Inc.

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 6

20GB native should be OK. the third party products are good for multiple servers and multiple schedules for different databases

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Looking at a third party tool may also be needed if you have limited space to store backups files. Or you could implement a zip routine to compress your backup files after they are created.