Is the RESTORE process dependent on schema?

Question

Let's say I have two database instances:

InstanceA - Production server
InstanceB - Test server  

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

So, at any one time, the instance schema relationship looks like this:

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 fulfill this, I am taking the database backups of InstanceA and applying them (restoring them) to InstanceB.

My question is, how does schema version affect the restoral 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 no, what would the failure look like?

If yes, would the type of schema change matter?

For example, if Schema Version 1.6 differed from Schema Version 1.5 by just having an altered storec proc, I imagine that this type of schema change should't affect the restoral process. On the other hand, if Schema Version 1.6 differed from Schema Version 1.5 by having a different table definition (say, an additional column), I image this would affect the restoral process.

I hope I've made this clear enough.

Thanks in advance for any input!

Answer 1

No, a restore doesn't look at the schema, it simply restores datafiles as they were, so you'll end up with schema 1.5 after the restore. It will overwrite whatever is already there.

You'd be better to do the data refresh restore first, then apply the 1.6 schema changes.

Answer 2

Unless you're doing restores of just specific filegroups, your restore will include both data and schema. Doing a full restore of the database will include ALL schema and in the database, and would replace the database entirely. If you were to create filegroups and put specific tables in the filegroups, you could restore just those filegroups that you wanted - and that would include just the data and schema for the tables (and anything else you put in the file groups too - like indexes, materialized views, etc.)

What you should really be looking at are Schema- and Data-Compare tools. Things like Redgate, ApexSql Compare, AdeptSQL, or Visual Studio Database Edition all can do a great job with exactly the scenario that you describe. I personally use the VS Database edition 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 at it. I've used all the others in the past too, and would recommend any of them. Most of them have 30 day trial editions - you should download a couple and give it a test drive in your current scenario.