THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

John Paul Cook

When a restore isn’t really complete - vote on Connect

This week I discovered that restoring from a full backup doesn’t always restore SQL Server to the same state it was in when the backup was made. There are three settings that, if enabled, are not restored after a database restore. Thanks to Greg Low for pointing out that the list of affected settings is found in the SQL Server 2008 Upgrade Technical Reference Guide from which I quote:

· is_broker_enabled
· is_honor_broker_priority_on
· is_trustworthy_on

Detaching and attaching a database will also cause these settings to be lost if they are enabled.

Although it is documented behavior, I consider it to be a bug when a restore does not restore a database to the same state it was in when the backup was made. Please voice your opinion to Microsoft in this Connect item:

Published Thursday, April 15, 2010 3:36 PM by John Paul Cook

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Remus Rusanu said:

Disabling Service Broker after a restore is by design. SSB is all about *distributed* applications and restoring a database means part of your distributed application is potentially rolled back in time. Given the potential disaster lurking there the measure to disable broker at restore and attach was intentionally put in.

The reasoning around the other two flags (honor broker priority and trustworthy) are different, but just as valid, they represent well understood vectors of elevation denial of service and of privilege escalation attacks.

April 16, 2010 2:24 AM

James Luetkehoelter said:

I agree with Remus on these - I would want them disabled on the restore of a database. I'd rather the assumption be that you're restoring/attaching to a new instance and reduce potential priveledges than to leave it open.

April 16, 2010 10:29 AM

j said:

have to agree with the others; this would just create a security hole. not a good idea at all.

April 17, 2010 11:57 AM

j said:

just wanted to add that calling this a bug is akin to asking MS to disable password policies because it's too difficult to remember complex passwords.

April 17, 2010 12:06 PM

Leave a Comment


About John Paul Cook

John Paul Cook is a database and Azure specialist in Houston. He previously worked as a Data Platform Solution Architect in Microsoft's Houston office. Prior to joining Microsoft, he was a SQL Server MVP. He is experienced in SQL Server and Oracle database application design, development, and implementation. He has spoken at many conferences including Microsoft TechEd and the SQL PASS Summit. He has worked in oil and gas, financial, manufacturing, and healthcare industries. John is also a Registered Nurse currently studying to be a psychiatric nurse practitioner. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2. Connect on LinkedIn

This Blog



Privacy Statement