Is the RESTORE process schema dependent?

Question

Let's say I have two database instances:

InstanceA - Production server InstanceB - Test server 

My workflow is to first implement the new schema changes in InstanceB , test them, and then deploy them to InstanceA .

So, at any time, the instance schema relationships are as follows:

 InstanceA - Schema Version 1.5 InstanceB - Schema Version 1.6 (new version being tested) 

An additional part of my workflow is to keep the data in InstanceB as fresh as possible. To accomplish this, I take the InstanceA database backups and applying them (restoring them) to InstanceB .

My question is: how does the version of the circuit affect the recovery process?

I know I can do this:

 Backup InstanceA - Schema Version 1.5 Restore to InstanceB - Schema Version 1.5 

But can I do this?

 Backup InstanceA - Schema Version 1.5 Restore to InstanceB - Schema Version 1.6 (new version being tested) 

If not, what will the failure look like?

If so, would the type of schema change change?

For example, if Schema Version 1.6 is different from Schema Version 1.5 just by having a modified storec procedural, I believe that this type of scheme change should't affect the Restoral process. On the other hand, if Schema Version 1.6 differs from Schema Version 1.5 if there is a definition for another table (say, an additional column), I will image this will affect the Restoral process.

Hope I made it clear enough.

Thanks in advance for any input!

Answer 1

If you do not restore only certain file groups, your recovery will include both data and the scheme. A full database recovery will include the ALL schema and database and will completely replace the database. If you created filegroups and added specific tables to filegroups, you could only restore the filegroups that you wanted - and that would include only the data and schema for the tables (and everything else that you put in filegroups too - like indexes, materialized representations, etc.)

In fact, you should look at the Schema- and Data-Compare tools. Things like RedGate, ApexSQL Compare, AdeptSQL or Visual Studio Database Edition can all do a great job with the exact script you are describing. I personally use the version of VS Database because we get it with our Gold Partner licenses and love it. It does exactly what you are looking for and does a great job of it. I have used all the others in the past, and would recommend any of them. Most of them have 30-day trial versions - you should download a couple and give it a test disk in your current scenario.

Answer 2

No, the recovery does not look at the scheme, it just restores the data files as they are, so after the restoration you will get the scheme 1.5. He will overwrite everything that is already there.

It is better to perform data recovery first, and then apply the changes to the 1.6 scheme.