Migration Scripts trying to run unnecessarily on re-link
CJ314
Posts: 2
Every few weeks, I have to get updated data for my local/dev environment. This is my current, tried-and-true method:
Unlink current database(s) from SQL Source Control.
Delete Databases (there is only so much disk space on this computer).
Download and unzip the latest database.
Create a new database on my computer.
Restore the new, local database with the bak file from the production environment.
Link SQL Source Control to the database when it's been restored.
Run the "undo" command on any objects in the Commit tab.
Run the Get Latest command.
Proceed with productivity.
In my latest run though this process, SQL Source Control is trying to run Migration Scripts that have already been run. This is a real problem because they throw errors and drop tables which greatly aggravates the whole process (and me). Further, the migration scripts run before any other schema changes run, so I'm trapped until I can get around this issue.
Why is this happening? How do I fix it?
It's worth noting that one or more of the migration scripts that are trying to run may have been created when my local ("dedicated") database was connected in "shared" mode. I still don't know if that has any lasting/deleterious effects, and I'm worried that that mistake is finally coming back to haunt me.
I'd appreciate any help or insight
Thanks!
Corey
PS: If anyone has any insight on a better way to handle getting recent data from production, I'd be glad to hear it - but I MUST figure out how to get this database re-linked without errors ASAP.
Unlink current database(s) from SQL Source Control.
Delete Databases (there is only so much disk space on this computer).
Download and unzip the latest database.
Create a new database on my computer.
Restore the new, local database with the bak file from the production environment.
Link SQL Source Control to the database when it's been restored.
Run the "undo" command on any objects in the Commit tab.
Run the Get Latest command.
Proceed with productivity.
In my latest run though this process, SQL Source Control is trying to run Migration Scripts that have already been run. This is a real problem because they throw errors and drop tables which greatly aggravates the whole process (and me). Further, the migration scripts run before any other schema changes run, so I'm trapped until I can get around this issue.
Why is this happening? How do I fix it?
It's worth noting that one or more of the migration scripts that are trying to run may have been created when my local ("dedicated") database was connected in "shared" mode. I still don't know if that has any lasting/deleterious effects, and I'm worried that that mistake is finally coming back to haunt me.
I'd appreciate any help or insight
Thanks!Corey
PS: If anyone has any insight on a better way to handle getting recent data from production, I'd be glad to hear it - but I MUST figure out how to get this database re-linked without errors ASAP.
Comments
In SQL Source Control 5, if you use Migration Scripts, applied scripts are tracked in the table [RedGateLocal].[DeploymentMetadata] .
When you restore your backup from Production you lose this table and that explains why the tool attempts to apply all scripts again.
Regarding an efficient method of updating your data from production, I suggest that you use SQL Datacompare Professional, you can setup a comparison and deployment using the Windows command line and run it either on demand or schedule regular syncs (using the Windows task scheduler).
You can use the standard version of SQL Data Compare if you are ok with running the comparison and deployment from the UI on demand only.
I hope this helps
Thank you,
Product Support Engineer
Redgate Software Ltd
Please see our Help Center for detailed guides on how to use our tools