SQL Server Performance Forum – Threads Archive

Preferred backup plan

This may be a dumb question, but what is the preferred way for performing backups on a clustered SQL Server database. Is it better to backup to use: 1. BACKUP DATABASE [master] TO [masterbackup] WITH INIT , NOUNLOAD , NAME = N’master db backup’, NOSKIP , STATS = 10, NOFORMAT or 2. EXEC master.dbo.xp_sqlmaint N’-D master -CKAL -CKCAT -BKUPDB -BKUPMEDIA DISK -USEDEFDIR -BKUPONLYIFCLEAN -VRFYBACKUP -DELBKUPS 1DAYS’ They both do the same thing, but the date is attached to option 2. Does it really matter how a clustered db is backed up? Please let me know what you all think. Thanks.

Also, what is the best way to perform transaction log backups as well. Thanks.

It doesn’t really matter how a clustered db is backed up. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] You might want to schedule it with standard jobs and scripts. You can find sample scripts here: http://weblogs.sqlteam.com/tarad</a> MeanOldDBA [email protected] When life gives you a lemon, fire the DBA.

There is no specific rule to backup, but ensure the backup database file is in good condition. You can achieve this my restoring it on a test server to ensure there are no issues with the file, occasionally. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Thanks. I just keep hearing that using BACKUP DATABASE was the way people suggest to backup on a clustered database, but I could never ascertain why it was better than the other method.

The other option does the same thing. Most people hate maintenance plans because generally speaking, they suck. You can add the date logic easily to your scripts and schedule them. Tara has some good examples. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

]]>