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
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
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 to satisfy healthcare meaningful use requirements and improve patient outcomes. Contributing author to
SQL Server MVP Deep Dives and
SQL Server MVP Deep Dives Volume 2.