He was not talking about TFS in any way, but he is an enterprise guy and he highlighted the value of backups. Apparently he runs a stored procedure before every backup to ensure that the database is not corrupted.
His script is for SQL Server 2000, and I thought about how it might compare to my maintenance plan that I run every night with my backup:
As you see, for each task I have collected all the TFS relevant databases together. (As good practice, there shouldn’t be any others on your server)
The main point of this task collection is to check the database integrity before I backup. If you backup a corrupt database, you will then have to go back an indefinite amount of time to fix it.
I then clean up TFS backups older than 2 weeks… this is because I don’t want the backup drive to fill up, and potentially lose the latest backups when I am not around.
I make a full backup each time. I know that backing up the difference between backups would be far more efficient, but I prefer to be safe. I will probably switch to daily incremental backups, and weekly full backups in future.
I don’t really expect any great performance boost from rebuilding the indices and updating the statistics, but there is no cost at doing this anyway at 01:00 everyday.
How does everyone else do this? If you can suggest any enhancements then that would be great 🙂