How to sync a server stored procedures from other server?
The company have dev/stage/prod server. Stage server will refresh every week from prod server(use prod backup file restore in stage). The new stored procedures in dev server should deploy to stage, but not in prod yet. So when after stage restored, all new stored procedure wipe out from stage server. Company use a job in stage to run after stage refresh. the job put in all new stored procedures. The issue is if people forget put new version stored procedures in this job, the stage server will not run correctly. What is best way to do the sync? Can we use linked server sp_helptext direct deploy new stored procedure to stage from dev server? Thanks.