fredag 19 augusti 2011

Backing up SQL with DPM

Currently there are some questions regarding what is the best practice of backing up SQL databases with DPM 2010. First we must clarify what modes a SQL databases could be in. SQL databases can be in the following recovery models:

• Simple
• Full
• Bulk-full

The thing that differentiates the databases is that the simple database has no transactional log files and can therefore only use the Express-Full backup technique in DPM.

The best practice for SQL server databases is to run a weekly database consistency check (also called DBCC) for avoiding backing up a database with a corruption issues with DPM.

You should not run or use other backup applications on a SQL server that is protected with DPM. This will lead to that DPM will not be able to back up your databases. If you should use DPM to protect your SQL server databases use only DPM, nothing else.

Last but not least verify with the SQL Server administrator that the following task are not scheduled at the same time as the Express-Full backup is running:

• Check database integrity
• History cleanup
• Maintenance cleanup
• Rebuild index
• Reorganize index
• Shrink database
• Update statistics

3 kommentarer:

  1. Hi Robert,

    First, thanks for sharing.

    About backuping SQL databases with DPM, i can read the following :

    "Question: If I configure automatic adding of SQl databases, will DPM also automatically remove protection of databse if will be deleted ?

    I noticed that I had to manually remove protection of deleted databases?"

    "Answer: The reason is that if the DB is temporarily down, we dont want to delete it from protection."

    My question is :
    So, if automatic adding is configured and down database come back, it will be added to protection...

    So why not remove "temporary down/deleted" db instead of reporting errors every days, for every deleted databases ?

    Did you have any clue ?
    Thanks for sharing.

    Best Regards,

  2. Hi Julien!

    Thank you for kind words.

    I think you are absolutely right. But since DPM uses VSS to manage the workloads my guess is that this is depending on a limitation of the SQL VSS.

  3. Thanks for your answer.