0

We've just experienced an issue where a newly attached Dev instance "picked up" schema changes that had been made to the previous Dev database but were not present in the Live DB replica before it was renamed and attached as Dev.

The Dev DB starts as a log shipping standby that is fully restored, detached, files are renamed and then re-attached as the new Dev DB. Note that the previous Dev database is not being deleted just detached. Also note that the old and new Dev DBs are both "forks" of the same original database.

Now this is a Dev/Test environment so it's not particularly problematic but it makes me worry that we'll create something against a silently changed Dev that will break Live when it's applied.

So my working theory is that this relates to the unique identifiers used by the SQL Server internals and possibly the timing of the changes to Dev.

Can anyone confirm this or shed any light on the issue?

EDIT: Further detail The schema changes involved the addition of new nullable columns at the end of a few tables. So they were admittedly minor and importantly did not involve any actual data. Also, the log shipping copy was in Standby until it was reattached (so read only) and I've reviewed the query logs to verify that no DDL changes were run.

That said, unfortunately I cannot replicate the issue. It does not happen with a small DB created just to test it and I deleted the Dev DB and recreated it from a full backup to resolve the issue so I cannot repeat the exact process.

My goal in asking the question is to ascertain whether we've experienced an exceptional fluke or our process is fundamentally unsound. I'm leaning toward exceptional fluke at the moment.

Joe Harris
  • 13,671
  • 4
  • 47
  • 54
  • 6
    SQL Server doesn't perform magic. If you attached a database and the schema changes were there, then the changes were in the file before you attached it. – Aaron Bertrand Apr 18 '13 at 13:26
  • 2
    Your working theory cannot possibly be correct my good man. I'm not sure where your process has gone wrong but SQL server cannot just change schema like that. Not sure what else to tell you on this one. Double check your process. – Zane Apr 18 '13 at 13:31
  • Thanks for your insightful comments folks. :-/ SQL Server stores schema info outside of the DB in the 'msdb' system database as well as database identity data, backup history, etc. So, having confirmed that it really happened, the question is whether anyone else has experienced this. – Joe Harris Apr 18 '13 at 14:25
  • You haven't given many details. What "schema changes" are you referring to exactly and how have you observed them? What happens if you use backup/restore instead of attach/detach? Since the DB in question is a log shipping target, are you certain that the state of the database was 'stable' when it was detached? What version of SQL Server? Can you show the script you use to detach/rename/attach? There is no way that just attaching a database changes the schema, so it's much more likely that there is an error in your script, but we don't know exactly what you're doing. – Pondlife Apr 18 '13 at 16:24
  • @Pondlife Added further detail, I have script written to try and replicate it but in fact it works fine in the test. I guess we'll see if the question flushes out anyone else who's had a similar experience. – Joe Harris Apr 19 '13 at 09:43

0 Answers0