I'm a sysadmin who has recently been landed with some SQL DBA responsibility. Not reluctantly I might add, this is a world I'm very much looking to get immersed in.

I am curious about one of the aspects of our existing setup (where DBA tasks were previously largely left to the developers), namely the update mechanism for one of our production databases.

Essentially what happens is that any time the underlying data changes, one of the developers creates a new database in the dev environment and restores it onto the production environment. To facilitate this the development team have been given dbcreator permissions. This happens once or twice a week.

Can any of the more experienced DBAs spot any flaws with this approach? Should I insist on handling all the restores myself (I have written out the T-SQL commands for the dev team to use instead of SSMS which required sysadmin rights to browse the available media)? Could something go wrong during a restore that would knock the database offline?

We're using SQL Server 2008 R2 standard edition.

Thanks all.


As a general rule, I have an issue with the development team applying changes to a production database I am responsible for.

If I was in your shoes, I would change the process so I applied all database changes in Production. This eliminates surprises (for you) and adds a new set of eyes to the process at the very point it is critical to do so.

I am also a firm believer in using comparison tools. My current choices are the RedGate SQL Compare (for structure comparison) and RedGate SQL Data Compare (for data comparison). You are also able to use the Visual Studio for Database Professionals, if you have it loaded.

Another benefit of using the comparison tools is they minimize the changes to the modified database while providing you with a set of scripts which you are able to add to your source-control system to documentation each release.

Following this path removes the need for the developers to have elevated permissions in the production database and should reduce your exposure. In my mind, reducing exposure is always good.

| improve this answer | |
  • I agree. Updates to a production system should be carried out by the entity responsible for that production system. – joeqwerty Jul 5 '11 at 20:53
  • Thanks Robert, I had a meeting with the dev team yesterday and we have agreed that I will have complete responsibility for database restores. Turns out the DB is read only apart from a handful of tables so we're going to split those out to a separate database (they do not need to be restored to update them). This will not even require cross database queries apparently. – briancarrig Jul 8 '11 at 18:11
  • I will check out the tools you mention as well, thanks. – briancarrig Jul 8 '11 at 18:11

I'm guessing this is a read-only database? If so, I don't see any huge problems with it. The way I've done this before is saving the update scripts written against the dev database (in source control, naturally!) and then just re-applying the ones since the last update to prod. This method is probably a bit faster than doing a full DB restore, though that probably shouldn't matter too much if you don't do it frequently or if the restores are small. It is still good to snapshot prod before each update, in case something ever goes horribly wrong and prod and dev diverge.

| improve this answer | |
  • Unfortunately we cannot snapshot as the edition is SQL Standard. The database is not read-only either! But the services (web servers mainly) that read and write to the DB are shutdown prior to the restore, as you might expect. – briancarrig Jul 5 '11 at 8:13
  • It is scary that it is not read-only. Are the live changes somehow mirrored to dev before the updates are made and then restored to prod? I would be worried that in this process you could be losing data. – user55703 Jul 5 '11 at 8:23
  • Hmmm ... I'm not sure if they do mirror it back but I will check. I think data loss will be my angle when I approach the dev team. Is there anything else that could go wrong during a restore? Or any other drawbacks to the approach that you can think of aside from data loss? – briancarrig Jul 5 '11 at 22:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.