THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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: 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) 
(required) 
Submit

About John Paul Cook

John Paul Cook is both a Registered Nurse and a Microsoft SQL Server MVP experienced in Microsoft 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. Experienced in systems integration and workflow analysis, John is passionate about combining his IT experience with his nursing background to solve difficult problems in healthcare. He sees opportunities in using business intelligence and Big Data to satisfy healthcare meaningful use requirements and improve patient outcomes. John graduated from Vanderbilt University with a Master of Science in Nursing Informatics and is an active member of the Sigma Theta Tau nursing honor society. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement