Job to copy off new stored procs

[mutley1]

Hi all,

Here's the scenario - we have a live and dev environment and the dev is updated nightly from the live backup. The thing is the developers will create new stored procs etc. during the day and then the restore overnight replaces the entire DB from scratch thus wiping out what they have created. Does anyone know (apart from scripting out what they have done and re-loading the procs etc. MANUALLY) of a script I could run, maybe using create date or something to script out the changes and then reload them afterwards? I am really only talknig SP's being added here and I don't want to have to back up the dev env then do a comparison as a whole. Ideally it would just be "find out what was added today and script it out". Once I have those scripts I can easily create a new step to add them back in.

TIA for any help.

Cheers,

M.

 

[JayKusch]

Why not create an DTS/SSIS package that scripts out the SPs, executes your backup and then scripts the SPs back in?

Just an idea.

Thanks

J. Kusch

 

[mrdenny]

This is why I don't like to automate the restore of production systems to Dev.

Jay's solution is probably the best solution.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[mutley1]

Thanks guys,

I was thinking along those lines, but was wondering if there was a date or something I could use because if they change live during the day (bad practise I know - don't ask.....my hands are tied) and I reload ALL SP's after a backup of Dev, then those changes will be overwritten.

I'm thinking about using sysobjects where xtype = 'P' and then the crdate / getdate to determine what was created today.......then script out and back in either side of the restore. Seem feasible?

TIA,

M.

 

[JayKusch]

Not quit following your process.

You have a Prod box (that could have SPs change during the day) ... Correct?

You cut a backup of Prod and apply it to your Dev box ... correct?

You are afraid that a change in a Prod SP may over-write an SP on Dev ... Correct?

Just trying to get the ducks in a line here.

Thanks

J. Kusch

 

[mutley1]

Hi Jay,

1. Normal day, SP1 and SP2 do not exist in prod as the developers have created them in dev that day.
2. Overnight process to restore live to dev runs and SP1 and SP2 get wiped.

Scenario 2:

1. Amendment is made to SP3 in prod, but a developer has also changed it to something different in Dev.
2. Restore of prod to dev runs with correct SP3, then the restore of stuff changed that day in dev kicks in and over-writes the correct live SP3 with the dev version they were working on.

I know the whole procedure sucks but I have no say as to who can change what and when (as I say, don't ask..........just don't ask!!!)

I've decided that I'm just getting the developers to send me a script of what they want building back in after a prod to dev restore and leave them to it. If they get it wrong among themselves then it's their problem!

Cheers,

M.

 

[JayKusch]

LOL ... same solution I have used in MANY cases with developers.

And I am sure there is no version control process/app in place like SourceSafe or such?

J

Thanks

J. Kusch

 

[mutley1]

I love developers........they're my best friends......

Yes we have SourceSafe, but they all think that's where they can hide their Ketchup so nobody can steal it