SQL Server Performance

Preferred backup plan

Discussion in 'SQL Server Clustering' started by unitycard, Sep 26, 2005.

  1. unitycard New Member

    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.
  2. unitycard New Member

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

    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:<br /><br /<a target="_blank" href=http://weblogs.sqlteam.com/tarad>http://weblogs.sqlteam.com/tarad</a><br /><br /><br />MeanOldDBA<br />derrickleggett@hotmail.com<br /><br />When life gives you a lemon, fire the DBA.
  4. satya Moderator

    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.
  5. unitycard New Member

    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.
  6. derrickleggett New Member

    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
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.

Share This Page