I recently came upon a problem and it may benefit someone else so I am going to throw this one out there.
When auditing your database infrastructure, please make a note of if the service broker is enabled anywhere!
You ask, why?
Well, when restoring a SQL Server Database, it does not re-enable the Service Broker for that database if it was previously enabled!
So how do you determine if it is enabled?
One of the key things to keep in mind is that if you are turning it on, it will try to wait for all transactions to finish. The best way to get around this is to set the database to single user mode, enable the broker, and then set it back to multi-user.
immediate then run:
new_broker and then run:
One thing that I have encountered at many companies is the complete lack of database backups. We all know that to really do your job properly you need a consistent backup plan.
Some of the key aspects to consider are your recovery mode — don’t forget your trans log backups if you are in Full! How big is your database? Can you afford to do a full backup daily? What about differentials? How difficult will it be for you to recover? What is the mean time to recovery for your system? How long can you be down?
Here is an example of what I like to do for important databases:
Daily Full at night when little else is going on
Differential either every 4 hours or every 6 hours [resulting in 6 or 4 diff logs]
Transaction Logs every 15 min or 30 min
The biggest thing most people forget, however, is to VERIFY your backups. Now how can you verify them?
Did you say ‘click the verify backup check box’? If so, you still run the risk of your database backup not working correctly. It does confirm that the backup is complete and the volumes are readable but it could still have integrity problems within the database files. Make sure you are running CheckDB against your database on a consistent basis.
Now how can you both help yourself out AND help out your developers?
Let’s say you are doing a nightly backup — at midnight, after you have performed your CheckDB. Why don’t you add another instance on your development server [SQL Server Developer License – $50, every company should be willing to foot the bill due to the benefits!]. Now you setup a new SQL Agent Job at, how about 1AM to restores from your backup location! Now the developers have fresh data they can import into their development environment on a nightly basis.
The easiest way to do this? Rely on filenames! Save your backups using something similar to DatabaseName_FULL_Date.bak , which will give you a reliable way to generate the filename to perform your restore.
I hope this gives you some good ideas and gets you started on helping your company, your development team, and yourself!
Questions? Comments? Drop a comment!