Is the RESTORE process dependent on schema?

Posted by Martin Aatmaa on Stack Overflow See other posts from Stack Overflow or by Martin Aatmaa
Published on 2010-03-19T21:35:42Z Indexed on 2010/03/19 21:41 UTC
Read the original article Hit count: 334

Filed under:
|
|
|

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!

© Stack Overflow or respective owner

Related posts about sql-server

Related posts about backup