THE SQL Server Blog Spot on the Web
Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | Join | Help
in Search
Home Blogs Downloads   Opml View as RSS news feed in XML

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: https://connect.microsoft.com/SQLServer/feedback/details/551695/a-database-restore-does-not-restore-all-database-settings-such-as-trustworthy

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

Comments

 

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

(required) 
(optional)
(required) 
Submit

About John Paul Cook

SQL Server MVP based in Houston, Texas. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2. John is currently on sabbatical from database consulting and is very busy as a full-time nursing student.

This Blog

Syndication
©2006-2012 SQLblog.comTM
Brought to you by Adam Machanic & Peter DeBetta
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement